Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Content of cell in Vlookup function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Content of cell in Vlookup function
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/sh...ad.php?t=45493 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Content of cell in Vlookup function
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" wrote in message ... 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/sh...ad.php?t=45493 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Content of cell in Vlookup function
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/sh...ad.php?t=45493 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup - extracting cell content from external spread sheet | Excel Worksheet Functions | |||
Conditional formatting formula that uses VLookup, based on content of another cell | Excel Discussion (Misc queries) | |||
Conditional formatting formula that uses VLookup, based on content of another cell | Excel Discussion (Misc queries) | |||
is there a function for getting the content of a cell? | Excel Worksheet Functions | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) |