| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup first & last values within row & return column header value
I have a spreadsheet with data as follows:
A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header value
With your posted data in A1:F4
This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Thanks Ron
The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Here you go....
Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Thanks Ron
Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
The rows beneath contain a "1" in each cell
when an employee will be on site. For the last entry: =IF(SUM(K11:BJ11),LOOKUP(2,K11:BJ11,K$7:B$J7),"N/A") -- Biff Microsoft Excel MVP "Matt" wrote in message ... Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
You're welcome, Matt....I'm glad I could help.
*********** Regards, Ron XL2003, WinXP "Matt" wrote: Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Sorry to bother you again Ron.
How would I change the formulas that you gave me (both for first value and last value) so that it recognises any number rather than just a "1". -- Regards Matt "Ron Coderre" wrote: You're welcome, Matt....I'm glad I could help. *********** Regards, Ron XL2003, WinXP "Matt" wrote: Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
I am using the sheet to show manning levels
so that it recognises any number I don't imagine you'd have negative manning? So, assuming the numbers are =0: For the first number (if any): =IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A") For the last number (if any): =IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A") Format both as DATE -- Biff Microsoft Excel MVP "Matt" wrote in message ... Sorry to bother you again Ron. How would I change the formulas that you gave me (both for first value and last value) so that it recognises any number rather than just a "1". -- Regards Matt "Ron Coderre" wrote: You're welcome, Matt....I'm glad I could help. *********** Regards, Ron XL2003, WinXP "Matt" wrote: Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Thanks Biff. Works great!
-- Regards Matt "T. Valko" wrote: I am using the sheet to show manning levels so that it recognises any number I don't imagine you'd have negative manning? So, assuming the numbers are =0: For the first number (if any): =IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A") For the last number (if any): =IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A") Format both as DATE -- Biff Microsoft Excel MVP "Matt" wrote in message ... Sorry to bother you again Ron. How would I change the formulas that you gave me (both for first value and last value) so that it recognises any number rather than just a "1". -- Regards Matt "Ron Coderre" wrote: You're welcome, Matt....I'm glad I could help. *********** Regards, Ron XL2003, WinXP "Matt" wrote: Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Matt" wrote in message ... Thanks Biff. Works great! -- Regards Matt "T. Valko" wrote: I am using the sheet to show manning levels so that it recognises any number I don't imagine you'd have negative manning? So, assuming the numbers are =0: For the first number (if any): =IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A") For the last number (if any): =IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A") Format both as DATE -- Biff Microsoft Excel MVP "Matt" wrote in message ... Sorry to bother you again Ron. How would I change the formulas that you gave me (both for first value and last value) so that it recognises any number rather than just a "1". -- Regards Matt "Ron Coderre" wrote: You're welcome, Matt....I'm glad I could help. *********** Regards, Ron XL2003, WinXP "Matt" wrote: Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Biff,
How can we modify this formula to recognise the last cell containing text rather than the number 1? -- Jo :) "T. Valko" wrote: The rows beneath contain a "1" in each cell when an employee will be on site. For the last entry: =IF(SUM(K11:BJ11),LOOKUP(2,K11:BJ11,K$7:B$J7),"N/A") -- Biff Microsoft Excel MVP "Matt" wrote in message ... Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup first & last values within row & return column header v
Try this:
=IF(COUNTIF(K11:BJ11,"*"),LOOKUP(REPT("z",255),K11 :BJ11,K$7:BJ$7),"N/A") That will not *exclude* cells that contain formula blanks. -- Biff Microsoft Excel MVP "Jo" wrote in message ... Biff, How can we modify this formula to recognise the last cell containing text rather than the number 1? -- Jo :) "T. Valko" wrote: The rows beneath contain a "1" in each cell when an employee will be on site. For the last entry: =IF(SUM(K11:BJ11),LOOKUP(2,K11:BJ11,K$7:B$J7),"N/A") -- Biff Microsoft Excel MVP "Matt" wrote in message ... Thanks Ron Works a treat. -- Regards Matt "Ron Coderre" wrote: Here you go.... Your example began in Col_A, so no offset adjustment was necessary. With the new information, try this: =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a") or this (with a hardcoded offset of 10) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7, LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... Thanks Ron The formula for the first value works fine however the formula for the last value returns a #REF!. The specific formula that I am using is as follows (different range of data obviously) =IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a") What do you think? -- Regards Matt "Ron Coderre" wrote: With your posted data in A1:F4 This formula returns the data corresponding to the first 1 in Row_2: G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a") ....and this one returns the data corresponding to the last 1 in Row_2: H2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a") Format those cells as Dates. Copy those formulas down as far as you need. In the above example, G2 returns 01/07/2008 H2 returns 02/04/2008 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Matt" wrote in message ... I have a spreadsheet with data as follows: A B C D E F 1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08 2 1 1 1 1 1 3 1 1 1 1 1 4 1 1 1 1 I am using the sheet to show manning levels for each week of a project. Row 1 contains the dates of each week. The rows beneath contain a "1" in each cell when an employee will be on site. I am trying to lookup the first value in each row and return the relevant date from row 1 above (eg on row 3 the required result would be 01/14/08). Similarly, I would also like to lookup the last value in each row and return the date from row 1 above (eg on row 4 the required result would be 02/11/08). Any help with this would be greatly appreciated. Thank you. -- Regards Matt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| Lookup Value in Range/Array and Return Column Header Value | Excel Discussion (Misc queries) | |||
| Lookup Value in Range/Array and Return Column Header Value | Excel Worksheet Functions | |||
| Lookup values in one column to return another | Excel Worksheet Functions | |||
| Return Column header, if row value is > X | Excel Discussion (Misc queries) | |||
Linear Mode
