index and offset functions
 

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

index and offset functions
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Denise" wrote:
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

index and offset functions
 

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

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Denise" wrote:
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

index and offset functions
 

Denise,

Think you mis-understood the 2nd part of my response.
I was referring to using the menu command: Edit Replace
in this para

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: =
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

index and offset functions
 

Hi Max -
I got it!! YIPPEE! thanks again so much for your help.
Denise

"Max" wrote:

Denise,

Think you mis-understood the 2nd part of my response.
I was referring to using the menu command: Edit Replace
in this para

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: =
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

index and offset functions
 

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

"Max" wrote:

Denise,

Think you mis-understood the 2nd part of my response.
I was referring to using the menu command: Edit Replace
in this para

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: =
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

index and offset functions
 

Glad you got that up.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Denise" wrote in message
...
Hi Max -
I got it!! YIPPEE! thanks again so much for your help.
Denise

index and offset functions
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Denise" wrote in message
...
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

index and offset functions
 

In Excel 2007:

Home tabEditingFind & SelectReplace

Find & Select = binoculars icon

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Denise" wrote in message
...
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