Increment cell reference

L

Larry Kahm

=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
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

constant in formulas 3
problem with formulas (2) 7
Cell Reference Question 3
How to simplify CONCATENATE 3
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
inserting a row WITH any needed formulas 3
Printing From An "Alert" Cell 8
Increment Cell Reference using Indirect 0

Top