Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike Williams
 
Posts: n/a
Default How to paste INDIRECT function to range of cells?

IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4 increment
accordingly, so that in cell D5 the formula looks like this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1 (worksheetname)
reference increments or not, depending on the $, but the cell reference
doesn't increment, no matter what. Doing all this manually, one cell at a
time, will be a royal pain. Can someone please suggest a solution?

THANKS.
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Try this:

=INDIRECT(D$1&"!J"&ROW(4:4))

Copy as needed.

Biff

-----Original Message-----
IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from

thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4

increment
accordingly, so that in cell D5 the formula looks like

this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1

(worksheetname)
reference increments or not, depending on the $, but the

cell reference
doesn't increment, no matter what. Doing all this

manually, one cell at a
time, will be a royal pain. Can someone please suggest a

solution?

THANKS.
.

  #3   Report Post  
Harlan Grove
 
Posts: n/a
Default

Biff wrote...
Try this:

=INDIRECT(D$1&"!J"&ROW(4:4))

Copy as needed.

....

Since the reference is to a cell in a different column in the same row,
an alternative would be using R1C1-style references.

=INDIRECT("'"&D$1&"'!RC10",0)

  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

Mike

Entered in D4 and dragged down to D50.

=INDIRECT(D$1&"!J" & ROW())


Gord Dibben Excel MVP

On Thu, 17 Mar 2005 12:10:43 -0800, Mike Williams
wrote:

IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4 increment
accordingly, so that in cell D5 the formula looks like this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1 (worksheetname)
reference increments or not, depending on the $, but the cell reference
doesn't increment, no matter what. Doing all this manually, one cell at a
time, will be a royal pain. Can someone please suggest a solution?

THANKS.


  #5   Report Post  
mikewilltoo
 
Posts: n/a
Default

Gord Dibben <gorddibbATshawDOTca wrote in
:

=INDIRECT(D$1&"!J" & ROW())


Thanks, all. A great set of alternatives. All worked; I must say, Gord's
seems the most elegant.
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
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
How to create a formula for a range of cells Marathonrealty Excel Worksheet Functions 3 February 17th 05 04:55 PM
Paste a function as a fixed number Martini25 Excel Discussion (Misc queries) 1 December 21st 04 12:39 PM
How can I dynamically eliminate blank cells in a given range in E. Scott Steele Excel Worksheet Functions 6 December 17th 04 04:23 AM
How do i get an average that ignores blanks in the range of cells. ucastores Excel Worksheet Functions 7 November 11th 04 06:01 PM


All times are GMT +1. The time now is 12:55 PM.

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

About Us

"It's about Microsoft Excel"