| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect formula help
i need to use a formula in which the variable part is the worksheet name, i'm
trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
How do you want the A2 reference to update? By column or row or both?
Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
both please!
"Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
Try this:
=INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
Thanks, I was stuck with how to get the column part to show up as a letter!
So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW() Thanks so much for your help! "Biff" wrote: Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
You're welcome. Thanks for the feedback!
Biff "ivory_kitten" wrote in message ... Thanks, I was stuck with how to get the column part to show up as a letter! So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW() Thanks so much for your help! "Biff" wrote: Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
Hi Biff
Just a small point. Whilst this may well work for the OP's desired range, the formula will fail once you go past column Z. An alternative might be =OFFSET(INDIRECT($A$1 & "!$A$1"),ROW(1:1),COLUMN()-1) -- Regards Roger Govier "Biff" wrote in message ... Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
the formula will fail once you go past column Z.
Yes, I know. I would have dealt with it if needed. Biff "Roger Govier" wrote in message ... Hi Biff Just a small point. Whilst this may well work for the OP's desired range, the formula will fail once you go past column Z. An alternative might be =OFFSET(INDIRECT($A$1 & "!$A$1"),ROW(1:1),COLUMN()-1) -- Regards Roger Govier "Biff" wrote in message ... Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
Ok, so I have used this to import my data, however depending on the A$1 some
of my tables are smaller than others which is making my lookup functions not return properly! How can I stop this? "Biff" wrote: Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
How about providing an explanation of what you're trying to do. What lookup
functions? What lookup tables? Where are the tables? Where is the lookup value? Biff "ivory_kitten" wrote in message ... Ok, so I have used this to import my data, however depending on the A$1 some of my tables are smaller than others which is making my lookup functions not return properly! How can I stop this? "Biff" wrote: Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect formula help
Hi Biff,
Thanks for your help, I figured this one out already, i have to use offset instead of index :) "Biff" wrote: How about providing an explanation of what you're trying to do. What lookup functions? What lookup tables? Where are the tables? Where is the lookup value? Biff "ivory_kitten" wrote in message ... Ok, so I have used this to import my data, however depending on the A$1 some of my tables are smaller than others which is making my lookup functions not return properly! How can I stop this? "Biff" wrote: Try this: =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2)) Biff "ivory_kitten" wrote in message ... both please! "Biff" wrote: How do you want the A2 reference to update? By column or row or both? Biff "ivory_kitten" wrote in message ... i need to use a formula in which the variable part is the worksheet name, i'm trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the worksheet name, but when i copy my formula to the other cells, the A2 part does not update how do i make it automatically update to the current cell reference? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Invalid Formula with the use of Indirect | Excel Worksheet Functions | |||
| vlookup + indirect formula | Excel Discussion (Misc queries) | |||
| Indirect formula using Data Validation List of Worksheet Tabs | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Indirect used in an array formula | Excel Worksheet Functions | |||
Linear Mode
