Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing parts of formula
Is there any way I can adapt how a formula copies across, apart from doing it
manually? I want part of the formula to change. For example Sheet1!$a2 when copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3 and then Sheet3!$a2. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing parts of formula
Enter this in A1 of a new sheet then drag across.
=INDIRECT("Sheet" & (COLUMN()) & "!A2") Gord Dibben MS Excel MVP On Sun, 31 Aug 2008 05:54:00 -0700, Chay wrote: Is there any way I can adapt how a formula copies across, apart from doing it manually? I want part of the formula to change. For example Sheet1!$a2 when copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3 and then Sheet3!$a2. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing parts of formula
BIG thanks to Gord and Roger ! Will save me hours of manual entry !
"Roger Govier" wrote: Hi Gord I think the OP wanted to step up the row number as well each time, so I suspect you meant to write =INDIRECT("Sheet" & COLUMN() & "!A" & COLUMN()+1) -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Enter this in A1 of a new sheet then drag across. =INDIRECT("Sheet" & (COLUMN()) & "!A2") Gord Dibben MS Excel MVP On Sun, 31 Aug 2008 05:54:00 -0700, Chay wrote: Is there any way I can adapt how a formula copies across, apart from doing it manually? I want part of the formula to change. For example Sheet1!$a2 when copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3 and then Sheet3!$a2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parts Per Million Formula | Excel Discussion (Misc queries) | |||
formula for separating parts of cells | Excel Worksheet Functions | |||
Formula needed for my parts list | Excel Discussion (Misc queries) | |||
Formula to pick out common parts of a cell and drop them in new sh | Excel Discussion (Misc queries) | |||
How do I set up a formula for parts (or units) per hour? | Excel Worksheet Functions |