| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OFFSET vs INDEX(MATCH(...)) | Excel Discussion (Misc queries) | |||
| using OFFSET(INDEX(MATCH))) | Excel Discussion (Misc queries) | |||
| INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
| Index, Vlookup, Offset | Excel Worksheet Functions | |||
| Want to offset found value using Index | Excel Worksheet Functions | |||
Linear Mode
