Increment cell reference

**Larry Kahm** · April 8th 08, 01:28 AM posted to microsoft.public.excel.misc

=INDEX(INDIRECT("'"&$A3&"'!$G5"),COLUMNS($A:$A))

How can I get the variable, "G5" to increment when copied across a row or
down a column (i.e., $G5, $G6, $G7, etc)

I've tried copy and then paste special, formula, but it doesn't increment.
What should I be doing?

Thanks

Larry

**Pete_UK** · April 8th 08, 01:40 AM posted to microsoft.public.excel.misc

You have to move the 5 outside the quotes for it to increment, eg:

=INDEX(INDIRECT("'"&$A3&"'!$G"&ROW(A5)),COLUMNS($A :$A))

as this is copied down then the ROW(A5) (returning 5) will become
ROW(A6), ROW(A7), ROW(A8) etc, returning 6, 7, 8 etc

Hope this helps.

Pete

On Apr 8, 1:28*am, "Larry Kahm" >
wrote:
> =INDEX(INDIRECT("'"&$A3&"'!$G5"),COLUMNS($A:$A))
>
> How can I get the variable, "G5" to increment when copied across a row or
> down a column (i.e., $G5, $G6, $G7, etc)
>
> I've tried copy and then paste special, formula, but it doesn't increment.
> What should I be doing?
>
> Thanks
>
> Larry

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro - How to increment cell reference by one row	lau_ash[_2_]	Excel Worksheet Functions	6	June 6th 12 08:21 AM
How do I get a worksheet reference to increment when copied	Raf	Excel Worksheet Functions	9	March 25th 09 11:38 PM
Relative reference autofill increment other than +1	SteveB	Excel Discussion (Misc queries)	3	June 14th 05 07:40 PM
how to make cell address reference increment?	jacko	Excel Worksheet Functions	3	June 1st 05 05:33 PM
Row reference increment but preserve column reference	Pwanda	Excel Worksheet Functions	1	April 28th 05 01:12 PM