Changing linked cell row reference

**kta79** · 11-14-2009, 06:58 AM

I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.

I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is:
=INDIRECT("Summary"!C&,X1)

'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.

Hope I have explained this correctly, I have been going round in circles for hours now but can't figure out where i am going wrong

**teylyn** · 11-14-2009, 07:04 AM

You're almost there! Try

=INDIRECT("Summary!C"&X1)

You may also need single quotes around the sheet name if the sheet name includes one or more spaces, like this:

=INDIRECT("'Monthly Summary'!C"&X1)

**DonkeyOte** · 11-14-2009, 07:10 AM

Originally Posted by kta79

I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet.

Further to teylyn's post (in which solution has been provided) I would say that the above implies that the sheet names could be listed in a given column on each of the 21 rows, say in Column A with the row numbers in row 1 ?

At which point, say first sheet is in A2, second in A3 etc and first row to be referenced is in X1, second in Y1 and so on and so forth, then

X2: =INDIRECT("'"&$A2&"'!C"&X$1)

which could be copied across and down for the remaining cell references and sheets.

the use of sheet names in A saves having to update the sheet names in use in each formula on each of the 21 rows.