Hi all,
Have used this forum a lot in the past so thanks for all the help up to this point!
I have had to make a pretty huge formula in an attempt to automate an existing report (unfortunately I can't change the format of the report), which is working but takes incredibly long to update (10 minutes plus on a Core i5).
The formula matches a date and a meeting room and returns how many hours that room was booked out for for the day. The complicating factor is that if the room was booked out for more than 8 hours, the formula needs to return 8 hours, and there is another formula that will return the number of hours in excess of 8. This is the first formula (returning a max of 8 hours):
=IF(IFERROR(INDEX(ImportedData!$F:$F,MATCH($D21&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)),0)+IFERROR(INDEX(ImportedData!$D:$D,MATCH($F$19&$D21,ImportedData!$B:$B&ImportedData!$A:$A,0)),0)>8,8,(IFERROR(INDEX(ImportedData!$F:$F,MATCH($D21&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)),0))+(IFERROR(INDEX(ImportedData!$D:$D,MATCH($F$19&$D21,ImportedData!$B:$B&ImportedData!$A:$A,0)),0)))
And this is the second (returning anything above 8 hours)
=IFERROR(IF(INDEX(ImportedData!$F:$F,MATCH($D53&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0))+(INDEX(ImportedData!$D:$D,MATCH($F$19&$D53,ImportedData!$B:$B&ImportedData!$A:$A,0)))>8, (INDEX(ImportedData!$F:$F,MATCH($D53&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)))+(INDEX(ImportedData!$D:$D,MATCH($F$19&$D53,ImportedData!$B:$B&ImportedData!$A:$A,0)))-8,0),0)
Is there any way that I can do this is a more efficient manner to make it quicker to update? I can't think of how to do this in VBA, or of how I can make the formula more efficient without modifying the report (which again I can't really do).
I've attached part of the report (heavily butchered to save on size and protect info), if you look at this, don't worry so much about the other broken formulas (I can fix these). Also, I'm not sure if the formulas will auto update or not now that I've sent this.
I know this a big ask, so thanks heaps for any time you put into this.
Regards,
Stuart
Bookmarks