Date Ranges and Pulling Data

Question

Using Office 365 on an MS Surface device.&nbsp;I am trying to create a spreadsheet where I can enter daily data for various items (sales, quantity, price, etc.).&nbsp; This data is in a separate worksheet and is populated every day for the year.&nbsp; So, I have a row with dates and underneath, rows with all the data I am tracking for each date:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Jan 1&nbsp; &nbsp; &nbsp; &nbsp; Jan 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Jan 3&nbsp; &nbsp; &nbsp;etc...Quantity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120Price&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100&nbsp; &nbsp; &nbsp; &nbsp; $110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$240&nbsp;I then have a separate worksheet where I create daily, weekly, monthly, quarterly reports.&nbsp; What I want to be able to do is set a date range on the report worksheet (for example, Jan 1 to Jan 3) and have the report populate the data from the data sheet:&nbsp;Date range: Jan 1 - Jan 3Quantity: 330Sales: $450&nbsp;I imagine there is a simple formula for this...I have been using a massive series of "IF" statements and my spreadsheet is getting unstable...and too hard to fix/edit/use...&nbsp;Anyone have any thoughts?&nbsp;

sergeibaklan · Answer

ggjer&nbsp;
I'd suggest you to reconsider the data structuring, more exactly use table as
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Quantity&nbsp; Price Sales
Jan 1&nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp; &nbsp; &nbsp;&nbsp;
Jan 2&nbsp; &nbsp; &nbsp; &nbsp; 110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1
Jan 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $2
...
Sales will be calculated. Same with other fields. With that you may build reports using PivotTable or formulas relatively easy. Otherwise that's not simple task and in any case you need to do such transformation in background to generate reports.
&nbsp;
For already existing data that's one time job to transpose the data, could be done by formula or Power Query. In any case it's better if you provide sample file, otherwise someone shall make some guesses and assumptions (e.g. Jan 1 is text or date formatted as this), build the model and play with formulas. Without the file much less chances to have an answer plus assumptions could be wrong.

Forum Discussion

Date Ranges and Pulling Data

1 Reply