Register
Log In
Unanswered
Home
Power Query
Power Pivot
Power BI
June 25, 2016
Offline
Hello All 🙂
I have an index and match formula that works and I need to now vary this slightly and I cannot figure out how to get it to work.
What the formula does is look for a value on sheet 1, match the value on the SeriesSales sheet, then search the next 10 rows for "Site Total" and when it finds that it returns the $ value in column 3. Here is the formula
=IFERROR(INDEX(INDEX(SeriesSales!$C$1:$C$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)):INDEX(SeriesSales!$C$1:$C$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)+10),MATCH("Site Total",INDEX(SeriesSales!$A$1:$A$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)):INDEX(SeriesSales!$A$1:$A$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)+10),0)),"$0.00")
My problem is I now need to find the value, then search the following rows from that cell with the value until the first instance of "Site Total" is found, Â and then return the $ value shown in column 3. This text string could be 4 rows down or as many as 40+ rows down or anywhere in between
I hope I have explained this clearly enough!Â
Any help will be greatly appreciated 
July 16, 2010
Offline
It would help if you upload a sample file so we can see your question in context.
Power Query
Power Pivot
Power BI
June 25, 2016
Offline
Hi Mynda
Attached is a small example of the file - the problem I have is that site total can appear any number of rows after the site heading. Hope this helps to make my problem clearer? The original spreadsheet contains 1000+ rows. I need a formula solution as this is part of a larger spreadsheet that summarises data from a variety of files and different  sources for monthly reporting.
Thank you 🙂
July 16, 2010
Offline
Thanks for sharing a sample of your data. I don't recommend any formulas for this. The data really should be cleaned, then you can use PivotTables to summarise the data without any risk of formula errors.
In the attached file I've used Power Query to reformat the data into a tabular layout using Power Query. I've then summarised it in a PivotTable (see sheet Query & Pivot).
I've also added another PivotTable that summarises the data by employee and service by location to illustrate the different information you can get from the data when you format it correctly and use PivotTables.
Note: I've saved the file as a .xlsx which is compatible with Excel 2007 onwards. Power Query is available in Excel 2010 onwards. You can download Power Query here.
Please let me know if you have any questions.
Mynda
Power Query
Power Pivot
Power BI
June 25, 2016
Offline
Thank you Mynda but I cannot see any attachment? Where do I go to find that to download?
July 16, 2010
Offline
Oops, sorry I forgot to press 'Start Upload'. Here it is.
Power Query
Power Pivot
Power BI
June 25, 2016
Offline
Thank you very much Mynda this works exactly as I needed 🙂 I have looked at how you wrote the query and think I have managed to follow the very creative way that you came up with a resolution! I have another similiar report that needs to have data extracted over many hundreds of rows and whilst this solution won't work with that data it has given me some inspiration to "give it a go". I see that you have a course in power query which I think would be very helpful so I'm going to ask my boss if I can sign up for it.
Once again thank you very much for your assistance.
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
