Linking to sheets in excel

[shep6]

Hi there

Using the vlookup function I need to lookup values in a different workbook, the problem I have is that the workbook which contains the values has sheet names which are enclosed in square brackets (eg "[Sheet1]". This is because the data in the sheets is loaded into a database, using the sheet names for table references. Therefore to change the sheet names in the workbook isn't possible.

When I create my vlookup I get a message telling me that I have entered an incorrect workbook name/path, because excel is looking at the sheet name as part of the workbook name, because of the square brackets.

Hope this makes sense! Is there anyway round this?

thanks

 

[dan1967]

I would open one of these source workbooks and enter the formula in your summary workbood by clicking on the source cell references, close the source workbook, after saving your formula, and seeing how Excel reconciles this problem. You can then copy that same reconciliation to your other formulas.

Dan.

 

[shep6]

I've tried opening both the source and summary workbooks and simply clicking in the source workbook to see what happens, when I try to set a cell reference in the summary and I receive the same message from excel, "Invalid external reference to a worksheet, check the path and name".

cheers

 

[dan1967]

I can't even place a "[" or "]" in my sheet names to try different things. Could you remove them from the names and in Access add them back programatically?

"[" & Sheet1 & "]"

Without being able to test it, this is my only other idea. I'm sorry.

Dan.

 

[shep6]

Thanks anyway Dan.

The source workbook is maintained by our IT dept, therefore any small change like removing the "[" is likely to be considered a major change by them!

I found when I was trying different things out that I couldn't enter a "[" in the sheet name without a preceding space or other text.

Not sure if there is something I could enclose the workbook name in the formula, to separate it from the sheet name....

 

[dan1967]

Sorry I couldn't help more. The brackets in the sheet name are what needs to change, I think.

Dan.