Concatenate to make a formula

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 6, 2006

• #2

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

 

Upvote 0

NBVC

Well-known Member

Joined

Aug 31, 2005

Messages

5,828

• Nov 6, 2006

• #3

Try:

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

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,616

Office Version

1. 365

Platform

1. Windows

• Nov 6, 2006

• #4

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.

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 6, 2006

• #5

DOH! Shoulda caught that...

 

Upvote 0

A

amorts

Board Regular

Joined

Jan 4, 2006

Messages

181

• Nov 6, 2006

• #6

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!

 

Upvote 0