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
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.
Last edited by DonkeyOte; 06-27-2009 at 02:28 PM. Reason: added alternative
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks