Same Formula via multiple sheets

[jhynes89]

Hi

I have a spreadsheet that i would like to input the same formula but across every worksheet i have.

I have one master spreadsheet which has 38 columns per person and then 38 separate worksheets.

I want to be able to input the value of the same cell from these worksheets in each of the 38 columns without having to amend the formula manually to change the sheet name etc?

Help Appreciated

Jordan

 

[arthurbr]

Hi, if I understood correctly
while holding down the Shift key select the first sheet tab than the last one. All tabs should be highlighted.
Enter a formula in the active sheet it will be copied to the other ones automatically

 

[jhynes89]

hi thanks for your reply

i have a master sheet which has columns week 1 to week 38

i also have then week 1 to week 38 additional sheets

='WEEK 1'!BT14

then week 2 read

='WEEK 2'!BT14

i want to be able to fill the rest of the weeks without having to copy and paste the formula and changing week numbers 1 by one

i tried just dragging the cells so it auto populated but this chagnes the cell reference to the next cell

Help much appreciated

Jordan

 

[Joe4]

This can be done.

i also have then week 1 to week 38 additional sheets

='WEEK 1'!BT14

then week 2 read

='WEEK 2'!BT14

Exactly which two cells are you placing these two formulas in?

Also, is there a column in the same row that indicates the week number for that row?
If so, what exactly does it look like?

 

[jhynes89]

B2 and C2 are where the formulas lie currently

in b1 and c1 there isthe week number 1,2,3,4,5,6 etc

i know it can be done manually, but just trying to save time

Cheers

Jordan

 

[stumac]

Use the indirect function to reference the sheet you want... something like this in the cell B2:

=INDIRECT("'WEEK "&B1&"'!BT14")

You should be able to fill this across.

 

[jhynes89]

Hi

I tried the indirect formula above whcih i was able to fill accross fine but when adding in new scores which adjusted the table i got REF# on the areas that had moved/changed?

Is there a way round this or an alternate method?

Cheers

Jordan

 

[jhynes89]

bump

 

[BarryL]

what are you inputting and where?

 

[jhynes89]

Hi

The sheet where i have inputted the indirect formula above is the master spreadsheet with the league table on it and the sheets it links to are the individual scores for each week we play.

so when i enter there scores and results i want this to auto translate into the master sheet where the league is

Please help

Many thanks

Jordan