TOP Urgent
 

Please help me doing the below:

I have 2003 xls file with more than 200 worksheets..
Last worksheet contains the index..
In the index we have summary of the data available in the other worksheets..

The data was established in the index as follows:
='1'!C16, when linking data from worksheet named 1
='2'!C16, when linking data from worksheet named 2
='3'!C16, when linking data from worksheet named 3
and so on....

I need to estalish new column in the index worksheet & data will come from D30 of whole worksheets by using the auto fill down..

How I should write the formula to be able to get the data from D30 in each worksheet when using the auto fill down in the index worksheet??

A very quick and dirty trick is to copy your cells that already reference C16 in all of the tabs and paste it across one column and down 14 rows. The formulas will automatically become D30 formulas (i.e. C+1 column across=D and 16+14 rows down=30).

You can then highlight these new D30 formulas and move them (by clicking on the black frame and dragging the whole range) where you want them and the formulas will remain as D30s.