Cross referencing based on Sheet Name

[Simon Pipe]

Hi,

I attach an example of what I am trying to do and stripped all the unnecessary stuff so I appreciate may look a bit odd but.....

Sheet 'Dave'
D3 should read from Sheet 'Rd1', and fill in relevant info from Column B for Dave's selection.
E6 to E13 should read from sheet 'Rd1', and as above and read from Daves row to fill in the cells.

There are many other sheets that are just replicates of 'Dave' but need to pull in the data relevant to their names in the corresponding cells as above.

There can be up to 100 sheets so don't want to copy and paste it all and want the workbook automatically reading from 'Rd1' which is a data from a form.

I do have D2 cells automatically named based on the sheet name but have removed it for this example.

Hope this makes sense and any help gratefully received!
Thanks!

M365

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Simon Pipe]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

M365 - I did put on the post but will update profile. Thanks

 

[Fluff]

Thanks for that.
How about in D3

=XLOOKUP(D2,'Rd1'!A2:A20,'Rd1'!B2:B20)

and in E6

=TOCOL(XLOOKUP(D2,'Rd1'!A2:A20,'Rd1'!C2:H20))

 

[Simon Pipe]

Excellent stuff! Thank you so much - works a treat!

 

[Fluff]

You're welcome & thanks for the feedback.