# Same Formula via multiple sheets

#### jhynes89

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

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

='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

This can be done.
i also have then week 1 to week 38 additional sheets

='WEEK 1'!BT14

='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?

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

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

Code:
``=INDIRECT("'WEEK "&B1&"'!BT14")``

You should be able to fill this across.

Last edited:
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

bump

what are you inputting and where?

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

Many thanks

Jordan

Replies
4
Views
450
Replies
3
Views
230
Replies
1
Views
347
Replies
3
Views
631
Replies
14
Views
877

1,196,057
Messages
6,013,164
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back