| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing last cell
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Try
COL A 1 2 3 4 55 66 55 35 =INDIRECT("D"&(MAX(COUNT(D:D),1))) 55 "Jean" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Try this
=INDIRECT("D"&(MAX(COUNT(D:D),1))) It should identify the last row and provide the vaklue in the last row. sorry for the previous premature posting!! Woody "Jean" wrote: 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
This will divide the last cell in column D with a value in it by the cell
immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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 "Woodsey" wrote: Try this =INDIRECT("D"&(MAX(COUNT(D:D),1))) It should identify the last row and provide the vaklue in the last row. sorry for the previous premature posting!! Woody "Jean" wrote: 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jean" wrote: 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Sorry, that one was wrong, try
=INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
There are no blank cells in column D
Try this: =IF(COUNT(D:D)<2,"",(LOOKUP(100^10,D:D)/INDEX(D:D,MATCH(100^10,D:D)-1))-1) Biff "Jean" wrote in message ... 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! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Fabulous! Thank you so much!
"Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Great! Thank you.
"T. Valko" wrote: There are no blank cells in column D Try this: =IF(COUNT(D:D)<2,"",(LOOKUP(100^10,D:D)/INDEX(D:D,MATCH(100^10,D:D)-1))-1) Biff "Jean" wrote in message ... 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! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Another great suggestion! Thank you!
"Martin Fishlock" wrote: 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 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jean" wrote: 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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 "Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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: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:D)-1) give? How does that compare with 'Unit Values'!D$78 if that is the penultimate value in your list? -- David Biddulph "Jean" wrote in message ... 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 "Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
It works!!! (I left out an apostrophe...it would have been easier if my
sheet names didn't have spaces!) Thanks again. Jean "Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
Thank you, David...that helped me to find my error!
Jean "David Biddulph" wrote: 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: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:D)-1) give? How does that compare with 'Unit Values'!D$78 if that is the penultimate value in your list? -- David Biddulph "Jean" wrote in message ... 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 "Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
You're welcome!
Biff "Jean" wrote in message ... Great! Thank you. "T. Valko" wrote: There are no blank cells in column D Try this: =IF(COUNT(D:D)<2,"",(LOOKUP(100^10,D:D)/INDEX(D:D,MATCH(100^10,D:D)-1))-1) Biff "Jean" wrote in message ... 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! |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing last cell
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. "Jean" wrote in message ... It works!!! (I left out an apostrophe...it would have been easier if my sheet names didn't have spaces!) Thanks again. Jean "Alan" wrote: Sorry, that one was wrong, try =INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D))/INDIRECT("'Unit Values'!D"&MATCH(9.9999999E+306,'Unit Values'!D:D)-1)-1 All one line, Regards, Alan. "Jean" wrote in message ... 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 "Alan" wrote: This will divide the last cell in column D with a value in it by the cell immediately above it, =LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)/(LOOKUP(2,1/(Sheet1!D1:D1000),Sheet1!D1:D1000)-1) Regards, Alan. "Jean" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a cell based on the value in another cell | Excel Discussion (Misc queries) | |||
| Cell Referencing? | Excel Worksheet Functions | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Linear Mode
