Is there a way to evaluate a series of formulas for a table of values without combining them into a single megaformula?
It's easier to illustrate with an example. Say Table1 looks like this:
A B C D E F
1 PRODUCT COST MARKUP SECRET FORMULA PRICE PROFIT
2 burger 4.00 50% =22*12345 =B2*(1+C2)*D2 =E2-B2
3 fries 3.00 50% =22*12345 =B3*(1+C3)*D3 =E3-B3
4 soda 1.50 50% =22*12345 =B4*(1+C4)*D4 =E4-B4
Now I want to build Table2 to return profit for a range of markup rates for each product, like this:
A B C D E F
1 MARKUP 10% 20% 30% 40% 50%
2 burger
3 fries
4 soda
In Table2!B2, I want to want to display the value of cell Table1!$C2 when the value of cell Table1!$C2 is set to the column heading value from Table2!B$1. In other words, I want the profit for a burger when the markup is 10%. By then copying across rows and cells, I can observe the effect on profit for each item when I vary my markup from 10% to 50%.
I know I could do this manually by combining the formulas from Table 1, cols D and E into a single megaformula for Table 2. For instance, cell Table2B1 would have this formula:
= Table1!B2*(1 + B$1)*22*12345
But let's say that the SECRET FORMULA is actually a very complex series of calculations over several columns with dozens of dependencies. My actual set of formulas spans about 20 columns, and generates an 8 line formula ...impossible to edit.
So having built the series of dependent formulas in Table 1, it would be great to use those formulas to generate Table 2, without having to replicate them all in Table 2.
