substitute cell contents into formula 2

[wakeywakey]

Hi

I have a formula like:

='sheetname'!a1

but instead of hard coding sheetname, I want to take it from the contents of a cell.

For example, cell a1 contains the word SummarySheet and I want to write a formula equivalent to the first formula above that substitutes the contents of a1 as a variable sheetname.

Sometimes I think the questions are harder than the answers!

Thanks

 

[iGrant]

You can do this with the INDIRECT function. So, if A1 contains the text SummarySheet, then your formula in B1 could be:

=INDIRECT(A1&"!A1&quot

which Excel will interpret as a reference to SummarySheet!A1.

Note that if your other sheets have spaces in the names, you will need to concatenate some single quote marks around the reference too, which complicates things a little. Best not to have spaces in your sheet names if you can help it, though.

Hope this helps

 

[wakeywakey]

Just to confirm that I tested out your suggestion successfully, including the sheet name with spaces, which would be:

=INDIRECT("'"&A2&"'!a1&quot


Thanks!

 

[johnbailey]

Hi,

Is it possible to use this code for linking between files?

i.e use cell "A1" as a variable for a filename?

e.g A1 = "file1"

C:\documents\file1.xls

hope this makes sense.

Thanks

 

[Hasit]

John, yes it is possible. You could either use INDIRECT or the DGET function as well. So for example, I can create a file name called MathsClass.xls, using the the DGET function by listing out all the types of classes and placing an "X" next to the one I wish to choose and create a file name from. The X is related to choiceA in the formula below, while Classtype is a range which consists of Chemistry, Maths, PE, etc

=DGET(Classtype,1,choiceA)&"Class.xls"

If you want to use the MathsClass.xls file and reference a cell in that file, then using the INDIRECT function would help. So, if I used the following formula:

=INDIRECT("["&filenameA&"]"&"Class1!A1&quot

I could not only create a filename, but also reference cell A1 in sheet Class1 in the the range called filenameA (which in this case is the cell where I have got the DGET formula).

Hope this makes sense.

 

[johnbailey]

Thanks Hasit,

The formula used

=INDIRECT("'C:\["&filenameA&".xls]"&"sheet1'!a1&quot

I am having problems with the linking, They do not seem to be permanent links and when filenameA.xls is closed, the return cell changes to "#Ref!".

Is this because the indirect function is only suitable for open spreadsheets, or is my formula wrong?

Thanks

 

[Hasit]

John,

The formula is not wrong, but with the INDIRECT function, the file you are referencing has to be open and loaded into memory.

I apologise, I should have mentioned that before.

On another note, you may want to create a new thread and copy and paste the exchanges you and I have had because it is a slight different question to the one asked by "wakeywakey", which others may find useful.

 

[iGrant]

I've had the same problem as you, in that once the file (filenameA) is closed the INDIRECT function doesn't keep a record of the value that it previously used, so returns a #REF! error.

Can't think of any way round this, unless Hasit's DGET idea works (I've never tried this).

 

[Hasit]

Nope, DGET gives the same "error", which is a pain in the rear. However, one way I did get around having to remember to open multiple workbooks is to save them all in a workspace (XLW) and open the workspace up.

That seemed to work well for me.