Hi there,
After presenting the most rediculously detailed financial model of a factory to my boss, he complained that I used arrays and If functions where I should have used the LOOK Up Function.
So now I need to change the function that a cell uses, the problem is I dont know how.
I've looked at various lookup tutorials, and now kinda get how they work, but am having trouble.
I have a table of assumption data for, the allocation of common expenses across three products.
At the top of the table data used by the rest of the spreadsheet will populate with data that is then used in the gigantic calculation depending on which scenario is selected (I'll call this the populating table).
I've uploaded sample data. There are two tables there, factory maintenance (which populates using an array, although Google has not managed to handle that, which would be relatively straight forward to swap our with a LOOKUP), and Division of shared costs.
For the Division of shared costs, its somewhat more complicated though. Have a look at the sample sheet here -
https://spreadsheets.google.com/ccc?key=0AlJYmMeMJ83edGhHV3g1WXVsLVhTeS1aYVY1c2ZLRmc&hl=en
Is there a way to have an entire table populate based on a the scenario selected using lookup? i.e. when scenario 1 is selected, the entire populating table updates with it. Or would I need to use some other thing.
The problem is that the way I've structured the data, Lookup doesnt seem to work. I could change the structure of the data so that I could use LOOKUP but that would take ages and remove the logic to it, so I dont want to. A third alternative would be to ignore the requests of my boss.
I think what I want becomes pretty clear when you look at the if function I've used in the googlespreadsheet same.
Thanks in advance for any help, and let me know if more clarification is required.
Cheers.
Alex
After presenting the most rediculously detailed financial model of a factory to my boss, he complained that I used arrays and If functions where I should have used the LOOK Up Function.
So now I need to change the function that a cell uses, the problem is I dont know how.
I've looked at various lookup tutorials, and now kinda get how they work, but am having trouble.
I have a table of assumption data for, the allocation of common expenses across three products.
At the top of the table data used by the rest of the spreadsheet will populate with data that is then used in the gigantic calculation depending on which scenario is selected (I'll call this the populating table).
I've uploaded sample data. There are two tables there, factory maintenance (which populates using an array, although Google has not managed to handle that, which would be relatively straight forward to swap our with a LOOKUP), and Division of shared costs.
For the Division of shared costs, its somewhat more complicated though. Have a look at the sample sheet here -
https://spreadsheets.google.com/ccc?key=0AlJYmMeMJ83edGhHV3g1WXVsLVhTeS1aYVY1c2ZLRmc&hl=en
Is there a way to have an entire table populate based on a the scenario selected using lookup? i.e. when scenario 1 is selected, the entire populating table updates with it. Or would I need to use some other thing.
The problem is that the way I've structured the data, Lookup doesnt seem to work. I could change the structure of the data so that I could use LOOKUP but that would take ages and remove the logic to it, so I dont want to. A third alternative would be to ignore the requests of my boss.
I think what I want becomes pretty clear when you look at the if function I've used in the googlespreadsheet same.
Thanks in advance for any help, and let me know if more clarification is required.
Cheers.
Alex