K
Kerry Purdy
Hi all,
I am struggling with how to solve a reference table problem and was
wondering whether you have any suggestions on how to help please or even if
it possible.
I have a tall spreadsheet with a starting figure at the top of units sold
(a), then based on the units sold, various calculations and fixed values
underneath we are given an in-house total value (b). Based on the in-house
total, various calculations and fixed values underneath we are then given a
invoice value (c).
In a separate sheet I have a quick reference table for our sales guys with
just 3 columns showing:
(a)=100, (b), (c)
(a)=200, (b), (c)
(a)=300, (b), (c)
......
......
(a)=1000, (b), (c)
I created this manually by entering 100 in the units sold cell in the
original sheet and then copying and pasting the calculated values of (b) &
(c) into my qrt, then back into the original sheet changing the units sold to
200 and again copying and pasting the (b) and (c) values into my qrt.
My problem is that in the original sheet the fixed values that are being
used in formulas to calculate the (b) and (c) values are constantly changing
so therefore I have to keep manually updating my quick reference table. It
is very time consuming.
Is there anyway that I can improve this process? I thought about using data
tables but wondered as i have lots of intermediate calcs to arrive at (b) and
(c) a data table would not work.
I would be very grateful for some advice please.
Thank you very much for your time.
I am struggling with how to solve a reference table problem and was
wondering whether you have any suggestions on how to help please or even if
it possible.
I have a tall spreadsheet with a starting figure at the top of units sold
(a), then based on the units sold, various calculations and fixed values
underneath we are given an in-house total value (b). Based on the in-house
total, various calculations and fixed values underneath we are then given a
invoice value (c).
In a separate sheet I have a quick reference table for our sales guys with
just 3 columns showing:
(a)=100, (b), (c)
(a)=200, (b), (c)
(a)=300, (b), (c)
......
......
(a)=1000, (b), (c)
I created this manually by entering 100 in the units sold cell in the
original sheet and then copying and pasting the calculated values of (b) &
(c) into my qrt, then back into the original sheet changing the units sold to
200 and again copying and pasting the (b) and (c) values into my qrt.
My problem is that in the original sheet the fixed values that are being
used in formulas to calculate the (b) and (c) values are constantly changing
so therefore I have to keep manually updating my quick reference table. It
is very time consuming.
Is there anyway that I can improve this process? I thought about using data
tables but wondered as i have lots of intermediate calcs to arrive at (b) and
(c) a data table would not work.
I would be very grateful for some advice please.
Thank you very much for your time.