Referencing last cell

[Guest]

I have a formula that looks like this:
=('Unit Values'!D$79/'Unit Values'!D$78)-1
The D$79 is referencing the last row of the 'Unit Values' sheet. Every
month I have to add another row of information in the 'Unit Values'
spreadsheet. This means that I have to edit all my formulas to read D$80.
The following month, I have to edit the formula to read D$81. This is always
referencing the last row of data. There are no blank cells in column D. Is
there a function that I could add to the above formula that will always
reference the last cell in that column? Thanks!

 

[Guest]

Try


COL A
1
2
3
4
55
66
55
35 =INDIRECT("D"&(MAX(COUNT(D),1)))
55

 

[Guest]

Try this

=INDIRECT("D"&(MAX(COUNT(D),1)))

It should identify the last row and provide the vaklue in the last row.
sorry for the previous premature posting!!

Woody

 

[Alan]

This will divide the last cell in column D with a value in it by the cell
immediately above it,
=LOOKUP(2,1/(Sheet1!D11000),Sheet1!D11000)/(LOOKUP(2,1/(Sheet1!D11000),Sheet1!D11000)-1)
Regards,
Alan.

 

[Guest]

Thank you so much, but I'm having problems incorporating this into my
formula. Can you assist???

=('Unit Values'!D$79/'Unit Values'!D$78)-1

 

[Guest]

Thank you very much...I thought this might work, but I ended up with
100.486%. How would I use this in my current formula of:
=('Unit Values'!D$79/'Unit Values'!D$78)-1

 

[Guest]

Another way to do it which may be considered a little easier (and a lot
easier to check!) is to have a couple of rows at the top of the 'unit values'
data sheet picking up the two rows of data using the formula as provided by
Alan or Woodsey.

D1: =OFFSET($D$3,COUNT($D$3:$D$5000)-1,0) << current
D2: =OFFSET($D$3,COUNT($D$3:$D$5000)-2,0) << prior

The above assumes no breaks in the data and

Then in your spreadsheets to link to the D1 and the D2 and this does not
need to change. All that changes is the items in D1 and D2 picking up the
last rows of the worksheet. Note if the unit values is downloaded from a
system every month and not a maintained file then consider using a dummy
sheet in your analyis book instead picking up the values and then you link to
the dummy sheet.

You can then

 

[Alan]

Sorry, that one was wrong, try
=INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D)-1)-1
All one line,
Regards,
Alan.

 

[T. Valko]

There are no blank cells in column D

Try this:

=IF(COUNT(D)<2,"",(LOOKUP(100^10,D)/INDEX(D,MATCH(100^10,D)-1))-1)

Biff

 

[Guest]

Fabulous! Thank you so much!

Sorry, that one was wrong, try
=INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D)-1)-1
All one line,
Regards,
Alan.

 

[Guest]

Another great suggestion! Thank you!

Another way to do it which may be considered a little easier (and a lot
easier to check!) is to have a couple of rows at the top of the 'unit values'
data sheet picking up the two rows of data using the formula as provided by
Alan or Woodsey.

D1: =OFFSET($D$3,COUNT($D$3:$D$5000)-1,0) << current
D2: =OFFSET($D$3,COUNT($D$3:$D$5000)-2,0) << prior

The above assumes no breaks in the data and

Then in your spreadsheets to link to the D1 and the D2 and this does not
need to change. All that changes is the items in D1 and D2 picking up the
last rows of the worksheet. Note if the unit values is downloaded from a
system every month and not a maintained file then consider using a dummy
sheet in your analyis book instead picking up the values and then you link to
the dummy sheet.

You can then

 

[Guest]

Great! Thank you.

Try this:

=IF(COUNT(D)<2,"",(LOOKUP(100^10,D)/INDEX(D,MATCH(100^10,D)-1))-1)

Biff

 

[Guest]

I'm trying this formula, but my answer is incorrect. I'm getting -.48% when
the answer should be 1.43%. I did use your exact formula. Any other
suggestions??

Thanks - Jean

 

[David Biddulph]

Without knowing what numbers are in your cells, it's difficult to tell.

Why not break the formula up into manageable chunks?

What does =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D)) give?
How does that compare with your ='Unit Values'!D$79 if that is the last
value in your list?

What does =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit
Values'!D)-1) give?
How does that compare with 'Unit Values'!D$78 if that is the penultimate
value in your list?

 

[Guest]

It works!!! (I left out an apostrophe...it would have been easier if my
sheet names didn't have spaces!)

Thanks again.
Jean

 

[Guest]

Thank you, David...that helped me to find my error!
Jean

 

[T. Valko]

You're welcome!

Biff

Great! Thank you.

 

[Alan]

It would indeed! Those apostrophe's are very fiddly, for future reference
try Unit_Values instead, then the dreaded apostrophe's are unnecessary. Glad
you have a resolution though. Very good advice from you on this too David,
thanks,
Regards,
Alan.