Formula to Find and Replace sheet reference in individual sheets

[abellad]

Hi

I have a workbook with 250 individual worksheets (named C1-C250) and a 'Data Summary' sheet. On each of the individual sheets (C1-C250) in need to display data from the 'Data Summary' sheet using a cell reference. So for example on sheet C1 I need to pull data from row 3 of the 'Data Summary Sheet' so on sheet C1 cells

B4='Data Summary'!A3
B6='Data Summary'!D3
B7='Data Summary'!B3
B8='='Data Summary'!C3'

on sheet C2 the reference needs to change to row 4 of the 'Data Summary' sheet. At the moment I am using the Find & Place function on each sheet and changing the 'Data Summary' sheet row references manually. Is there a quicker way of doing this?

Thanks.

 

[BGY23]

This formula will go to sheet1 column B and the row number shown in b3 of the current sheet. By changing the number in B3 you change the row the formula looks at. Without understanding what you're doing I can't give anymore help than that. The sheet and column will not change only the row. You'd have to put formulas in for B C D E etc.

+INDIRECT("Sheet1!B"&$B$3)

 

[abellad]

Thank for your response. I'm not sure I explained myself clearly. I'll try and simplify this:

The workbook contains a sheet called "Data Summary". Column D in this sheet has a list of candidate numbers (e.g. D1_C1). In total there are 250 rows of data in column D. So the "Data Summary" sheet has a row for each candidate. Each row of data has a corresponding sheet in the workbook so candidate D1_C1 has a sheet named D1_C1.


The ask.
On each candidate sheet I need to bring in data from the ""Data Summary" sheet. So on
sheet D1_C1 cell B6 should ='Data Summary'!D2, on
sheet D1_C2 cell B6 should ='Data Summary'!D3, on
etc.

Hope that makes sense.

Thanks

 

[BGY23]

Hi,
With the formual I gave you change the number in B3 to the row you want from the data summary sheet. However now I know you have a unique refence on each row
in the data summary sheet you should use a vlookup. On the summary put the canadidate reference in column A.
lets say Sheet D1_C1 B5 holds the reference D1_C1 for the candidate.
Sheet D1_C1 Formulas
B4 =VLOOKUP($B$5,Data summary!$A$1:$E$250,2,0)
B6 =VLOOKUP($B$5,Data summary!$A$1:$E$250,5,0)
B7 =VLOOKUP($B$5,Data summary!$A$1:$E$250,3,0)
B8 =VLOOKUP($B$5,Data summary!$A$1:$E$250,4,0)