Formula's in linking spreadsheets
I am doing a spreadsheet that has twenty tabs and one master (where I collect
all the data from the info entered into each tab). I need to copy the first formula on the master down the column to reflect the data in each tab. I have set up the formula that keeps the cell constant, but can't get the tab number to change when copied. i.e., Tab1, c12; Tab 2, C12, Tab 3, C12, etc. Every time I copy the link down the column, tab 1 stays as tab 1. What to do? -- JDF |
Formula's in linking spreadsheets
You can build up a text string any way you want and then use INDIRECT to convert it to an actual range reference. For example, if your worksheets are named Sheet1, Sheet2 and so on, enter the following in, say, G5 and copy down as far as you need to go. =INDIRECT("Sheet"&ROW()-ROW($G$5)+1&"!A1") Change the $G$5 to the first cell in which this formula appears, and change the A1 to the cell on each worksheet whose value you want to retrieve. If your sheets are not so logically named, first create a list of your worksheets starting in, say, H10. and enter the following formula in the first cell where the returned list is to begin and copy down as far as you need to go. Watch carefully the combinations of single quotes and double quotes. =INDIRECT("'"&H10&"'!A1") Both formulas work by using the & concatenate operator with cell references to build a text string that is a syntactically correct cell address. INDIRECT then takes that text string and converts it to an actual range reference and returns the value of that range. In general, you can use an INDIRECT function anywhere you would normally use a cell address. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 14 Mar 2010 13:02:01 -0700, Janie wrote: I am doing a spreadsheet that has twenty tabs and one master (where I collect all the data from the info entered into each tab). I need to copy the first formula on the master down the column to reflect the data in each tab. I have set up the formula that keeps the cell constant, but can't get the tab number to change when copied. i.e., Tab1, c12; Tab 2, C12, Tab 3, C12, etc. Every time I copy the link down the column, tab 1 stays as tab 1. What to do? |
All times are GMT +1. The time now is 12:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com