Dynamic Capacity and Drop Downboxes

[A_mutant]

Hello,

Building a spreadsheet (Excel 2010) for a client that wants to track different levels of capacity based on location. All of the widgets are moving and I want the total capacity of new location to be dynamic based on the drop down that is chosen on the widget page.

Details:

I have two pages, Widgets & Destination

Widget Page:

• Widgets have Size and Weight Columns. A third column has been added for DESTINATION. I have the destination cells as dropdowns. (Dest 1, Dest 2 etc etc) Which is completed.
• The Size and Weight of the Widget is static for this case.

Destination Page:

• Destinations have Total Size(#), Current Full (%), Total Weight Over Committed (%)
• Destinations match those in the drop down box on the Widgets Page

What I would like is when a new destination is selected from the Widgets page, the Destination Page will take the Size and Weight of the Widget into its values for Current Full and Total Weight Over Committed. If I move a Widget to another Destination on the Widgets Page, the Values will update correctly on the Destination Page.

FYI: I am using Widgets, Size and Weight as values to keep this simple on the forum. This is a Capacity roadmap for Storage of Virtual Machines in VMware. Total Weight Over Committed is actually Over Provisioned Space. So it would be a % above 100%. This is perfectly acceptable in VMware and Storage. However the question I have is totally 100% Excel. I will be keeping the terms to Widget, Size and Weight.

Back to topic. Would a formula in the cells for Current Full and Total Weight Over Committed work or would I need Macros?

Thank you for the help.

a_mutant