If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Embedded lookup
Can a lookup be embedded in another lookup? A file has separate sheets for
each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the lookup function. |
#2
|
|||
|
|||
Embedded lookup
Take a look at using the INDIRECT function, perhaps something like:
=VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) -- Best Regards, Luke M "HotaG" wrote in message ... Can a lookup be embedded in another lookup? A file has separate sheets for each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the lookup function. |
#3
|
|||
|
|||
Embedded lookup
Modified to allow numeric value for month in A1:
=VLOOKUP(A3,INDIRECT("'"&LOOKUP(A1,{1,2,3,4,5,6,7, 8,9,10,11,12}, {"January","February","March","April","May","June" , "July","August","September","October","November"," December"})& "'!A:B),2,FALSE) "Luke M" wrote in message .. . Take a look at using the INDIRECT function, perhaps something like: =VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) -- Best Regards, Luke M "HotaG" wrote in message ... Can a lookup be embedded in another lookup? A file has separate sheets for each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the lookup function. |
#4
|
|||
|
|||
Embedded lookup
Erm, got a bit carried away there, try this instead:
=VLOOKUP(A3,INDIRECT("'"&TEXT(DATE(2010,A1,1),"mmm m")& "'!A:B),2,FALSE) "Steve Dunn" wrote in message ... Modified to allow numeric value for month in A1: =VLOOKUP(A3,INDIRECT("'"&LOOKUP(A1,{1,2,3,4,5,6,7, 8,9,10,11,12}, {"January","February","March","April","May","June" , "July","August","September","October","November"," December"})& "'!A:B),2,FALSE) "Luke M" wrote in message .. . Take a look at using the INDIRECT function, perhaps something like: =VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) -- Best Regards, Luke M "HotaG" wrote in message ... Can a lookup be embedded in another lookup? A file has separate sheets for each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the lookup function. |
Thread Tools | |
Display Modes | |
|
|