Forum Discussion
Formula to automatically take from individual equipment and reflect in Summary Tab
Hello,
I'm trying to see if there is a possibility excel can automatically update a formula if certain criteria is met. The objective here is that if there is anew repair for one of our equipment, as soon as the data is filled in one of our equipment tabs, (245D 325BL, etc.) it will automatically display in our summary tab.
Below is the spreadsheet I am using. Thanks in advance.
I'm trying to see if there is a possibility excel can automatically update a formula if certain criteria is met.
Do you really mean that? Do you really want a formula updated? I don't know that I've ever seen that request. Using INDIRECT it is indeed possible to modify (a form of updating) formulas. But if that is possible, it doesn't really fit with the rest of what you've written.
The objective here is that if there is anew repair for one of our equipment, as soon as the data is filled in one of our equipment tabs, (245D 325BL, etc.) it will automatically display in our summary tab.
So what you really appear to want is to make sure that the summary sheet is up-to-date with any and all repairs done on individual pieces of equipment. Whether or not a formula is updated or changed in any way is beside the point; your actual objective is the creation of a summary that accurately reflects all repairs on each piece of equipment.
To do that in the "cleanest way," I would suggest that you need to be willing to consider modifying the way you collect the raw data on each individual machine, as well as how you display the summary (more inclusive). In particular, I wonder if you would be willing to
- record all of the individual repairs to each of the individual machines in a single data base (rather than each on their own sheet);
- have a summary sheet that displayed all repairs for all machines sorted either by date of repair OR by machine (doing so without all the blank space in the current design, blank space because you need to allow for multiple rows that will eventually, presumably, get filled but now are just wasted space)
- have yet another sheet where the repairs for any given machine could be displayed "on demand"
The fundamental design you have is far more labor intensive than it needs to be--which is why (I would submit) you're needing to ask if the formula could be updated based on criteria. A different design altogether would enable Excel to do the work with just a few formulas that don't need revision based on criteria, that can select the summary data to display based on criteria, but with static formulas.
Are you open to that kind of re-design? To re-thinking how you've approached the whole task?
