Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 24th 08, 02:02 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 21
Default Indirect function – relative column copy/paste

I’m using INDIRECT to allow me to refer to a previous month’s 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 I’ve 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 can’t think of how to generate this. Any ideas?

Regards


  #2   Report Post  
Old September 24th 08, 02:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,240
Default Indirect function – relative column copy/paste

Gary T wrote:
I’m using INDIRECT to allow me to refer to a previous month’s 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 I’ve 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 can’t 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy and Paste of Relative Cell References Skyscan Excel Worksheet Functions 1 August 20th 07 07:39 AM
How do I copy an absolute column and relative row to the right? Fusterated Excel Discussion (Misc queries) 1 March 23rd 06 08:49 PM
Copy/Paste of QueryTable doesn't adjust relative parameters btait1 Excel Discussion (Misc queries) 1 September 30th 05 06:51 PM
INDIRECT Function impact on Copy Worksheet BG Excel Worksheet Functions 5 July 13th 05 02:29 AM
How to paste INDIRECT function to range of cells? Mike Williams Excel Worksheet Functions 4 March 18th 05 03:02 AM


All times are GMT +1. The time now is 04:46 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017