Hiya I have a workbook with a summary page that lists various data from the other sheets in the workbook. I have the sheet names listed on the summary page using VBA, and now want to ruse VLookup to reference the listed hseet names and return the data.
For instance the list of sheet names is in Col A, and in Col B is the following formula:
=VLOOKUP("Hours",SHEETNAME!A1:B6,100,FALSE) where SHEETNAME is got by manually typing in the worksheet name - I would instead like to reference the sheet names in Col A.
Hope that makes sense,
Thanks in advance
Nina
Last edited by boatbabe; 08-24-2011 at 04:56 PM.
If your sheet name is in, say, cell A1 you could use:
=VLOOKUP("Hours",INDIRECT("'" & A1 & "'!A1:B6"),100,FALSE)
Does that help?
That seems to work, thank you. I do have a REF issue but not sure what it is relating to, have studied the formula for ages but can not see an error in it. For some reason I can't upload the zip file but if anyone can see the error here I would be gratefulOriginally Posted by Andrew-R
=VLOOKUP(J1,INDIRECT("'"&A5&"'!b129:ea163"),MATCH(C4,INDIRECT("'"&A5&"'!cw7:ea7",0),FALSE))
Thanks
You haven't closed off the INDIRECT bracket in the match statement.
Try:
=VLOOKUP(J1,INDIRECT("'"&A5&"'!b129:ea163"),MATCH(C4,INDIRECT("'"&A5&"'!cw7:ea7"),0),FALSE))
I knew it was an obvious one, thank you very much :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks