update vlookup formula sheet reference for multiple sheets
in [Functions]
|
From: SRH on 7 May 2010 20:30 In Excel 2003 Starting with this formula I need to have the sheet name change to each tab available on the sheet. =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) Other tab names are: 4-11 to 4-17 4-18 to 4-24 4-25 to 5-1 Looking for a more automated way to create the following other than manually change the sheet name. =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) I think I am seeing a possibility to use INDIRECT but not sure how. Or is this VBA stuff? -- SRH
From: Dave Peterson on 7 May 2010 21:03 I'm not quite sure what you're doing, but maybe you could insert a new row 1. Then put the worksheet names in B1, C1, D1, ... Then you could use a formula like: =vlookup($a$2,indirect("'" & b$1 & "'!A:P"),13,false) and drag to the right and then drag down the data??? SRH(a)Boise wrote: > > In Excel 2003 > Starting with this formula I need to have the sheet name change to each tab > available on the sheet. > =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) > Other tab names are: > 4-11 to 4-17 > 4-18 to 4-24 > 4-25 to 5-1 > > Looking for a more automated way to create the following other than manually > change the sheet name. > =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) > > I think I am seeing a possibility to use INDIRECT but not sure how. Or is > this VBA stuff? > > -- > SRH -- Dave Peterson
From: L. Howard Kittle on 8 May 2010 00:17 Hi SRH @ spudville, I think this rather complicated vlookup formula I got from Peo Sjoblom will do what you want. You enter a value in the lookup value cell and this formula will look across many sheet tabs in the workbook. (I note that you said "...each tab available on the sheet" and I assume you mean on the workbook.) It looks formitable but is not too bad to walk through and adapt to your workbook. I am able to work with it but cannot explain to any satisfaction why it works, even with Peo's four page e-mail on how it works. =VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),3,0) Where: A1 is the lookup value on the sheet holding the formula. MySheets is a named range of all the sheets you want to conduct the lookup. Make a list of the sheet names, select them and name it. A2:A200 are the columns on EACH sheet and is the Table_Array which you want to look up. (Yours might look like A1:P200, which is 16 columns and you want to return the 13th column.) You will need to change the third argument of 3 in the sample to 13 for your use to return the 13th column. You must Array Enter the formula uaing CTRL+SHIFT+ENTER. If you make changes to the formula you will again have to use Ctrl+Shift+Enter to commit. Problems...? Post back. HTH Regards, Howard "SRH(a)Boise" <SRHBoise(a)discussions.microsoft.com> wrote in message news:7E170099-169D-49F7-893E-EC9388709F69(a)microsoft.com... > In Excel 2003 > Starting with this formula I need to have the sheet name change to each > tab > available on the sheet. > =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) > Other tab names are: > 4-11 to 4-17 > 4-18 to 4-24 > 4-25 to 5-1 > > Looking for a more automated way to create the following other than > manually > change the sheet name. > =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) > > I think I am seeing a possibility to use INDIRECT but not sure how. Or is > this VBA stuff? > > -- > SRH
From: Jacob Skaria on 8 May 2010 02:18 Try the below formula You need to have the start date in a separate cell. In the below formula cell E1 holds the start date which is 4/4/2010 in excel date format.The below formula would build the sheets names as shown below.... =TEXT($E$1+((ROW(A1)-1)*7),"m-d") & " to " & TEXT($E$1+6+((ROW(A1)-1)*7),"m-d") 4-4 to 4-10 4-11 to 4-17 4-18 to 4-24 4-25 to 5-1 5-2 to 5-8 5-9 to 5-15 The below vlookup formula use the above indirect() formula to build the sheet name.. =VLOOKUP($A$1,INDIRECT("'" & TEXT($E$1+((ROW(A1)-1)*7),"m-d") & " to " & TEXT($E$1+6+((ROW(A1)-1)*7),"m-d") & "'!$A:$P"),13,0) -- Jacob (MVP - Excel) "SRH(a)Boise" wrote: > In Excel 2003 > Starting with this formula I need to have the sheet name change to each tab > available on the sheet. > =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) > Other tab names are: > 4-11 to 4-17 > 4-18 to 4-24 > 4-25 to 5-1 > > Looking for a more automated way to create the following other than manually > change the sheet name. > =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) > > I think I am seeing a possibility to use INDIRECT but not sure how. Or is > this VBA stuff? > > -- > SRH
From: L. Howard Kittle on 8 May 2010 03:09 <<This is a repost, first one did not seem to show up>> Hi SRH @ spudville, I think this rather complicated vlookup formula I got from Peo Sjoblom will do what you want. You enter a value in the lookup value cell and this formula will look across many sheet tabs in the workbook. (I note that you said "...each tab available on the sheet" and I assume you mean on the workbook.) It looks formitable but is not too bad to walk through and adapt to your workbook. I am able to work with it but cannot explain to any satisfaction why it works, even with Peo's four page e-mail on how it works. =VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),3,0) Where: A1 is the lookup value on the sheet holding the formula. MySheets is a named range of all the sheets you want to conduct the lookup. Make a list of the sheet names, select them and name it. A2:A200 are the columns on EACH sheet and is the Table_Array which you want to look up. (Yours might look like A1:P200, which is 16 columns and you want to return the 13th column.) You will need to change the third argument of 3 in the sample to 13 for your use to return the 13th column. You must Array Enter the formula uaing CTRL+SHIFT+ENTER. If you make changes to the formula you will again have to use Ctrl+Shift+Enter to commit. Problems...? Post back. HTH Regards, Howard "SRH(a)Boise" <SRHBoise(a)discussions.microsoft.com> wrote in message news:7E170099-169D-49F7-893E-EC9388709F69(a)microsoft.com... > In Excel 2003 > Starting with this formula I need to have the sheet name change to each > tab > available on the sheet. > =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) > Other tab names are: > 4-11 to 4-17 > 4-18 to 4-24 > 4-25 to 5-1 > > Looking for a more automated way to create the following other than > manually > change the sheet name. > =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) > > I think I am seeing a possibility to use INDIRECT but not sure how. Or is > this VBA stuff? > > -- > SRH
|
Next
|
Last
Pages: 1 2 Prev: Question using countif Next: Colour code specific name if it appears in a group of names |