Excel Cell Referencing 1

[renesp]

Hi all and thanks for helping out. I need to have a cell on Sheet1 keep referencing the latest cell of column F Sheet2. So as the worksheet grows,I know the what the latest information on the other worksheet is.

Thanks again.

 

[WebGenii]

Try using the OFFSET function in combination with COUNTA
a sample formula would look like this.

=OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)-1,5)

 

[SkipVought]

Hi,

latest information on the other worksheet

Do you want to know the 1) MAX numeric value in a column or 2) the row number of the last row of data in a column or 3) the VALUE in the last row of data in a column ?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renesp]

I need to know what is in the last cell in column F. So if i add another row, the next last cell in column F keeps showing up.

Thanks

 

[xlbo]

In which case WebGeniis formula will work just fine

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Do you merely want to know what the last row is, or do you need to know the new range of data in column F, when you add or delete rows?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

By the way, welcome, renesp and WebGenii to tek-tips.

WebGenii, if you're looking at a challenge for proving who knows the best in Excel and other MS Office Apps, you've found the right place.

renesp, Skip asked you a pretty basic question, necessary for getting the definite right answer. That question is simply thus: Do you want the [blue]DATA[/blue] contained in the last cell of your Column F, or do you want the [blue]ADDRESS[/blue] of the last cell in said Column?

And either way, once you do find a solution that works well for your specific situation, please update the thread here, letting others know exactly what the solution was - whether something here, or whether something altogether different.

 

[xlbo]

I need to know what is in the last cell in column F

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Yes, the OP got his answer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Deceptive little word, that "in" is, you know.

 

[renesp]

I've tried the offset formula but it just returns 1/0/1900. When it the last cell in column F has 8/12/10. I forgot to mention that the data starts on F8 and continues on down.

 

[SkipVought]

Web's formula
[tt]
=OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)-1,5)
[/tt]
assumes that 1) there is a heading in F1 and the data starting in F2 is contiguous to the last cell containing data and 3) there are no cells in column F ANYWHERE below the last contiguous cell containing data.

I forgot to mention that the data starts on F8 and continues on down.

Are there any cells ABOVE F8 containing ANY value and are there any cells BELOW the last cell containing ANY values?

If not then...
[tt]
[tt]
=OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)+7,5)
[/tt]

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WebGenii]

The COUNTA portion of the formula counts the number of cells in the column (F) containing data. If there are empty cells above F8, this is the part of the formula to change.
Assuming empty cells above F8 I'd try this
=OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)+7,5)

Catharine (aka WebGenii)

http://www.webgenii.ca

 

[renesp]

The header starts from B7-F7. So the data starts on row 8. So there is "data" above F8 but nothing important.

Thank you all once again.

 

[SkipVought]

The header starts from B7-[red]F[/red]7

[red]THAT[/red] is important because it is a VALUE in column F. COUNTA [red]counts[/red] F7, sou you must account for that in the offset from row 1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renesp]

So should it be =OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)+8,5) instead of =OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)+7,5)

 

[SkipVought]

Doe it return what you expect?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renesp]

It keeps giving me 0 and when I change the cell type to date it always gives me 1/0/1900.

 

[SkipVought]

If your first cell containing data is in F7, then the offset from row 1 is 6: 1 + 6 = 7.

You need to look at the Excel HELP on OFFSET and fully understand how it works.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renesp]

Thank you all for all your help.