Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
Hi,
I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
find the last cell in that range with data
What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
hi Biff,
thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
Try this:
=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
FWIW -
This cell reference is *Text* And can't be used in other formulas for calculation purposes, just for display purposes. Must be wrapped in Indirect() to use in calculations. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )
Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
I forgot the "exact" argument:
=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0)) BUT ... I like yours better: =ADDRESS(18,3+MATCH(99^99,D18:V18)) And I usually wait for the OP's comments before adding too much "robustness".<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18 ,0))
That could choke if the last value was duplicated in the range: ...D....E....F...G....H 10...22........15...10 -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... I forgot the "exact" argument: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0)) BUT ... I like yours better: =ADDRESS(18,3+MATCH(99^99,D18:V18)) And I usually wait for the OP's comments before adding too much "robustness".<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
Why do you think I said I like yours better?<bg
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18 ,0)) That could choke if the last value was duplicated in the range: ...D....E....F...G....H 10...22........15...10 -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... I forgot the "exact" argument: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0)) BUT ... I like yours better: =ADDRESS(18,3+MATCH(99^99,D18:V18)) And I usually wait for the OP's comments before adding too much "robustness".<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
It could be "worse". If Harlan was critiquing this thread he would also
point out that it's more efficient to use a constant rather than a calculated lookup_value: =ADDRESS(18,3+MATCH(99^99,D18:V18)) =ADDRESS(18,3+MATCH(1E10,D18:V18)) And If Aladin were critiquing this thread he would "complain" about not using 9.99999999999999E+307 as the constant. And if Domenic were critiquing this thread he would calculate both the row argument and the column offset so that the formula would be robust against row/column insertions. So, no matter what you do ............ -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Why do you think I said I like yours better?<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V1 8,0)) That could choke if the last value was duplicated in the range: ..D....E....F...G....H 10...22........15...10 -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... I forgot the "exact" argument: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0)) BUT ... I like yours better: =ADDRESS(18,3+MATCH(99^99,D18:V18)) And I usually wait for the OP's comments before adding too much "robustness".<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
Yeah ! ! !
Ain't XL wonderful?<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... It could be "worse". If Harlan was critiquing this thread he would also point out that it's more efficient to use a constant rather than a calculated lookup_value: =ADDRESS(18,3+MATCH(99^99,D18:V18)) =ADDRESS(18,3+MATCH(1E10,D18:V18)) And If Aladin were critiquing this thread he would "complain" about not using 9.99999999999999E+307 as the constant. And if Domenic were critiquing this thread he would calculate both the row argument and the column offset so that the formula would be robust against row/column insertions. So, no matter what you do ............ -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Why do you think I said I like yours better?<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V1 8,0)) That could choke if the last value was duplicated in the range: ..D....E....F...G....H 10...22........15...10 -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... I forgot the "exact" argument: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0)) BUT ... I like yours better: =ADDRESS(18,3+MATCH(99^99,D18:V18)) And I usually wait for the OP's comments before adding too much "robustness".<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
thank you! that is just the ticket. I realise I might be taking advantage of
your good nature (and your considerable knowledge of Excel) but now i need to find a formula that will take the numeric value from that cell and use it in a summing function. i have been tooling around with INDIRECT but as I am pretty much a novice I haven't had much luck. any help you could provide would be greatly apprectiated. "T. Valko" wrote: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
You really don't want to use the address.
Simply use the *last* number: =Lookup(99^99,D18:V18) Which will return the *same* value as: =INDIRECT(ADDRESS(18,3+MATCH(99^99,D18:V18))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... thank you! that is just the ticket. I realise I might be taking advantage of your good nature (and your considerable knowledge of Excel) but now i need to find a formula that will take the numeric value from that cell and use it in a summing function. i have been tooling around with INDIRECT but as I am pretty much a novice I haven't had much luck. any help you could provide would be greatly apprectiated. "T. Valko" wrote: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
thanks heaps RD,
very helpful as always. regards, sevi "RagDyeR" wrote: You really don't want to use the address. Simply use the *last* number: =Lookup(99^99,D18:V18) Which will return the *same* value as: =INDIRECT(ADDRESS(18,3+MATCH(99^99,D18:V18))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... thank you! that is just the ticket. I realise I might be taking advantage of your good nature (and your considerable knowledge of Excel) but now i need to find a formula that will take the numeric value from that cell and use it in a summing function. i have been tooling around with INDIRECT but as I am pretty much a novice I haven't had much luck. any help you could provide would be greatly apprectiated. "T. Valko" wrote: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last cell in range with data, display cell address
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sevi61" wrote in message ... thanks heaps RD, very helpful as always. regards, sevi "RagDyeR" wrote: You really don't want to use the address. Simply use the *last* number: =Lookup(99^99,D18:V18) Which will return the *same* value as: =INDIRECT(ADDRESS(18,3+MATCH(99^99,D18:V18))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... thank you! that is just the ticket. I realise I might be taking advantage of your good nature (and your considerable knowledge of Excel) but now i need to find a formula that will take the numeric value from that cell and use it in a summing function. i have been tooling around with INDIRECT but as I am pretty much a novice I haven't had much luck. any help you could provide would be greatly apprectiated. "T. Valko" wrote: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) Try that with these values: D18 = 29 E18 = 41 G18 = 36 Try it like this: =ADDRESS(18,3+MATCH(99^99,D18:V18)) Since we saved a few keystrokes by eliminating the LOOKUP call we can add some robustness <g: =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"") We don't really need the $$ signs do we? Even if you want to use that address in another formula you'd have to reference it with INDIRECT and the $$ signs are superfluous to INDIRECT. =IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"") -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... Try this: =ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "sevi61" wrote in message ... hi Biff, thank you for your prompt reply. in answer to your question; the data is numeric and there are formulas in the range but not to return blanks. the formula that would be in the last populated cell would be =(9-SUM(XXX:XXX)) I hope this clarifies things a little thanks again for taking the time to help with this regards, sevi "T. Valko" wrote: find the last cell in that range with data What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are there any formulas in this range that return blanks? Do you want to include these blanks as data? Assuming the data type is TEXT and there are no formulas in the range that return blanks: =IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"") -- Biff Microsoft Excel MVP "sevi61" wrote in message ... Hi, I have scoured these posts but haven't quite found what I'm looking for, hopefully someone can help.... Is it possible to write a formula that will search a range (D18:V18), find the last cell in that range with data, then show the cell address, not the value of the cell, in a different cell? I have been thinking that maybe I will need an array formula, or at worst, a macro or VBA? Any response will be greatly appreciated. sevi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display a cell address | Excel Worksheet Functions | |||
find email address in a cell | Excel Worksheet Functions | |||
How to find the address of the min cell within a range | Excel Worksheet Functions | |||
Is there a way to find the address of a cell with a certain value? | Excel Worksheet Functions | |||
Display the address of cell with max value | Excel Worksheet Functions |