Help with Excel +Sheet1!XY function please

[Closed]
Report
Posts
2
Registration date
Thursday May 18, 2017
Status
Member
Last seen
May 18, 2017
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I'm using Excel to monitor some data from my class, but I'm a fairly novice user.

I've been using +Sheet1!XY to copy data from one sheet to another. I found it on this site and it's been a life saver and I just followed the instructions and it worked, but now I need to use it again but I'm stuck, can anyone help?

I have Sheet1 with names in column B from row 8 down, and then information going from column C to AZ. I need each named person to have their own sheet as well. So I used +Sheet1!C8 etc and mapped that all out on Sheet2. But on the next sheet I need to to copy the data from row 9, and so on. How do I easily do this change in the +Sheet1!XY formula without having to manually edit the row number in each cell on each sheet?

I wondered whether using Find and Replace for the row number might work?

Thanks for any help anyone can offer!



1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Hjdx,

Find and Replace will work, but only after you display the formula in the cells. Do this by hitting Ctrl+t, then Ctrl+h for Find and Replace and finally Ctrl+t to change the formula's back to showing results.

Best regards,
Trowa