What is the proper way to go about simplifying my below formula/worksheet setup?
The purpose of my workbook and setup is for project management, I'm looking to simplify my work processes and Excel calculations to increase the efficiency of my workbook, as well as removing some back-end maintenance on my part in helping maintain it for others.
I have a column in my "Tracker" spreadsheet in the workbook that returns costing information ($ value) from a "Data" spreadsheet. It does so by referencing the listed production location for a product # in the Tracker tab and returning the costing information for that product # at a specific location.
The formula I use for this is just a long chain of IF("production location column/cell" = "specific location",VLOOKUP(product#&specific location,table with the costing, column of the costing information, 0), repeat this IF(VLOOKUP ( chain like 30 times).
I want to try making a table of production locations and the specific location I want to look up costing information on for each location so I can heavily simplify the entire formula on my Tracker tab. Just not sure how to approach it.
If anyone here can help be figure out the bones of this I'd be very grateful, also wouldn't complain if someone helps me fully solve this and can explain the logic. My brain might be fried from the holidays and it's more simple than I'm able to see currently.
Posted Dec 30, 2021, 6:33 PM UTC