Hi, I’m a new member and first-time poster. To give a little background to my setup and issue, my data source worksheet (Covid 19 Graphs) looks similar to the table below. The range is from row 160 to 211, and it gets a new date column added each week:
I created a formula which will find the largest value(s) for a specified date (adjusted from a static referenced date on the summary report tab, WeekSummary_Automation). I initially built the formula directly on the data source tab to test it, and it worked fine there, but when moved to the summary report tab (WeekSummary_Automation) within the same workbook, it has a #NUM! error. I tried to add in the worksheet reference to the formula in the new location, but I still get errors. I don’t often use the INDIRECT function, and I feel like the issue may be in my formatting of referencing the other worksheet and possibly in the required double quotation format. Since I’m using INDIRECT with other nested functions, I’m unsure what the solution/required format is on the new tab. Any help is much appreciated.
Here is the original formula, which works on Covid 19 Graphs tab but not on WeekSummary_Automation tab:
=LARGE(INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))),1)
...And here are just a few of my attempts of correcting the formula after moving it to the WeekSummary_Automation tab within the same worksheet:
=LARGE(INDIRECT("'Covid 19 Graphs'!"&ADDRESS(160,MATCH($D$2-4,'Covid 19 Graphs'!209:209,0))&":"&ADDRESS(211,MATCH($D$2-4,'Covid 19 Graphs'!209:209,0))),1)
=LARGE("'Covid 19 Graphs'!"INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")),1)
=LARGE("'Covid 19 Graphs'!"&INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")),1)
I created a formula which will find the largest value(s) for a specified date (adjusted from a static referenced date on the summary report tab, WeekSummary_Automation). I initially built the formula directly on the data source tab to test it, and it worked fine there, but when moved to the summary report tab (WeekSummary_Automation) within the same workbook, it has a #NUM! error. I tried to add in the worksheet reference to the formula in the new location, but I still get errors. I don’t often use the INDIRECT function, and I feel like the issue may be in my formatting of referencing the other worksheet and possibly in the required double quotation format. Since I’m using INDIRECT with other nested functions, I’m unsure what the solution/required format is on the new tab. Any help is much appreciated.
Here is the original formula, which works on Covid 19 Graphs tab but not on WeekSummary_Automation tab:
=LARGE(INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))),1)
...And here are just a few of my attempts of correcting the formula after moving it to the WeekSummary_Automation tab within the same worksheet:
=LARGE(INDIRECT("'Covid 19 Graphs'!"&ADDRESS(160,MATCH($D$2-4,'Covid 19 Graphs'!209:209,0))&":"&ADDRESS(211,MATCH($D$2-4,'Covid 19 Graphs'!209:209,0))),1)
=LARGE("'Covid 19 Graphs'!"INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")),1)
=LARGE("'Covid 19 Graphs'!"&INDIRECT(ADDRESS(160,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")&":"&ADDRESS(211,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0),,,"Covid 19 Graphs")),1)