Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate a Formula
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. -- Michael Conroy Stamford, CT |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate a Formula
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. -- David Biddulph "Michael Conroy" wrote in message ... 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. -- Michael Conroy Stamford, CT |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate a Formula
=trim(indirect("'" & a1 & "'!v25"))
Since v25 is text (within those quotes), you don't need the $ to indicate absolute. Michael Conroy wrote: 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. -- Michael Conroy Stamford, CT -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate a Formula
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 "Dave Peterson" wrote: =trim(indirect("'" & a1 & "'!v25")) Since v25 is text (within those quotes), you don't need the $ to indicate absolute. Michael Conroy wrote: 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. -- Michael Conroy Stamford, CT -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate a Formula
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! ================================================== === "Michael Conroy" wrote in message ... 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 "Dave Peterson" wrote: =trim(indirect("'" & a1 & "'!v25")) Since v25 is text (within those quotes), you don't need the $ to indicate absolute. Michael Conroy wrote: 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. -- Michael Conroy Stamford, CT -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with concatenate formula | Excel Worksheet Functions | |||
Need help with concatenate formula | Excel Worksheet Functions | |||
Concatenate in a formula | Excel Worksheet Functions | |||
Evaluating results of a concatenate formula, as a formula | Excel Worksheet Functions | |||
CONCATENATE formula | Excel Discussion (Misc queries) |