VLOOKUP INDEX MATCH then copy paste / Link?

Using Excel 2003
The example is a workbook with 3 sheets
The first is the data base (Up_Down) that has 8 COLS A:H
The second sheet (Up_Dn_Red) I created to extract all the data from sheet one that was with the condition Red and inserted that in COLS A:H
The third sheet (Up_Dn_Blue) I created to extract all the data from sheet one that was with the condition Blue and inserted that in COLS A:H
Then on each of the 2nd and 3rd sheets I extract the data that has the condition that matches weekdays from the COLS A:H and I link that to the next set of 8 COLS starting with Monday at COL J then Tuesday at COL R etc. So if no data for Tuesday that should result in a blank/unpopulated set of cells for COL R::X but if it does then it should increment the row. IE take the latest Tuesday and populate

For now the database (Up_Down) is built by linking to another sheet that is manually input
The (Up_Down_Red and Up_Down_Blue) sheets are built manually
and I would like to find a solution that lookups up the database, and matches the data to then either copy paste or link in the relevant sheets

I suspect I should be looking for an INDEX MATCH solution with ISBLANK? to enable the increment expansion of a new day? and/or even INDIRECT ?

Hi alleyb,

After looking at your problem, I believe you can do it all using a Pivot Table and not need the extra sheets.
Find the attached where I put a Pivot Table of your sheet 1 data. You can use the filters to show Blue or Red. You can group the dates by months. BTW - I changed your Col G to =Weekday so you can also filter by the day of week.

Find the attached.

Hi alleyb,

Group by weekly - see http://www.contextures.com/xlPivot07.html and look at the "Number of Days" box.

To input the sum, I simply drag and drop the High, Low and Close field names into the summation area of the pivot table design. I know their aren't two of any of these per day so I could also change the Sum to Min or Max and would get the same answer.

When you group by month you will need to worry about the Sum vs Min or Max as I think you will want the values on the first day of the month and last day of the month. I haven't thought about that too much yet.

Pivot Tables in 2003 aren't as easy to use or robust as the newer versions of Excel. If you really have 2003 then search the net for more examples and learn.

I hope Pivot Tables are a much better answer than your separate sheets.

Hi MarvinP
I have finally managed to re-produce in the local machine spreadsheet what you created for me
It was a case of not understanding (amongst most of the stuff) how to get the PivotTable to show the Columns of Open High Low Close to be represented vertically rather than horizontally and that was a case of dropping the Data greyed button onto the total col and hey presto
Much obliged for what is a great tutorial in essence of how to create a PivoTable and even how to manipulate the data with the Group etc

There's a BUT.
The issues that arise are as follows
My existing multiple sheets exist because they in fact are referenced to calculate the number of Mondays that are Red and the number of Mondays that are Blue then restated as %ages. this then is taken into another sheet purely for comparison sake and I haven't yet figured how the "group by month" should be manipulated to get the 1st of a month for the open and the last of a month for the close especially as there is 29 years of data the MAX for the high and MIN for the low columns works just fine

PS once again thanks for the links and pointers. much appreciated

Hello alleyb,

See the attached, For the monthly total you can use Pivot Table or Database functions.

Hey alleyb

Find the attached where I've added two more close summations. In the first I show the value as percent change from the previous day and the second I show the percent change from the close on the first day of the Pivot Table.

Pivots are great - I think you can do more than you think with them. I you really need the first and last day of the month, you can filter the dates to only show those roes.