| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for information in the last column
Imagine:
Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec part 1 2 2.5 2.5 3 3.25 part 2 1 1.1 part 3 6 7 34 I'm looking for a seperate column that will list out the last value entered in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34. In my attempt to find a formula I thought of something like =lastvalue(b3:b15) but that's not a formula. Does anyone have any suggestions? As usual thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking for information in the last column
The following formula assumes your table is in the upper most left hand part
of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month names are in B1, C1, D1, etc. =VLOOKUP("part1",$A$2:$I$4,MAX(IF(A2:I2<"",COLUMN (A2:I2),"")),0) -- Regards, Dave "Hirsch" wrote: Imagine: Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec part 1 2 2.5 2.5 3 3.25 part 2 1 1.1 part 3 6 7 34 I'm looking for a seperate column that will list out the last value entered in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34. In my attempt to find a formula I thought of something like =lastvalue(b3:b15) but that's not a formula. Does anyone have any suggestions? As usual thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking for information in the last column
David I appreciate the attempt, I should have stated that the numbers can go
up or down as they goto the left. thank you anyhow. "David Billigmeier" wrote: The following formula assumes your table is in the upper most left hand part of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month names are in B1, C1, D1, etc. =VLOOKUP("part1",$A$2:$I$4,MAX(IF(A2:I2<"",COLUMN (A2:I2),"")),0) -- Regards, Dave "Hirsch" wrote: Imagine: Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec part 1 2 2.5 2.5 3 3.25 part 2 1 1.1 part 3 6 7 34 I'm looking for a seperate column that will list out the last value entered in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34. In my attempt to find a formula I thought of something like =lastvalue(b3:b15) but that's not a formula. Does anyone have any suggestions? As usual thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking for information in the last column
I'm not sure what you mean. The formula I gave will output the exact
examples you posted in your table (e.g. "part1" will output 3.25, "part2" will output 1.1 and "part3" will output 34). Are you getting confused because I hardcoded "part1" into the formula? If so create a helper cell (e.g. J1) in which you place your part number, and use this formula: =VLOOKUP(J1,$A$2:$I$4,MAX(IF(A2:I2<"",COLUMN(A2:I 2),"")),0) I also should have mentioned this is an array formula so you need to commit it with CTRL+SHIFT+ENTER. If this still isn't what you mean post another example specifying better. -- Regards, Dave "Hirsch" wrote: David I appreciate the attempt, I should have stated that the numbers can go up or down as they goto the left. thank you anyhow. "David Billigmeier" wrote: The following formula assumes your table is in the upper most left hand part of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month names are in B1, C1, D1, etc. =VLOOKUP("part1",$A$2:$I$4,MAX(IF(A2:I2<"",COLUMN (A2:I2),"")),0) -- Regards, Dave "Hirsch" wrote: Imagine: Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec part 1 2 2.5 2.5 3 3.25 part 2 1 1.1 part 3 6 7 34 I'm looking for a seperate column that will list out the last value entered in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34. In my attempt to find a formula I thought of something like =lastvalue(b3:b15) but that's not a formula. Does anyone have any suggestions? As usual thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
looking for information in the last column
David's formula will work as it looks at the maximum COLUMN number which is
non -blank. And it should be entered with Ctrl+Shift+Enter "Hirsch" wrote: David I appreciate the attempt, I should have stated that the numbers can go up or down as they goto the left. thank you anyhow. "David Billigmeier" wrote: The following formula assumes your table is in the upper most left hand part of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month names are in B1, C1, D1, etc. =VLOOKUP("part1",$A$2:$I$4,MAX(IF(A2:I2<"",COLUMN (A2:I2),"")),0) -- Regards, Dave "Hirsch" wrote: Imagine: Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec part 1 2 2.5 2.5 3 3.25 part 2 1 1.1 part 3 6 7 34 I'm looking for a seperate column that will list out the last value entered in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34. In my attempt to find a formula I thought of something like =lastvalue(b3:b15) but that's not a formula. Does anyone have any suggestions? As usual thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stock data manipulation | Excel Worksheet Functions | |||
| Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Linear Mode
