Concatenate to make a formula

[amorts]

Each of my workbooks is called wkb#### where #### is a 4 digit number. In one column I have a list of these 4 digit numbers. However, I cannot work out how to concatenate the number into the formula as below.

I can create it as a text string but not as a formula.

=wkb7051!$B$3

 

[Greg Truby]

Let's say your 4-digit # is in A1, then use <ul>[*]=indirect("wkb" & $A$1 & "!$B$3")[/list]

 

[NBVC]

Try:

=INDIRECT("'wkb"&A1&"'!B3") where A1 houses your 1st set of 4-digits.

 

[Scott Huish]

The text string sample you gave would seem to indicate worksheets not workbooks, if a worksheet, you can use the example Greg Truby gave, if a workbook, you would also need to indicate the sheet and something like this:

=INDIRECT("["&A1&".xls]Sheet1!$B$3")

The workbook would need to be open, otherwise you might want to look at INDIRECT.EXT from the MOREFUNC add-in.

 

[Greg Truby]

DOH! Shoulda caught that...

 

[amorts]

No, sorry I should have caught that. I am fairly new to this and I accidentally named my worksheets wkb instead of wks.

I like the solution though, I have never seen or read about the INDIRECT key word in my books!