Excel Combining Cells into a Formula

[SteveJR73]

Welcome beginner. What is your question or comment?
Please try to briefly and clearly tell us: What you want, What you tried, and What happened.
-----------------------------------------------------------------------------------------------------------
In a new spreadsheet I am currently trying to use the values of two cells to create part of a formula

e.g Cell A10 = O
Cell B1 = 5
and the formula I am trying to create is :='[2008 Holiday Planner.xls]Jan-Jun'!O5

What is the best way to combine the values of A10 and B1 to create "O5" in the formula?

Many Thanks
Steve

[Villeroy]

Code: Select all

=OFFSET('file:///C:/full/path/2008 Holiday Planner.xls'#$Jan-Jun.$A$1 ; $B$1 ; $A$1)

... where A1 has column offset 14 instead of letter "O" and B1 has the row offset 4. 14 columns and 4 rows offset from A1 is O5.

If it has to be "O" and 15 for "O15:
A1: O
B1: 15
X1: =COLUMN(INDIRECT($A$1&"1"))-1 => 14
Y1: =$B$1-1 => 4

Code: Select all

=OFFSET('file:///C:/full/path/2008 Holiday Planner.xls'#$Jan-Jun.$A$1 ; $Y$1 ; $X$1)

[Dave]

and the formula I am trying to create is :='[2008 Holiday Planner.xls]Jan-Jun'!O5

Do you perhaps just mean this?

='[2008 Holiday Planner.xls]Jan-Jun'!&A10&B1?

The "&" concatenates text. [That's the ampersand symbol above the "7" key". It's a weird font here for me.]

David.

[jackgopher]

Dave is correct,

The easiest way is to use the ampersand character to combine cells into a string, or to use the "concatenate()" function, as in:
=concatenate('[2008 Holiday Planner.xls]Jan-Jun!',A10,B1)


______
Excel Formulas