index and offset functions

  • Thread starter Thread starter Denise
  • Start date Start date
D

Denise

I am pulling values from another spreadsheet within the same workbook and
need to increment the row munber by 1 when moving the formula down 22 lines.
i.e. =+MON!$D$6 is in I62 and =+MON!D7 will be I84

=+MON!$D$6 =+MON!$E$6 =+TUES!$D$6 =+TUES!$E$6
=+MON!$G$6 =+MON!$H$6 =+TUES!$G$6 =+TUES!$H$6
=+MON!$J$6 =+MON!$K$6 =+TUES!$J$6 =+TUES!$K$6
=+MON!$M$6 =+MON!$N$6 =+TUES!$M$6 =+TUES!$N$6

thanks much!\
Denise
 
i.e. =+MON!$D$6 is in I62 and =+MON!D7 will be I84

If you replace the formula in I62 with this equivalent:
=INDIRECT("Mon!D"&6+INT((ROWS($1:1)-1)/22))
and you then copy I62 and paste on I84,
I84 will return the required: =+MON!D7

But in your instance, it might be easier to use edit > replace to replicate
the incrementation of the entire range of existing formulas (as your post
indicates) all at one go, though. Eg you could select the existing range of
formulas, convert it all to text using edit > replace to replace: = with say
: zzzz. Then copy n paste into the range below (with I84 as the top left
cell), use edit > replace to replace: $6 with $7, then restore the equal
signs, ie edit > replace: zzzz with: = . Then just go back up & restore the
source range in the same manner.
 
THanks Max. I tried the replace command but can't get it to replace the cell
I62, etc. It places the result in whichever cell the replace command is in.

I used =replace(I62,1,1,"Z") and I also tried =replace(I62:J68,1,1,"Z") If
I write the command in cell h62, the resultant "Z" shows up in H62.
Denise
 
Denise,

Think you mis-understood the 2nd part of my response.
I was referring to using the menu command: Edit > Replace
in this para
(with I84 as the top left cell),
Click to expand...

---
 
Hello -
I'm using excel 2007 and can't find what you are talking about. could you
please give a specific example of what to do.
thanks so much
Denise
 
I don't have/know excel 2007, so other responders would need to pitch in
here. Suggest you put in a fresh new posting.

In xl2003, it'd just be selecting the range involved,
then clicking Edit > Replace to surface
the "Find and Replace" dialog for actions as described
 
In Excel 2007:

Home tab>Editing>Find & Select>Replace

Find & Select = binoculars icon
 
Back
Top