Drop down Box Assitance

**philjthommo** · 01-09-2013, 01:57 AM

Hello!

I have spent several hours searching the web for my answer. I am wanting to have first sheet of my excel change data according to the top dropdown box.

The data will be pulled from different sheets within the workbook depending on the month selected. ie Projected data will all be pulled from the 'data' sheet depending on the month, while the actual data will be filled into each month from non aligned cells.

The only cells i want to change are colored blue and the Loc A (Year to date) cells will have to calculate all the previous months income & Expenses.

Please let me know if you can assist with the formulas that are required.

Thank you in advance.

**FDibbins** · 01-09-2013, 02:02 AM

Just to make sure I understand you correctly...if the dropdown B1 says january, then you want to pull the data from DATA column B, if it says February, you want the data from column C?

**philjthommo** · 01-09-2013, 06:36 PM

Hi FDibbins,

Yes that is one of the places i am wanting to pull information from. I have tried to further explain in the excel.

-Green colored boxes information is to be pulled from the 'data' sheet.
-Purple colored boxes information is to be pulled from each months sheet (of which will be filled in throughout the year)
-Red colored boxes information is to be pulled from the 'data' sheet with a running total. (ie when march is used it would be calculated as =SUM(Data!B37:D37), and when November is selected it would show the calculation =SUM(Data!B37:L37)
-Blue colored boxes information is to be pulled from each month as a running total of which will be imputed every month and the cells may not be the same place (i can edit the formula every month for the blue boxes if needed. (for example when February is selected it would look like =SUM(January!B16+Febuary2!B16).

I hope this clears up what i am trying to achieve and apologies for the poor example formulas.

Thanks

**philjthommo** · 01-15-2013, 12:10 AM

Hello Forum,

Any help with this excel would be greatly appreciated.

Thanks again.
Phil

**FDibbins** · 01-15-2013, 01:33 AM

Hi sorry for the delay in replying.

to begin with, you dont need to use =sum(ref) to reference a cell, you can just use =ref.

on the Summary sheet, if you want to pull from the month sheets, based on C3, use this
=INDIRECT($B$1&"!B19")
adjust the B19 part as needed for the rest of the similar formulas (I only did 1), and please note that the formula will reference B19 on ANY sheet in the pull-down, so either make sure that the data you want for that "return" is in B19 in EVERY sheet. Or if you need to, you could move B19 further down, but it HAS to be in the same place in EVERY sheet. the same goes for any other reference you add.

If you want to pull data from the DATA sheet, based on the month, use this...
=INDEX(INDIRECT(SUBSTITUTE($A$4," ","_")),MATCH('Progress Report 2013'!A7,Data!$A$7:$A$18,0),VLOOKUP($B$1,$G$1:$H$12,2,FALSE)+1)

I moved your summary data off to the side so you can still see your original answers, then put the new formulas where they need to be