Help with Excel +Sheet1!XY function please

Closed
hjdx
Posts
2
Registration date
Thursday May 18, 2017
Status
Member
Last seen
May 18, 2017
- May 18, 2017 at 07:50 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- May 18, 2017 at 11:31 AM
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

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
May 18, 2017 at 11:31 AM
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
0