Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 8th 08, 01:28 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 3
Default Increment cell reference

=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



  #2   Report Post  
Old April 8th 08, 01:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Increment cell reference

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




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


All times are GMT +1. The time now is 01:44 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