can I send data to different cells depending on a drop box list on the same row?

[Ferj]

Hi I'm trying to create a pipeline budget tracker. I have different worksheets for different industries I work with, for example "film or TV". There are two different investment mechanisms for each industry called Comm1 and Chall1. I need to track the total investment by companies who invest via either Comm1 or Chall1, but I need the totals to be separated across England, Scotland, Northern Ireland and Wales.

I can set up drop down lists on the Film sheet for which nation and which of investment mechanism is being recorded on each row. I have a totals worksheet with cells that should total each nation's investment and split that total across Comm1 and Chall1.

Can I use conditional formatting so that depending on which nation and which investment mechanism is selected on a row in Film then only those numbers will be added up and totaled in the relevant cell on the totals page?

The point being on my totals page I should see a separate total investment amount for each industry (film, TV) in England, each industry in Scotland etc. But I will also have totals for each investment mechanism within in each industry in each nation.

Hope that makes sense

 

[json]

you put all of this data on 1 sheet, a pivotTable may work nicely for you.

 

[Ferj]

Thanks Json,

Unfortunately I can't do that. I mean, I can use pivots and slicers, but this tracker is going to be used by lots of different people in the organisation. The point is they just want a simple form to fill in and then I will pull regular reports from it so pivots etc will scare them from entering the data. I could always just simply add columns for each but I've been asked to make the form as simple as possible.