macro instead of vlookups

[tinkerbelle]

hi everyone, i have tried the following using vlookups and sumif functions in my actual spreadsheet and it gets very slow due to the number of rows and columns.

If you look at the MainReport worksheet there is a drop down on cell G5
what i would like is for a macro to look at each worksheet from "Data" onwards and find each instance of Type 1 (or whatever is in cell G5 above) and return the values in rows D to O onto the Data sheet. i have tried to demonstrate this by putting a simple link in the cells on "Data" sheet.
If anyone can help i would be really grateful, thanks
Luce

[JBeaucaire]

I'm not sure what formulas you were using, but this data layout and report layout can be done with formulas and it should be very robust. INDEX/MATCH/MATCH is perfect for this.

I added some fake data on each of the report sheets to demonstrate.

[tinkerbelle]

Thanks JB, the reason i thought a macro would be better was that this is only an example from a much larger spreadsheet, which might quickly become unwield or too big, however I like your INDEX and Match formulaes and i think they will be just perfect.
thanks again.
Luce

[JBeaucaire]

I have used INDEX/MATCH on datasets 10mb+ and it is very robust! Since it isn't volatile, it's very good at not impacting sheet performance.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.