Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automating use of complicated formula
I dont know if there is an Excel or Access solution to my problem.
The issue is that I have a list of 80 clients I need to calculate the costs for; each client can have 4 programs theyre involved in. To get the cost, I need to use a very complicated formula that takes input # (# people) and multiplies it by different costs over several steps; I have a great template set up to calculate the cost. The problem is that the use of the template is manual €“ I need to enter the clients #s for the 4 programs into the template, then paste the $ values back into the client spreadsheet. In addition, I now need to do multiple scenarios for each client, so thats 80 sets of program input #s times 3 or 4 scenarios. So Im trying to see if theres a way to automate this process. What _cant_ be done is to put list of clients & their input # (# people/program) on one tab, and formula on another, and drag down the formula, then link input and output cells in each tab. This cant be done because the formula is much too complicated. The only solution I can think of now is to create 81 tabs: 1 master vendor tab, and then 1 template (formula calculation) tab for each vendor. Then link cells in master vendor tab to the input/output cells in that vendors tab. I would need to put 3 €“ 4 templates on each child tab, to allow for multiple scenarios. Pros: its doable. Cons: its laborious to set up, but can be done; it doesnt allow flexibility€”Im locked into using Column E for first scenario, Column M for second scenario, etc. (I mean, I can add columns, so M becomes N€”and N will be the one linked to template; but I cant move scenarios around easily on the page€”cant risk losing the link; if I decide I want to make Scenario 3 into Scenario 2, I can certainly move those columns€”but then its illogical that the second scenario is connected to the third template€”that might screw things up later if I dont remember that fact and try to €śfix€ť things; so its just big and unwieldy to use); it makes a big file Is there a smarter way to do this? €“using either Excel or my throwing together a simple database in Access. I feel like theres a brilliant solution Im just unaware of€”that I hope one of you has come across in the past! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automating use of complicated formula
Look at the DataTable command.
I do have a description of how it can be used for batch-processing of complex (multi-sheet) calculations with many inputs and outputs. If you give me your e-mail address I'll mail you the recipe. -- Kind regards, Niek Otten Microsoft MVP - Excel "Ellens80" wrote in message ... I dont know if there is an Excel or Access solution to my problem. The issue is that I have a list of 80 clients I need to calculate the costs for; each client can have 4 programs theyre involved in. To get the cost, I need to use a very complicated formula that takes input # (# people) and multiplies it by different costs over several steps; I have a great template set up to calculate the cost. The problem is that the use of the template is manual €“ I need to enter the clients #s for the 4 programs into the template, then paste the $ values back into the client spreadsheet. In addition, I now need to do multiple scenarios for each client, so thats 80 sets of program input #s times 3 or 4 scenarios. So Im trying to see if theres a way to automate this process. What _cant_ be done is to put list of clients & their input # (# people/program) on one tab, and formula on another, and drag down the formula, then link input and output cells in each tab. This cant be done because the formula is much too complicated. The only solution I can think of now is to create 81 tabs: 1 master vendor tab, and then 1 template (formula calculation) tab for each vendor. Then link cells in master vendor tab to the input/output cells in that vendors tab. I would need to put 3 €“ 4 templates on each child tab, to allow for multiple scenarios. Pros: its doable. Cons: its laborious to set up, but can be done; it doesnt allow flexibility€”Im locked into using Column E for first scenario, Column M for second scenario, etc. (I mean, I can add columns, so M becomes N€”and N will be the one linked to template; but I cant move scenarios around easily on the page€”cant risk losing the link; if I decide I want to make Scenario 3 into Scenario 2, I can certainly move those columns€”but then its illogical that the second scenario is connected to the third template€”that might screw things up later if I dont remember that fact and try to €śfix€ť things; so its just big and unwieldy to use); it makes a big file Is there a smarter way to do this? €“using either Excel or my throwing together a simple database in Access. I feel like theres a brilliant solution Im just unaware of€”that I hope one of you has come across in the past! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automating use of complicated formula
Hi Niek,
Exploring the questions looking for ideas to make my file easier to process and see that Ellens may be similiar to mine - at least with size and complexity. Except mine is too big to use in Access or I'm too inexperienced. I'm moving past column HW with this report and so far 25+ columns are formula based with a few list box columns (over 400 rows. Ugh! ) In many cases, one column is based on the answer in another. I'm still trying to work through various scenarios to set up conditional formatting, if then statements, alerts to time sensitive changes, etc to cover all the possiblities. I'm not sure what batch processing is, other than using it in one of our vendor program systems to pull reports. If you wouldn't mind sharing I would be grateful. Kathleen "Niek Otten" wrote: Look at the DataTable command. I do have a description of how it can be used for batch-processing of complex (multi-sheet) calculations with many inputs and outputs. If you give me your e-mail address I'll mail you the recipe. -- Kind regards, Niek Otten Microsoft MVP - Excel "Ellens80" wrote in message ... I dont know if there is an Excel or Access solution to my problem. The issue is that I have a list of 80 clients I need to calculate the costs for; each client can have 4 programs theyre involved in. To get the cost, I need to use a very complicated formula that takes input # (# people) and multiplies it by different costs over several steps; I have a great template set up to calculate the cost. The problem is that the use of the template is manual €“ I need to enter the clients #s for the 4 programs into the template, then paste the $ values back into the client spreadsheet. In addition, I now need to do multiple scenarios for each client, so thats 80 sets of program input #s times 3 or 4 scenarios. So Im trying to see if theres a way to automate this process. What _cant_ be done is to put list of clients & their input # (# people/program) on one tab, and formula on another, and drag down the formula, then link input and output cells in each tab. This cant be done because the formula is much too complicated. The only solution I can think of now is to create 81 tabs: 1 master vendor tab, and then 1 template (formula calculation) tab for each vendor. Then link cells in master vendor tab to the input/output cells in that vendors tab. I would need to put 3 €“ 4 templates on each child tab, to allow for multiple scenarios. Pros: its doable. Cons: its laborious to set up, but can be done; it doesnt allow flexibility€”Im locked into using Column E for first scenario, Column M for second scenario, etc. (I mean, I can add columns, so M becomes N€”and N will be the one linked to template; but I cant move scenarios around easily on the page€”cant risk losing the link; if I decide I want to make Scenario 3 into Scenario 2, I can certainly move those columns€”but then its illogical that the second scenario is connected to the third template€”that might screw things up later if I dont remember that fact and try to €śfix€ť things; so its just big and unwieldy to use); it makes a big file Is there a smarter way to do this? €“using either Excel or my throwing together a simple database in Access. I feel like theres a brilliant solution Im just unaware of€”that I hope one of you has come across in the past! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automating use of complicated formula
Send me your email address
-- Kind regards, Niek Otten Microsoft MVP - Excel "Kathleen" wrote in message ... Hi Niek, Exploring the questions looking for ideas to make my file easier to process and see that Ellens may be similiar to mine - at least with size and complexity. Except mine is too big to use in Access or I'm too inexperienced. I'm moving past column HW with this report and so far 25+ columns are formula based with a few list box columns (over 400 rows. Ugh! ) In many cases, one column is based on the answer in another. I'm still trying to work through various scenarios to set up conditional formatting, if then statements, alerts to time sensitive changes, etc to cover all the possiblities. I'm not sure what batch processing is, other than using it in one of our vendor program systems to pull reports. If you wouldn't mind sharing I would be grateful. Kathleen "Niek Otten" wrote: Look at the DataTable command. I do have a description of how it can be used for batch-processing of complex (multi-sheet) calculations with many inputs and outputs. If you give me your e-mail address I'll mail you the recipe. -- Kind regards, Niek Otten Microsoft MVP - Excel "Ellens80" wrote in message ... I dont know if there is an Excel or Access solution to my problem. The issue is that I have a list of 80 clients I need to calculate the costs for; each client can have 4 programs theyre involved in. To get the cost, I need to use a very complicated formula that takes input # (# people) and multiplies it by different costs over several steps; I have a great template set up to calculate the cost. The problem is that the use of the template is manual €“ I need to enter the clients #s for the 4 programs into the template, then paste the $ values back into the client spreadsheet. In addition, I now need to do multiple scenarios for each client, so thats 80 sets of program input #s times 3 or 4 scenarios. So Im trying to see if theres a way to automate this process. What _cant_ be done is to put list of clients & their input # (# people/program) on one tab, and formula on another, and drag down the formula, then link input and output cells in each tab. This cant be done because the formula is much too complicated. The only solution I can think of now is to create 81 tabs: 1 master vendor tab, and then 1 template (formula calculation) tab for each vendor. Then link cells in master vendor tab to the input/output cells in that vendors tab. I would need to put 3 €“ 4 templates on each child tab, to allow for multiple scenarios. Pros: its doable. Cons: its laborious to set up, but can be done; it doesnt allow flexibility€”Im locked into using Column E for first scenario, Column M for second scenario, etc. (I mean, I can add columns, so M becomes N€”and N will be the one linked to template; but I cant move scenarios around easily on the page€”cant risk losing the link; if I decide I want to make Scenario 3 into Scenario 2, I can certainly move those columns€”but then its illogical that the second scenario is connected to the third template€”that might screw things up later if I dont remember that fact and try to €śfix€ť things; so its just big and unwieldy to use); it makes a big file Is there a smarter way to do this? €“using either Excel or my throwing together a simple database in Access. I feel like theres a brilliant solution Im just unaware of€”that I hope one of you has come across in the past! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automating use of complicated formula
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up a complicated IF formula | Excel Discussion (Misc queries) | |||
help with complicated formula | Excel Worksheet Functions | |||
complicated formula | Excel Worksheet Functions | |||
Please Help with complicated SUM formula | Excel Worksheet Functions | |||
Complicated formula? | Excel Discussion (Misc queries) |