Link to home
Start Free TrialLog in
Avatar of CanfieldComputers
CanfieldComputers

asked on

Master Formula Sheet Excel

We use excel for to keep track of hours spent at clients.  We have a sheet for each client that are all the same.  Want to make a master sheet that has all the formulas and the client sheets pull the formula from the master so when rates change i don't have to edit all 50 sheets just the one master.  How can i create a formula in the master sheet and link it to the client sheets?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

If you are referring to retrieving rates for various services/tasks or something like that, then VLOOKUP is often handy; my article here reviews the syntax and common pitfalls: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

That said, I strongly suggest you think about going to a database for this.  For example, Access has a time tracking template that probably fits 80% or more of your needs out of the box.  Generally, a database will:

1) do a better job of data management (for example, getting out of the one worksheet per client business)
2) allow much more robust multi-user support
3) support just about any reporting requirements you have
Avatar of CanfieldComputers
CanfieldComputers

ASKER

I would prefer a database as well but i'm not proficent in Access to create/support what it would need to do and no one else in the office is. (Not to mention the end user traning would be a nightmare).  Also don't have enough access licenses to cover all the workstations.

What i need is I have the formula on the master sheet lets say =A2*A3*15.50.  I want to link that formula on Sheet1, Sheet2, ect to be the same thing so if i change the 15.50 to 16.00 all the sheet will get the change.

I don't want to copy and paste to every sheet on the workbook.  I want to make the change once and have it affect the whole workbook.
>>I would prefer a database as well but i'm not proficent in Access to create/support what it would need to do Access has a built-in time tracking template that probably gets you 80% or more of the way there.>>and no one else in the office is. Ah, but that is what EE is for :)>>(Not to mention the end user traning would be a nightmare).  With a forms based interface, as with that template I mentioned above, it shouldn't be any harder than the Excel method was.>>Also don't have enough access licenses to cover all the workstations.If you use Access 2007, you can create free runtime instances for the folks without licenses, substantially reducing the cost.OK, enough shilling for Access, you get the point that I think that (or some other DB) is the best way to go.  If you still really, really want to do this in Excel, I think we're going to need a sample file.Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.Patrick
Here is the example.  Some of the formulas are a little more complex but i kept them simple for just the concept of what i want.  On the "Date Master" sheet I want the formulas in the cells with red text to also be the formulas used on the Master Templates where the red is.  When I change the "Date Master" formulas i have marked in red i need it to affect all the formulas in the Master Template Sheets where its marked in red.


Client-Schedule-Example.xls
Is the first hour *each week* assessed that different rate, or is it the first hour *each day*?
Also, say on Day 1 an employee does a SSN and a HMK, 3 hours each.  Do you assess that first hour rate on both of them?
This deals with home health care, so the formula in each of those boxes will change depending on how its billed.  SSN may have X 1st hour and Y each hour on one workbook.  But on another complete different workbook it may just be flat hour rate because of how its billed (Different State programs dictate the rates, and they change all the time). Some are a flat rate for 1 visit(dosn't matter on how many hours), some have 1st hour is X amount and Y amount for each additional hours, some are diffrent for week days vs weekends.  These billing formulas can change at any time as well.  I can handle making all the formulas I need.  I just don't want to copy and past them to every sheet in a workbook then when it changes i have to copy and past it all over again.  I just want all the sheet to pull the formula from a "master sheet".  Don't care how the master sheet is formated, just want to be able to have a formula like =IF(A3=0,0,(A3-1)*12.5+15) on the "master sheet" (lets say A3 is the total hours for example)  and all the other sheet pull that formula from the "master sheet" since all the other sheets are uniform they can pull the proper amount of hours A3 on there own sheets.  Now if the billing changes to a flat rate for just 13 an hour i would like to be able to change the master to just say =A3*13 and the rest of the sheets would adjust with out me having to copy and past to all of them.  

**We have diffrent workbooks depending billed so each sheet in that workbook will have the same formulas threw out.  So we don't have to worry about one sheet having an exception.
OK, you've now just discussed a crazy-quilt schema of varying rates (and even varying practices in how fees are calculated).  Not at all unexpected, of course, as health care is one of the most complex industries going, but it means you are going to have some real humdingers of formulas.In any event, in your billing sheets, you are NOT, I repeat NOT, going to want to put in any formulas that actually have rates hard-entered in the formula.  Rather, you are going to need one or more lookup tables, and then you should use reference functions to retrieve values from those lookup tables.So, the functions that will become your new best friends are: VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH.  If you need a primer on VLOOKUP, my article here provides an overview of the syntax, as well as a guide to troubleshooting the most common problems:https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html
I not worried about the formulas, i can handle that.  I just don't want to put the formula in 50 sheets then have to change all 50 sheet every other month by having to do a copy and paste over and over and over and over and over.  I want to have the formula in one place and all the sheet get the formula from that.    lets say i have the fromula A3*A4*A5+A6 on the master, i want the all the other sheet to also have the same A3*A4*A5+A6.  Low lets say the formula changes to A4*A5+A6, I want all the other sheets to recieve the updated fromula with out having to go to every sheet and going copy paste, copy paste, copy paste.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thats exactly what i was looking for.  Thank you, even easier than I thought.
I'm a little embarrassed I did not suggest that earlier :)
Its always the simple ones that get us.