Help selecting data for formulas through dropdowns

**eirikmagnussen** · 05-28-2013, 04:26 AM

Hi.

I'm working on a portfolio management spreadsheet which is supposed to be a tool in finding and calculating risks and choosing a low/medium/high scenario for applying to different projects (11 in all).

I've done a great bit of work on this spreadsheet to try to make things easy for the project managers and the finance part of this (me).

Anyway. As sort of a last summary I've thought it might be a good idea to be able to summarize all the data from the different projects in one spreadsheet were I should be able to choose the risk of all projects.
(see linked image - I work in Norwegian, but have tried to quickly translate to English to give you a general idea of how the spreadsheet will work)

http://i.imgur.com/0JiH50d.png

So, on to the problem:

When I pick a risk-type from the dropdown (on the right side of the spreadsheet), I want the sheet to collect and summarize data from several worksheets according to what risk type I've chose. Either Low/Medium/High or None is supposed to be options here (all data is in the other worksheets). All of the other worksheets are identical except for the values of the input data, so all the cells contains the same data-type.

So if I've chose low risk for 'ABC Nyheter' and high risk for 'Appguiden', none data for 'Forsiden' and Medium risk for 'Fotball' I want cell B11 to get this formula (in some sort or another) (='ABC Nyheter'!B53+Appguiden!B208+0+Fotball!B129)

I am sure I could find out how to do this through formulas if I browse this forum for a couple of hours or post a question in a different sub, but I am interested in learning easier ways to do this sort of stuff. I am sure this could be done by some Macro of some sort, which I am new to, so I don't have a full understanding of how this works, but can anyone here still help me out?

(Disclaimer: I'm not at excellent written english proficiency, so I'm sorry if something is unclear or badly written)

**Norie** · 05-28-2013, 04:38 AM

Erik

Are the only things that change the worksheet names or the cell references, or both?

PS The image apparently isn't available, perhaps you could upload a small example workbook, remove any sensitive data.

**eirikmagnussen** · 05-28-2013, 04:46 AM

Norie: well the Cell reference should also change when chosing risk type. Data for Low is at row 53, medium at 129 and high at 208.

Are you sure the link is not working? "http://i.imgur.com/0JiH50d.png" (I also attached a file)

I also uploaded the image to another host; http://postimg.org/image/n876ze9wh/full/

**Norie** · 05-28-2013, 04:53 AM

Erik

Missed the 2nd image which works and the one in your 2nd post which also works.

What exactly do you have in the dropdowns?

Could you upload a sample workbook?

That would let us see the whole setup.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**eirikmagnussen** · 05-28-2013, 04:59 AM

I'm on it. give me a couple of minutes!

EDIT: The file was too big to upload here, so I went and uploaded it to another host

See if this works:

https://www.box.com/s/lt7zvftxv05rbb2q5d72

**eirikmagnussen** · 05-28-2013, 05:38 AM

Did it look alright to you Norie?

**Norie** · 05-28-2013, 05:50 AM

Erik

Yes, it looks fine.

Still trying to work out how to approach this.

I've actually got something but I don't know if it's what you are really looking for.

Also, I'm wondering if there is anything else involved, for example the year and quarter.

Anyway, I've uploaded what I've come up with so far here's the link:

https://www.box.com/s/r1r2pzzcpvlcvfq9dbce

PS I change some things - removed all the charts (thought it would reduce size, it doesn't) and renamed the worksheets to the original names (I think).

**eirikmagnussen** · 05-28-2013, 06:16 AM

We're getting somewhere!

however I want all the cells from B11:W32 to be totals from the data chosen from the dropdown menus. that means when I have chosen a custom set of risks for each product I want Cell H18 to be a total of all the data for all consultant costs (Konsulenter) for Q3 2014.

(Ps.Monthly data is of no importance, the other worksheets sum these into Quarterly data which is the only thing I am interested in)