| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last non blank cell in selected cells
Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
What do you mean by "find" the last non blank cell in the group?
Do you want a formula that returns the value from that cell? Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(A1:A20,MAX((MOD(ROW(A1:A20),5)=0)*(A1:A20< "")*(ROW(A1:A20)))) Biff "Ron Weaver" wrote in message ... Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))
ctrl+shift+enter, not just enter "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))
That returns the max value from the specified cells, not the last value from the specified cells. Also, what if A10 is the only entry in the range? Biff "Teethless mama" wrote in message ... =IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20))) ctrl+shift+enter, not just enter "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
I will try to make this a little more clear. On week one I will put a figure
in A1. I want this figure to show in B1. On week two I will put a figure in A5. Now I want this figure to show in B1. On week three I will put a figure in A10. Now I want this figure to show in B1, etc. To complicate things, there will be other values in A2,A3,A4,A6,A7,A8,A9. "T. Valko" wrote: =IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20))) That returns the max value from the specified cells, not the last value from the specified cells. Also, what if A10 is the only entry in the range? Biff "Teethless mama" wrote in message ... =IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20))) ctrl+shift+enter, not just enter "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
Try something like this:
B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
That works perfectly. Now, what if I want to do the same thing across:
Formula in B1 Values in A1,E1,J1,N1, etc. Thanks "Ron Coderre" wrote: Try something like this: B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
OK....
But, with one caveat: The formula can't be in Row_1 or you'll have a circular ref. So B2: =INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1)) BTW: Same issue for the formula referencing Col_A... It can't be in Col_A and... If the formula will be in a row other than Row_1 Use this version: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: That works perfectly. Now, what if I want to do the same thing across: Formula in B1 Values in A1,E1,J1,N1, etc. Thanks "Ron Coderre" wrote: Try something like this: B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
The formula I suggested does what you want.
Biff "Ron Weaver" wrote in message ... I will try to make this a little more clear. On week one I will put a figure in A1. I want this figure to show in B1. On week two I will put a figure in A5. Now I want this figure to show in B1. On week three I will put a figure in A10. Now I want this figure to show in B1, etc. To complicate things, there will be other values in A2,A3,A4,A6,A7,A8,A9. "T. Valko" wrote: =IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20))) That returns the max value from the specified cells, not the last value from the specified cells. Also, what if A10 is the only entry in the range? Biff "Teethless mama" wrote in message ... =IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20))) ctrl+shift+enter, not just enter "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
This works ! Thanks for your help.
"Ron Coderre" wrote: OK.... But, with one caveat: The formula can't be in Row_1 or you'll have a circular ref. So B2: =INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1)) BTW: Same issue for the formula referencing Col_A... It can't be in Col_A and... If the formula will be in a row other than Row_1 Use this version: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: That works perfectly. Now, what if I want to do the same thing across: Formula in B1 Values in A1,E1,J1,N1, etc. Thanks "Ron Coderre" wrote: Try something like this: B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
Thanks for the feedback, Ron.....
I'm glad I could help on that one (plus, it was fun to figure out) *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: This works ! Thanks for your help. "Ron Coderre" wrote: OK.... But, with one caveat: The formula can't be in Row_1 or you'll have a circular ref. So B2: =INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1)) BTW: Same issue for the formula referencing Col_A... It can't be in Col_A and... If the formula will be in a row other than Row_1 Use this version: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: That works perfectly. Now, what if I want to do the same thing across: Formula in B1 Values in A1,E1,J1,N1, etc. Thanks "Ron Coderre" wrote: Try something like this: B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last non blank cell in selected cells
Control+shift+enter:
=LOOKUP(9.99999999999999E+307,IF(MOD(COLUMN(A1:Z1)-COLUMN(A1),5)=0,IF(ISNUMBER(A1:Z1),A1:Z1))) Ron Weaver wrote: That works perfectly. Now, what if I want to do the same thing across: Formula in B1 Values in A1,E1,J1,N1, etc. Thanks "Ron Coderre" wrote: Try something like this: B1: =INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Weaver" wrote: Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have Excel find the last non blank cell in the group? These are numeric values that update a report weekly. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Blank cell is not blank | Excel Discussion (Misc queries) | |||
| Summing (or counting) cells until there is a blank cell | Excel Discussion (Misc queries) | |||
| Calculating only non-empty cells... | Excel Worksheet Functions | |||
| Counting blank cells until value is reached | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
