Concatenate a Formula

[Michael Conroy]

I have dozens of tabs in my file with a "topsheet" that has every tab name on
a separate row. I am trying to build a formula that pulls the contents of
cell V$25 and references the name of the tab from a column over. When I copy
the formula down, the tab name will change and it will pull the contents from
the next sheet. The concatenation looks OK, but I can't get Excel to
recognize it as a formula, I get the text of the formula, rather than the
value.
A B
Water1 ="trim('"&A1&"'!V$25)" displays proper formula without
equal sign
Water2 ="=trim('"&A2&"'!V$25)" equal sign appears no cell value
Water3 "=trim('"&A3&"'!V$25)" equal sign appears no cell value

And to be clear, I have included the single quotes on either side of the tab
name and the cell is formated as general. There must be some trick I am
missing. Thanks for any help you can give me.

 

[David Biddulph]

Of course you get the text of the formula, and not the value. You've got
double quote marks to ask it to do exactly that. Quote marks delimit a text
string.

It isn't clear exactly what you're trying to do, but I guess that you may
have intended to use the INDIRECT function.

 

[Dave Peterson]

=trim(indirect("'" & a1 & "'!v25"))

Since v25 is text (within those quotes), you don't need the $ to indicate
absolute.

 

[Michael Conroy]

Thanks for the reply, the indirect worked. However, in the past, there was a
way to do what I was originally trying to do. Because with indirect, the V25
won't change to W25 when copied to the next column over. I realize that was
not in my original post, but after 20 years of spreadsheet work I have never
heard of or uesd indirect and there is a way to accomplish that task. I will
use and learn about indirect, a new function for me. Thanks for the help.
--
Michael Conroy
Stamford, CT

=trim(indirect("'" & a1 & "'!v25"))

Since v25 is text (within those quotes), you don't need the $ to indicate
absolute.

 

[RagDyeR]

What you were originally trying to remember and do, is create a text
formula, which has the ability to increment and/or reference other cells
containing data to be incorporated into the formula.

The reason for using the syntax you're attempting, is to be able to
reference open or *closed* WBs by creating "direct" links to those WBs.

This is probably what you were trying to do (remember):

="='"&TRIM(A1)&"'!V$25"

AND ... the "trick", as you call it, was to copy the above text formula,
then Paste Special, and choose "Values".

THEN ... with the formula(s) selected, use:
<Data> <TextToColumns> <Delimited> <Finish>

And you'll have your linked formulas returning the contents of V25 from all
the WBs in your list.
You can then copy these formulas across columns to reference the other
columns in the WBs your interested in.


The Indirect() function will *not* work on closed WBs, but since you're
working within a single WB, it can be used to do the same job that the text
formula would perform, and without the hassle of copying and re-registering
the text formula to convert it into a working formula.

For example:

=INDIRECT("'"&TRIM($A1)&"'!V25")

Would work fine, and if you wanted to be able to copy it across, and access
other columns, you might create column headers with the appropriate column
letters and the formulas below would reference those headers, also using
Indirect().

With Sheet list starting in A2,
And B1 to F1 containing V, W, X, Y, and Z, starting in B2 you might use:

=INDIRECT("'"&TRIM($A2)&"'!"&B$1&"25")

And then copy across and down as needed.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks for the reply, the indirect worked. However, in the past, there was a
way to do what I was originally trying to do. Because with indirect, the V25
won't change to W25 when copied to the next column over. I realize that was
not in my original post, but after 20 years of spreadsheet work I have never
heard of or uesd indirect and there is a way to accomplish that task. I will
use and learn about indirect, a new function for me. Thanks for the help.
--
Michael Conroy
Stamford, CT

=trim(indirect("'" & a1 & "'!v25"))

Since v25 is text (within those quotes), you don't need the $ to indicate
absolute.