Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect function relative column copy/paste
Im using INDIRECT to allow me to refer to a previous months cells (thanks
to helpful answers to another query I posted on these forums). The INDIRECT portion of the formula is currently: INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy")&"'!"&"AB$3:AB$358") And Ive entered the whole formula into cell IC3. The above INDIRECT formula allows me to perform functions on the range: 'May 2008'!AB$3:AB$358 (cell $A$1 that the indirect function refers to contains the date 01-Jun-08) What I would like to do is copy the formula in IC3 across to QB3, but for the column reference to change relatively (i.e. so that the formula in ID3 refers to AC$3:AC$358, the formula in IE3 refers to AD$3:AD$358, and so on). However, I cant think of how to generate this. Any ideas? Regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect function relative column copy/paste
Gary T wrote:
Im using INDIRECT to allow me to refer to a previous months cells (thanks to helpful answers to another query I posted on these forums). The INDIRECT portion of the formula is currently: INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy")&"'!"&"AB$3:AB$358") And Ive entered the whole formula into cell IC3. The above INDIRECT formula allows me to perform functions on the range: 'May 2008'!AB$3:AB$358 (cell $A$1 that the indirect function refers to contains the date 01-Jun-08) What I would like to do is copy the formula in IC3 across to QB3, but for the column reference to change relatively (i.e. so that the formula in ID3 refers to AC$3:AC$358, the formula in IE3 refers to AD$3:AD$358, and so on). However, I cant think of how to generate this. Any ideas? Regards =INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy") &"'!"&"R3C"&COLUMN()-209&":R358C"&COLUMN()-209,FALSE) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste of Relative Cell References | Excel Worksheet Functions | |||
How do I copy an absolute column and relative row to the right? | Excel Discussion (Misc queries) | |||
Copy/Paste of QueryTable doesn't adjust relative parameters | Excel Discussion (Misc queries) | |||
INDIRECT Function impact on Copy Worksheet | Excel Worksheet Functions | |||
How to paste INDIRECT function to range of cells? | Excel Worksheet Functions |