copying 'sheet1'!A1 to sheet 2, I want to get 'sheet2'A1, not 'she

[Guest]

I want the sheet number to change relatively when I copy a formula in sheet 2
(referencing to say sheet1) to sheet 3. I've done this before but can't seem
to get it to work now. help!!!

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

 

[Guest]

we're on the right track, is there one for "next sheet"?

I got the sheet references to change in the past by entering the right
formulas in say 3 consecutive sheets and then grouping the sheets and then
copying the cells to 3 new consecutive sheets, the sheet references changed
correctly, but I can't seem to get it to do it again, have you tried this
method?

 

[Guest]

I just looked at what a UDF is, I get it, but I need a simpler solution.

Here's what I tried this morning, it didn't work but the other day I tried
it and it did work (maybe I did things a little differently today, but I know
there is a way to get the sheet references to change using group mode cause I
did it.

Please give it a try if you have the patience and let me know if you got it
to work.

open up 6 sheets

enter in A1 of the first 3 consecutive sheets
sheet 1 100
sheet 2 'sheet1'!A1
sheet 3 'sheet 2'A2

select and copy from sheet 2 A1 to sheet 3 A1
Group sheets 4 to 5
Click paste or press enter

Result, the sheet references just copied, the didn't move relatively, ugh.

 

[JE McGimpsey]

Result, the sheet references just copied, the didn't move relatively, ugh.

Sorry, I've never seen that happen.

 

[Guest]

ok I just got it to work but it only works for inserting/copying whole new
sheets, not for copying ranges. boo hoo. thanks for trying.