Single Cell Drop Down to Determine Data Sheet Lookup

Hello,
I am trying to create a spread sheet that will allow me to trend and proof the invoice data from one of our Colo sites we use. I am able to download monthly usage data from their web portal. Based on a drop down selection in cell A1, I would like to have the main sheet populated with that corresponding months data (on a separate sheet) but utilized the lookups and formulas from that main sheet. So, if I select FEB 2016 from the drop down, I would like the data from sheet 02-2016 input into the main sheet that would have all of the formulas and lookups which would correspond to the selected sheet. Image below and spread sheet attached. Please let me know if you have further questions.

Colo Excel.jpg

Thanks in advanced for any help on this!

Bryce

Hi there,

In your workbook, Columns B, C, D and E contain data for February, but Column F uses VLOOKUP formulas to display data for January. Is this intentional? Which month's data sould be displayed in Column F when Columns A-E contain data for (e.g.) April?

Regards,

Greg M

Crap... yup... that was me messing around and not discarding changes. Should look like this.


Thanks!

Hi again,

Thanks for the very prompt reply.

Sorry - I should have noticed this before - the Rack Levels in Column G of the Usage-Cost Detail worksheet aren't included in the monthly data worksheets. Are those values entered manually on the Usage-Cost Detail worksheet after the monthly location/consumption data values have been imported?

Regards,

Greg M

Yes they are... they don't usually change. But there are rare instances where we may "upgrade or downgrade" racks.

Thanks for another very prompt reply.

Ok on that - I'm going out for a few hours, but I should be able to post something here later tonight (5:45pm here now).

Greg M

Great! Thanks for all your help!

Bryce

So, the Detail sheet has 78 because of the two rows of subtotal for the individual Colo "Cage" space we have. Right now, each cage space has a different negotiated cost (hoping to fix that in the next few months).

The row sequencing changes because of the way the Colo designates their space. Right now, they are in the process of changing up their naming conventions to match across all of their different co-location buildings (our cages are spread across two different buildings).

Greg, it looks like you have hit the nail on the head! This is great! This spread sheet is a little rough that I gave you. I think I can translate what you have done here and hopefully clean up what it is we are trying to accomplish. I have no doubt I will be back asking for help on other bells and whistles that my boss is surely going to want me to add!

Thanks for all your help!

Bryce

I looked at pivot tables, but would have to apply the lookups and calculations for every month first, correct? The way we are billed is different for each of our two "cage" sites and our usage varies monthly. Eventually this book will have data going back to 2014 and will continue on for the life of our contracts.

I would be interested in seeing how you solved this with pivot tables.