Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving worksheet references in functions
I'm trying to create a formula so when i copy it across a couple columns it
will refer to the column header which are also worksheet names. Then, as the worksheet name in the column header changes the function will look at that page. For example, let's say the column headers below also refer to worksheet names and I want to return what is in cell A1 of each worksheet. Jon Bob Steve ='Sheetname!'A1 What I'd like to do is just copy the formula across so it refers to Jon, Bob, and Steve and returns A1. Does this make sense, and is it possible. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving worksheet references in functions
Assuming Jon is in cell B2 you could use a formula like this...
=indirect("'" & B2 & "'!A1") Indirect takes a text string that is a cell address and returns the value from that cell. Note that this function is volatile which means that it has a lot of overhead associated with it. If you have thousands of such formulas you will probably see a substantial drop in performance for you spreadsheet. -- HTH... Jim Thomlinson "scuba" wrote: I'm trying to create a formula so when i copy it across a couple columns it will refer to the column header which are also worksheet names. Then, as the worksheet name in the column header changes the function will look at that page. For example, let's say the column headers below also refer to worksheet names and I want to return what is in cell A1 of each worksheet. Jon Bob Steve ='Sheetname!'A1 What I'd like to do is just copy the formula across so it refers to Jon, Bob, and Steve and returns A1. Does this make sense, and is it possible. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving worksheet references in functions
Hmmm....didn't seem to work.
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving worksheet references in functions
Nevermind, now it works, just had a typo.
"scuba" wrote: Hmmm....didn't seem to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Workbook References and Functions | Excel Discussion (Misc queries) | |||
External References in Functions | Excel Worksheet Functions | |||
How to use complex no. functions that have cell references? | Excel Worksheet Functions | |||
Can I use variables for workheet name references in Excel functions? | Excel Discussion (Misc queries) | |||
Can I use variables for workheet name references in Excel functions? | Excel Worksheet Functions |