Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with building a formula based on cell values
I need help building a formula.
I have a cell whose formula is ='[Source Data.xls]200709'!$J$6 This, of course, means go to the "Source Data.xls" file, "200709" worksheet, and copy cell "$J$6". This works well. I have a need to find a way to to substitute the value in a cell for the "200709". for example, suppose me spread sheet looked like this: A B 1 Date sales 2 200708 x 3 200709 y 4 200710 z Then the value for the sales cells would be ='[Source Data.xls]200708'!$J$6 ='[Source Data.xls]200709'!$J$7 ='[Source Data.xls]200710'!$J$8 However, I have literally hundreds of these cells to do. So I would like to do something like this ='[Source Data.xls]"Reference A2"'!$J$6 ='[Source Data.xls]"Reference A3"'!$J$7 ='[Source Data.xls]"Reference A4"'!$J$8 where the "Reference A2" would cause the command to be evalusted with the value in cell A2. Is this possible? Thanks, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with building a formula based on cell values
In B2: =Indirect("'[Source Data.xls]" & A2 & "'!$J$" & row()+4)
Source Data.xls must be open for this to work. Indirect does not support going to a closed workbook in xl2003 and earlier. -- Regards, Tom Ogilvy " wrote: I need help building a formula. I have a cell whose formula is ='[Source Data.xls]200709'!$J$6 This, of course, means go to the "Source Data.xls" file, "200709" worksheet, and copy cell "$J$6". This works well. I have a need to find a way to to substitute the value in a cell for the "200709". for example, suppose me spread sheet looked like this: A B 1 Date sales 2 200708 x 3 200709 y 4 200710 z Then the value for the sales cells would be ='[Source Data.xls]200708'!$J$6 ='[Source Data.xls]200709'!$J$7 ='[Source Data.xls]200710'!$J$8 However, I have literally hundreds of these cells to do. So I would like to do something like this ='[Source Data.xls]"Reference A2"'!$J$6 ='[Source Data.xls]"Reference A3"'!$J$7 ='[Source Data.xls]"Reference A4"'!$J$8 where the "Reference A2" would cause the command to be evalusted with the value in cell A2. Is this possible? Thanks, James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with building a formula based on cell values
Use INDIRECT. Something like:
=INDIRECT("[Book2]"& B1 & "!$B$2",TRUE) where B1 contains: Sheet1 Of course both books must be open -- Gary''s Student - gsnu2007 " wrote: I need help building a formula. I have a cell whose formula is ='[Source Data.xls]200709'!$J$6 This, of course, means go to the "Source Data.xls" file, "200709" worksheet, and copy cell "$J$6". This works well. I have a need to find a way to to substitute the value in a cell for the "200709". for example, suppose me spread sheet looked like this: A B 1 Date sales 2 200708 x 3 200709 y 4 200710 z Then the value for the sales cells would be ='[Source Data.xls]200708'!$J$6 ='[Source Data.xls]200709'!$J$7 ='[Source Data.xls]200710'!$J$8 However, I have literally hundreds of these cells to do. So I would like to do something like this ='[Source Data.xls]"Reference A2"'!$J$6 ='[Source Data.xls]"Reference A3"'!$J$7 ='[Source Data.xls]"Reference A4"'!$J$8 where the "Reference A2" would cause the command to be evalusted with the value in cell A2. Is this possible? Thanks, James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with building a formula based on cell values
On Sep 28, 2:37 pm, Gary''s Student
wrote: Use INDIRECT. Something like: =INDIRECT("[Book2]"& B1 & "!$B$2",TRUE) where B1 contains: Sheet1 Of course both books must be open -- Gary''s Student - gsnu2007 Thanks. I tried this and I am almost there. The reason is I forgot to mention that the value of the worksheet is caluclated off of a data and not in a cell. So I have no vlue in B1, but I do calculate the vlue of the worksheet from a cell. Is it possible to use a formula instead of a cell? Thanks for all posts so far. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for changing cell format based on more than 3 values | Excel Worksheet Functions | |||
Using Formula based Cell Content Return Unique Consecutive Duplicate Values | Excel Worksheet Functions | |||
Building Excel Formula that Returns the Column of Last Cell with Data | Excel Programming | |||
formula for named cell/range based on cell values | Excel Programming | |||
building a list in a col...based on another col | Excel Programming |