Fill series including worksheets

[jamespeters01]

Hi,

I have a excel speadsheet, which has 14 worksheets (named wk1 - wk13 and 1 named MAST).
In sheets wk1 - wk13 there are certain cells which are filled in by the user with numerical figures. These cells are constant through each worksheet. i.e wk1!b9, wk2!b9, wk3!b9 etc. etc.

In my MASTER sheet I am trying to implement a fill series in which it automatically fills in the results in the about cells. So, in cell A1 in my Master sheet it reads =wk1!b9, Cell A2 reads =wk2!b9 and so on.

Now, I have about 70 records in each worksheet, and I want to, instead of doing the MASTER sheet workings by hand, I want to be able to fill the series (hence saving a heck of a lot of time!). When I fill the series the next cells increment as follows =wk1!b9, =wk1!c9, =wk1!d9, which is obviously backwards of the way I need the cells incrementd ! I have tried using $ in front of the cell value i.e =wk1!$b$9 and filled the series, but this locks the whole formula and fills all the cells with the identical info. from the 1st cell.


Any ideas ??

 

[xlbo]

Better spreadsheet design would probably help but failing that, set up a list eg A1:A13 with b thru to n or whatever cols you are referencing.

then in B1 enter =indirect("wk1!" & A1 & 9)

where A1 = b - this will give you the value of B9 on wk1. You can then fill this down and A1 will incrememnt to A2, A3 etc, picking up c, d etc as it goes

If this isn't clear I can email you an example

HTH
Geoff

 

[jamespeters01]

Thanks for the help.

If you could e-mail me an example that would be great. james@petrushkin.co.uk

Its not the A1, A2, A3 etc. that I want to increment though, its wk1, wk2, wk3 etc

 

[xlbo]

aaaaah - light dawns - you want to increment weeks downwards

in that case, insert a column so you have a new Col A. In A1, enter wk1 and fill down until you get to wk13
In B1 enter:
=INDIRECT($A1&"!B9&quot
This will reference sheets wk1, cell B9

If you fill this down, the next cell (B2) will reference sheets wk2, cell B9 etc etc
sending wb

HTH
Geoff