Forum Discussion
Adjusting Premade Template - Textboxes to calculate table info
Kia ora,
I have downloaded a template to calculate meal costs and am in the process of editing it to suit my needs however I'm struggling to figure out how to do what I want (see image). The first is the original template which shows what it originally calculates. The second image is how I've altered the template and what I'm wanting to change how/what these calculate but not sure how. Below is what I'm wanting each box to do:
First top one = Calculate the total amount in the meal cost column
The following five boxes = Calculate the total unit cost column based on each of the following categories: produce, meat, chilled, frozens, and pantry. It does seem to be linked somehow it's just calculating the wrong numbers and I don't know how to fix it
The last one = I want this to calculate the total amount in the unit price column
Finally, I'd like them all to show to the two decimal amount to include the cents. Hopefully this makes sense, I have no idea what I'm doing and really want to suss it out. Thank you for your time!
AnnabelleMay I'm sure mathetes won't mind if I add some comments. You can customise this template, though it will require quite a bit of manual "labour". And depending on your skills (you mention to be a newbie) that might be a challenge.
The template is driven by calculations on a hidden sheet called "calculations". When ever you type a Category in the shopping list it will include in a list that will be used to sum the totals by category. But, beware, if you miss-spell a category name you'll end up with two separate ones. Not very smart, I would say. Leaving the Category empty will place the product in "Other". Then it goes through several steps to rank all categories by amount and sort them in descending order, but these formulae contain hard-coded numbers that you need to change yourself if you have added a category.
The fancy shapes in the list on the right-hand side are linked, again by hard-coded references, to the ranked list of amounts per category in the calculations sheet. And the shapes intend to be displayed in ascending order. If the number of categories changes you'll have to be very careful that you add shapes in the correct place with the correct reference to the calculation sheet for both the category name and the amount. Increasing the number of decimals, by the way is done by changing the formulae in column L of the calculation sheet to round to 2 decimals.
Confused? I'm not surprised. This template works just fine, although it's not very well designed, if you stick to its initial set-up. Use no more that 4 named categories, group everything else in Other by leaving the category blank. This will then fill the 6 shapes correctly (4 named, 1 other and 1 for the total) without having to change the formulae.
I attached the template that has one extra category "Chilled". I just changed the one on row 7. Also adjusted the amounts in the shapes to display two decimals.
As a final recommendation I would suggest you learn about Data Validation and Pivot Tables and ditch this template. True, the end result may not be as colourful as the template, but it will be a lot easier to maintain. Added an example of a simple pivot table in the file.
5 Replies
What is the template? Where did you get it? Can you provide a link to the original? Images don't tell us what is in the formulas or how the whole thing is structured. Unfortunately, a lot of templates are not amenable to user changes. But if you can tell us where this is from, ideally giving a link to the original, we may be able to look into it.
- Opps sorry! Totally spaced on that one. The link for it is https://officetemplatesonline.com/download/download-detailed-shopping-list-with-sorting-filter/
AnnabelleMay I'm sure mathetes won't mind if I add some comments. You can customise this template, though it will require quite a bit of manual "labour". And depending on your skills (you mention to be a newbie) that might be a challenge.
The template is driven by calculations on a hidden sheet called "calculations". When ever you type a Category in the shopping list it will include in a list that will be used to sum the totals by category. But, beware, if you miss-spell a category name you'll end up with two separate ones. Not very smart, I would say. Leaving the Category empty will place the product in "Other". Then it goes through several steps to rank all categories by amount and sort them in descending order, but these formulae contain hard-coded numbers that you need to change yourself if you have added a category.
The fancy shapes in the list on the right-hand side are linked, again by hard-coded references, to the ranked list of amounts per category in the calculations sheet. And the shapes intend to be displayed in ascending order. If the number of categories changes you'll have to be very careful that you add shapes in the correct place with the correct reference to the calculation sheet for both the category name and the amount. Increasing the number of decimals, by the way is done by changing the formulae in column L of the calculation sheet to round to 2 decimals.
Confused? I'm not surprised. This template works just fine, although it's not very well designed, if you stick to its initial set-up. Use no more that 4 named categories, group everything else in Other by leaving the category blank. This will then fill the 6 shapes correctly (4 named, 1 other and 1 for the total) without having to change the formulae.
I attached the template that has one extra category "Chilled". I just changed the one on row 7. Also adjusted the amounts in the shapes to display two decimals.
As a final recommendation I would suggest you learn about Data Validation and Pivot Tables and ditch this template. True, the end result may not be as colourful as the template, but it will be a lot easier to maintain. Added an example of a simple pivot table in the file.
