Content of cell in Vlookup function "Ernie Fenwick" <fenwick[ at ]leahall.co.uk> 12/31/2008 3:56:03 PM Hi I have 52 files named "Losses " plus 2 digit week no 01 to 52 plus ".xls" I have a summary sheet with week numbers in row 2 E to AZ and branch locations in column C. I want to summarise the losses for each branch for each week no. I have used a VLOOKUP function which I can manually replicate for each of the 52 weeks, but it would be nice to pickup the week number from row 2 and add it to "Losses " and have the formula replcated across the summary sheet. I have tried looking at INDIRECT and using the ampersand but I just can't seem to get it right.when I incorporate it into VLOOKUP. Any help would be appreciated Ernie

Re: Content of cell in Vlookup function JBeaucaire <JBeaucaire.3laku9[ at ]thecodecage.com> 12/31/2008 4:41:37 PM VLOOKUP with INDIRECT cell references is the answer to your question, but it will only work if the sheets are open. You can't INDIRECTLY reference sheets that are closed. You can directly reference them, so if you need to do that, you'll have to manually create the references. If you're trying to display the information in cell A1 from the sheet Losses12.xls, and the number 12 was in the cell B5 on the current sheet, the INDIRECT formula would be: =INDIRECT(\"[LOSSES\"&B5&\".XLS]SHEET1!A1\") [/B] IF YOU HAD A SIMPLE VLOOKUP: *=VLOOKUP(_1_, A1:B20,2,FALSE) ...and wanted to insert the INDIRECT formula so that the lookup value came from the other sheet, just insert it into the underlined field: [B]=VLOOKUP(*_*INDIRECT(\"[Losses\"&B5&\".xls]Sheet1!A1\")*_*, A1:B20,2,false)* -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45493

Re: Content of cell in Vlookup function "Ernie Fenwick" <fenwick[ at ]leahall.co.uk> 12/31/2008 6:35:24 PM I have created a simple test sheet with just 12 in B5 and trying to pick up the value from Losses 12.xls which is currently open. I then get a "The formula you typed contains an error" dialog which highlights \"[LOSSES\" as the error. I have tried removing the \ symbols and then get #REF! error Regards Ernie "JBeaucaire" <JBeaucaire.3laku9[ at ]thecodecage.com> wrote in message news:JBeaucaire.3laku9[ at ]thecodecage.com... [Quoted Text] > > VLOOKUP with INDIRECT cell references is the answer to your question, > but it will only work if the sheets are open. You can't INDIRECTLY > reference sheets that are closed. You can directly reference them, so if > you need to do that, you'll have to manually create the references. > > If you're trying to display the information in cell A1 from the sheet > Losses12.xls, and the number 12 was in the cell B5 on the current sheet, > the INDIRECT formula would be: > > =INDIRECT(\"[LOSSES\"&B5&\".XLS]SHEET1!A1\") > [/B] > IF YOU HAD A SIMPLE VLOOKUP: > *=VLOOKUP(_1_, A1:B20,2,FALSE) > ..and wanted to insert the INDIRECT formula so that the lookup value > came from the other sheet, just insert it into the underlined field: > > [B]=VLOOKUP(*_*INDIRECT(\"[Losses\"&B5&\".xls]Sheet1!A1\")*_*, > A1:B20,2,false)* > > > -- > JBeaucaire > ------------------------------------------------------------------------ > JBeaucaire's Profile: > http://www.thecodecage.com/forumz/member.php?userid=73 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45493 >

Re: Content of cell in Vlookup function JBeaucaire <JBeaucaire.3lary2[ at ]thecodecage.com> 12/31/2008 7:16:49 PM I don't use spaces in workbook names, sheetnames, range names, anything. I try not to use them anywhere, it's too much hassle remembering to account for them. It looks like your workbook has a space in it, make sure the space is accounted for in the INDIRECT formula. =INDIRECT("[Losses "&B5&".xls]Sheet1!A1") Notice this version has the space after the "Losses" -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45493