Retrieve Data When Date Changes

[katnarbs]

Hello,

I'm wondering if there is a way for excel to run a simulation to retrieve a value of a specific cell for each day of the year. Example:

I have a drop down list that contains every day of the year. When I change the date, some cells are populated with values from a table of data using vlookup (only values for that day are displayed)

A1 - 1 B1 - 20
A2 - 2 B2 - 10
A3 - 3 B3 - -15
A4 - 4 B4 - 5
A5 - 5 B5 - -10
B6 - Sum(B1:B5)

The value of B6 changes based on the data that is retrieved from the table for that particular day.

I want excel to automatically populate cells on a separate sheet that have all the days of the year in column A and the values for B6 on each of those days in column B.

Can this be done?

 

[OGCV1]

From what I understand of your problem..

SUMIF function may do the job, adding together all the values for that date. Depends how the data in the original table is laid out.

 

[katnarbs]

Thanks for your reply.

Sorry for the poor explanation. It's not so much a matter of summing the values but rather that I want excel to take the value in that specific cell and record it elsewhere, for each day of the year (note: only one day of data is displayed and it changes with the drop down list). I'm wondering if there's some sort of simulation that will take all the dates in the drop down and run through them to find that value in cell B6 (as in the example) and spit it all out on a separate sheet. I want the result to be 2 columns, one column that contains the dates from Jan 1 to Dec 31 for 365 rows, and one column that records the value in that specific cell (B6 in the example) on each day.

 

[OGCV1]

What I was suggesting was instead of taking the value from B6, creating a Formula that works out the answer from the original data table - the one you VLOOKUP from. Can you post the VLOOKUP formula from B1?

 

[katnarbs]

The value in "B6" is actually much more complex in my file. My values are actually related to hours of the day and electricity usage per hour. So when I change the date using the drop down list, electricity usage for each hour of that day is populated on my sheet. Subsequent columns then use that data to determine other bits of data, and the final column (the value which I want to record) is based on the values of the previous columns.

The vlookup formula is [FONT=&quot]=VLOOKUP('Data Input'!D4,Database!1:1048576,2,FALSE), where Data Input contains the drop down list of dates and Database is my pivot table of data.[/FONT]


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}</style>

 

[katnarbs]

I've done a little more looking around to try to figure out what I need to do.

I think I need a macro that will cycle through every value in my drop down list and return the value in another cell.

Example: My drop down list is in D4 on Sheet 1. I want the macro to cycle/loop through the entire list and record the value in X32 on Sheet 2. The result will be a list of days of the year in column A and corresponding values of X32 for each day in column B.

Ideas on how I might achieve this?