incrementing worksheet numbers on a master sheet

karen1245 · #1 06-27-2009, 01:16 PM

Here is what I have: an Excel workbook where worksheet 1 is named Master and all worksheets after are simply 1,2,3... On the master, I want to reference for example cell C1 for each worksheet. I have it set up so that C1 is the absolute value and it links, however I don't know how to make the sheet numbers increment when I try to use the fill box. If I drag down, it simply copies the formula and then I have to go row by row and do a replace so that the correct worksheet shows in the formula. Is there any way that I can make this work without having to do the find/replace?

thanks,

Karen

DonkeyOte · #2 06-27-2009, 01:24 PM

Assuming

MASTER!A1 to link to 1!A1, MASTER!A2 to link to 2!A1 etc...

MASTER
A1: =INDIRECT("'"&ROWS(A$1:A1)&"'!A1")
copied down

INDIRECT is Volatile though... if this is a one off task you could enter

A1: ="#'"&ROWS(A$1:A1)&"'!A1"
copy down

Once complete, highlight column A, copy & paste special the results as values only (ie getting rid of the formulae), then with the column still highligted run an EDIT/REPLACE replaing # with = ... at which point all become non-volatile links.

Welcome to the Excel Forum

Forum Statistics