Master reference table, suggestions

[emilpull]

Hi,

I'm working on an excel book to simulate outcomes from a game. I have 6 different sheets covering various aspects of the game, and the formulas in these 6 sheets all draw from a set of common constants. These constants I need to change every month (as the game change). Right now I just keep these constants in each of sheets, and every month I change the values and copy paste them over to each of the individual sheets. I would however like to create a master table of sorts. So that when I change the values in one, all changes.

Best of all would be to have the constants in a separate sheet and make references to that one in the formula - but the problem with that is that the formulas themselves are so long and that sheet references would make it hard to get an overview when I'm playing around with the formulas. As an example, this is how a formula look right now:

=((C30*C24*LOOKUP(C4;Y6:Y15;AD6:AD15)+(C31*LOOKUP(C4;Y6:Y15;Z6:Z15))+(C29*C25*(LOOKUP(C4;Y6:Y15;AI6:AI15)+IF(C49=TRUE;1;0)))+(((O31*(AU35+(IF(C4=Y9;2;0))))/C7)+((C26*(AU36+(IF(C4=Y9;2;0))))/C7)+(((O29*AU34)+IF(C4=Y9;1;0))/C7))*IF(C13=BB29;LOOKUP(C4;AJ6:AJ15;BB6:BB15);1))*G40)

Adding sheet references there would make it even longer and harder to get an overview of...

Any suggestions/ideas?

 

[crazydragon84]

have one master table sheet, then have a copy of that table in each sheet that references to the master sheet (e.g. each sheet will have the same exact copy of master table but it just references to master sheet).

 

[emilpull]

have one master table sheet, then have a copy of that table in each sheet that references to the master sheet (e.g. each sheet will have the same exact copy of master table but it just references to master sheet).

That's actually not a bad idea. One problem would be that sometimes I have to add new constants. So the tables grow...

 

[crazydragon84]

well, there are few ways to go about:

1. Forget the master tab. you can have each sheet have master table in the same rows and columns of each tabs. When you update the table, just group the sheet so you are updating same rows/columns at the same time for all the group sheets.
2. Use macro - reflect all changes to master table to sub tables.
3. Maybe the ugly workaround approach, but make the master table either a dynamic named range or a table. Then, the sub master tables on each tab will actually be pivot tables with underlying data referencing to the table or the named range. Once you add your new parameter, you just need to manually referesh one of the pivot tables and all of them will update.

 

[Eric W]

I'd put all your constants in a separate sheet, and used Named Ranges. For example, say you have 3 constants in A1, A2, and A3 on your new Constants tab. A1 is 100 and stands for "Points per Game", A2 is 5 and stands for "Games per round", and A3 is 60 and stands for "seconds per game". Select A1, and in the Address box (left of the formula bar, it should say A1), type over the A1 with PtsPerGame.

Now on another sheet you can have a formula like:

=IF(D44>=Constants!A1,"You win!","Game not over")

but if you use the Name, it would be:

=IF(D44>=PtsPerGame,"You win!","Game not over")

Shorter, and easier to read. Easy enough to add new constants too.

 

[crazydragon84]

don't know why i didn't think about that lol i do that with all my templates too haha.