I have a report that I'm responsible for managing that comes in weekly from several different members of my organization. They are sending certain statistics on each facility of our organization, which I then copy into a tab for the week in a master file and use the information reported by each facility to analyze how much they are over or under budget. I then report on a summary page all the facilities that are under budget by more than 5% (the way the formulas are set up in each week's tab, they do come out as a negative percentage). I then sort the summary sheet by the VP who is responsible for their performance and send the summary sheet only to my boss. Both the summary sheets and the sheets where the percentage over or under are calculated contain a complete list of each facility. The differences between the two are:
1) The summary only contains the result, not the calculation
2) The cell for facilities that are not under budget for more than 5% are left blank on the summary tab, where as on the weekly tabs, facilities have an answer to their formula
3) The summary tab has the results from each week in the quarter and is built upon every week, with information coming from each new week's tab
So, to summarize, I take the information contained in Column G on Sheet2 for week 1 (or Sheet3 for week 2, etc) and report it into Column D for week 1 (or Column E for week 2, etc) on the Summary tab.
Currently, I use a VLOOKUP formula for all facilities and just delete it out of the cells that aren't meeting the criteria my boss wants to see. That is, any facility meeting or exceeding budget or those who might be under budget, but they're under by 4.99% or less. But there's 200 lines of data to sort through and it's time consuming. I'm wondering if there's a formula that would also hold true if I needed to sort the sheet differently (which I often due depending on the metric my boss wants to measure) that would return the value from the working week's sheet to the Summary sheet only if it was -5% or greater. I tried =IF('Tab B'!G7<-4.99%,VLOOKUP('SUMMARY SHEET'!$A7,'010315'!A7:G205,7,FALSE),""), but it does not work if I sort the summary sheet differently than the sheet pulling the numbers.
Any insight that anyone could provide would be greatly appreciated.
1) The summary only contains the result, not the calculation
2) The cell for facilities that are not under budget for more than 5% are left blank on the summary tab, where as on the weekly tabs, facilities have an answer to their formula
3) The summary tab has the results from each week in the quarter and is built upon every week, with information coming from each new week's tab
So, to summarize, I take the information contained in Column G on Sheet2 for week 1 (or Sheet3 for week 2, etc) and report it into Column D for week 1 (or Column E for week 2, etc) on the Summary tab.
Currently, I use a VLOOKUP formula for all facilities and just delete it out of the cells that aren't meeting the criteria my boss wants to see. That is, any facility meeting or exceeding budget or those who might be under budget, but they're under by 4.99% or less. But there's 200 lines of data to sort through and it's time consuming. I'm wondering if there's a formula that would also hold true if I needed to sort the sheet differently (which I often due depending on the metric my boss wants to measure) that would return the value from the working week's sheet to the Summary sheet only if it was -5% or greater. I tried =IF('Tab B'!G7<-4.99%,VLOOKUP('SUMMARY SHEET'!$A7,'010315'!A7:G205,7,FALSE),""), but it does not work if I sort the summary sheet differently than the sheet pulling the numbers.
Any insight that anyone could provide would be greatly appreciated.
