Forum Discussion
Monthly Budget VALUE! errors
As one with just enough Excel knowledge to be dangerous, I'm using the handy Monthly Budget template to get my finances under control. I fear I've added rows and botched formulas, as the handy aggregator screen designed to show my "Top 5 expense categories" now is a sea of VALUE! errors.
Would anyone please care to coach me to competence?
Thanks,
CliffordJ
CliffordJ Not sure what you did, but I suspect it was not only inserting some rows. When I tried opening it I received a warning that it had links to "External sources". Obviously, I could not update these.
The template has several hidden columns. In one of them, I found a reference to a table called "Housing1438" in a workbook called "Monthly Personal Budget C and M 2020.xlsx". Removed all of that and replaced it with a formula similar to what I found in another hidden column in another sheet which serves the same purpose (i.e. to enable ranking the Top5 expenses).
The summary table contained references to tables in a workbook "Budget for and with formulas.xlsx", causing #REF! errors. Removed all that so that the formulae point to tables in the current workbook.
Now it seems to work! Please see attached.
You ask to be coached to competence. Well, start by learning about the functions used in this template (within Excel's help screens and/or on-line). Learn about structured table references. The template uses them a lot. A nice feature, but not always easy to follow if you are not used to them. Unhide hidden columns and see what they are hiding from you. Good luck!
2 Replies
CliffordJ Not sure what you did, but I suspect it was not only inserting some rows. When I tried opening it I received a warning that it had links to "External sources". Obviously, I could not update these.
The template has several hidden columns. In one of them, I found a reference to a table called "Housing1438" in a workbook called "Monthly Personal Budget C and M 2020.xlsx". Removed all of that and replaced it with a formula similar to what I found in another hidden column in another sheet which serves the same purpose (i.e. to enable ranking the Top5 expenses).
The summary table contained references to tables in a workbook "Budget for and with formulas.xlsx", causing #REF! errors. Removed all that so that the formulae point to tables in the current workbook.
Now it seems to work! Please see attached.
You ask to be coached to competence. Well, start by learning about the functions used in this template (within Excel's help screens and/or on-line). Learn about structured table references. The template uses them a lot. A nice feature, but not always easy to follow if you are not used to them. Unhide hidden columns and see what they are hiding from you. Good luck!
Dear Riny van Eekelen,
Thank you so much for taking the time and making the effort to figure out where my problems lurked in my budget template. I will be certain to follow your coaching and learn more about structured table references, and certainly appreciate your generosity in offering that tip. This is my first go-round in making use of the global Excel support community, and you have made it a very positive experience. Thank you again, sir, and my regards to the good folks of Örnsköldsvik, Sweden.
Sincerely,
Cliff
Dubois, Wyoming
