Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Concatenating Functions
I have three (3) worksheets within a single workbook and am attempting to concatenate some fieilds from the first two (2) sheets and place them into the third sheet. This is what I have: WorkSheet1 - NamedField1 - NamedField2 WorkSheet2 - NamedField1 - NamedField2 WorkSheet3 Within the first two sheets I have some fields that are named the same. In the third sheet I have to build my formula based upon some of the values within it which results in referrencing the necessary WorkSheet and Namedfield. In other words I am trying to build something like this in worksheet #3. =concatenate("='",A8,"'!NamedField",B2) A8 is a field within worksheet #3 with an actual value of WorkSheet1 or WorkSheet2. B2 is a field within worksheet #3 with an actual value range of 1-24. So the final result should be something like: ='WorkSheet1'!NamedField1 or ='WorkSheet1'!NamedField2 And it would then display the particular value that is within the referenced field(s). However, when I do use the =concatenate("='",A8,"'!NamedField",B2) I only get the concatenated result ='WorkSheet1'!NamedField2 and not the referenced field value. Anyone out there able to assist me with this? Thanks in advance!!! Michael -- neominds ------------------------------------------------------------------------ neominds's Profile: http://www.excelforum.com/member.php...o&userid=25204 View this thread: http://www.excelforum.com/showthread...hreadid=386910 |
#2
|
|||
|
|||
One way:
=INDIRECT("'" & A8 & "'!NamedField" & B2) In article , neominds wrote: I have three (3) worksheets within a single workbook and am attempting to concatenate some fieilds from the first two (2) sheets and place them into the third sheet. This is what I have: WorkSheet1 - NamedField1 - NamedField2 WorkSheet2 - NamedField1 - NamedField2 WorkSheet3 Within the first two sheets I have some fields that are named the same. In the third sheet I have to build my formula based upon some of the values within it which results in referrencing the necessary WorkSheet and Namedfield. In other words I am trying to build something like this in worksheet #3. =concatenate("='",A8,"'!NamedField",B2) A8 is a field within worksheet #3 with an actual value of WorkSheet1 or WorkSheet2. B2 is a field within worksheet #3 with an actual value range of 1-24. So the final result should be something like: ='WorkSheet1'!NamedField1 or ='WorkSheet1'!NamedField2 And it would then display the particular value that is within the referenced field(s). However, when I do use the =concatenate("='",A8,"'!NamedField",B2) I only get the concatenated result ='WorkSheet1'!NamedField2 and not the referenced field value. Anyone out there able to assist me with this? Thanks in advance!!! Michael |
#3
|
|||
|
|||
If I understand what you want, try using the indirect function.
=indirect(concatenate("='",A8,"'!NamedField",B2)) "neominds" wrote: I have three (3) worksheets within a single workbook and am attempting to concatenate some fieilds from the first two (2) sheets and place them into the third sheet. This is what I have: WorkSheet1 - NamedField1 - NamedField2 WorkSheet2 - NamedField1 - NamedField2 WorkSheet3 Within the first two sheets I have some fields that are named the same. In the third sheet I have to build my formula based upon some of the values within it which results in referrencing the necessary WorkSheet and Namedfield. In other words I am trying to build something like this in worksheet #3. =concatenate("='",A8,"'!NamedField",B2) A8 is a field within worksheet #3 with an actual value of WorkSheet1 or WorkSheet2. B2 is a field within worksheet #3 with an actual value range of 1-24. So the final result should be something like: ='WorkSheet1'!NamedField1 or ='WorkSheet1'!NamedField2 And it would then display the particular value that is within the referenced field(s). However, when I do use the =concatenate("='",A8,"'!NamedField",B2) I only get the concatenated result ='WorkSheet1'!NamedField2 and not the referenced field value. Anyone out there able to assist me with this? Thanks in advance!!! Michael -- neominds ------------------------------------------------------------------------ neominds's Profile: http://www.excelforum.com/member.php...o&userid=25204 View this thread: http://www.excelforum.com/showthread...hreadid=386910 |
#4
|
|||
|
|||
Don't put "quotes" around your named fields -- it thinks they're text. Your concatenate formula should work fine. You may need to add spaces " " to get it to read right . . . -- besbaum ------------------------------------------------------------------------ besbaum's Profile: http://www.excelforum.com/member.php...o&userid=25208 View this thread: http://www.excelforum.com/showthread...hreadid=386910 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
Default User Defined Functions - How? | Excel Discussion (Misc queries) | |||
PASTE DOWN FUNCTIONS | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions |