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,'44 to 410'!$A:$P,13,FALSE) Other tab names are: 411 to 417 418 to 424 425 to 51 Looking for a more automated way to create the following other than manually change the sheet name. =VLOOKUP($A$1,'411 to 417'!$A:$P,13,FALSE) =VLOOKUP($A$1,'418 to 424!$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,'44 to 410'!$A:$P,13,FALSE) > Other tab names are: > 411 to 417 > 418 to 424 > 425 to 51 > > Looking for a more automated way to create the following other than manually > change the sheet name. > =VLOOKUP($A$1,'411 to 417'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'418 to 424!$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 email 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:7E170099169D49F7893EEC9388709F69(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,'44 to 410'!$A:$P,13,FALSE) > Other tab names are: > 411 to 417 > 418 to 424 > 425 to 51 > > Looking for a more automated way to create the following other than > manually > change the sheet name. > =VLOOKUP($A$1,'411 to 417'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'418 to 424!$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),"md") & " to " & TEXT($E$1+6+((ROW(A1)1)*7),"md") 44 to 410 411 to 417 418 to 424 425 to 51 52 to 58 59 to 515 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),"md") & " to " & TEXT($E$1+6+((ROW(A1)1)*7),"md") & "'!$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,'44 to 410'!$A:$P,13,FALSE) > Other tab names are: > 411 to 417 > 418 to 424 > 425 to 51 > > Looking for a more automated way to create the following other than manually > change the sheet name. > =VLOOKUP($A$1,'411 to 417'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'418 to 424!$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 email 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:7E170099169D49F7893EEC9388709F69(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,'44 to 410'!$A:$P,13,FALSE) > Other tab names are: > 411 to 417 > 418 to 424 > 425 to 51 > > Looking for a more automated way to create the following other than > manually > change the sheet name. > =VLOOKUP($A$1,'411 to 417'!$A:$P,13,FALSE) > =VLOOKUP($A$1,'418 to 424!$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 