INDIRECT(ADDRESS) function referencing another worksheet (also using LARGE and MATCH functions)

jrimm

New Member
Joined
Sep 11, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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:

Indirect_Address.PNG


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)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
it has a #NUM! error.
Any errors with indirect, etc would cause a #REF! error, the #NUM! error indicates that there are no numeric values in the range that the formula is looking at.

I don't see any need for indirect or address in your formula, the following should be sufficient for what you're trying to do.

This will look for D2-4 in row 160 and return the max from rows 161 to 190 in the column where D2-4 is found, which is what I believe that you are trying to do based on my interpretation of your formula.

Note that aggregate(14,6 serves the same purpose as large but will still give you a result if there are any errors in the source data.

=AGGREGATE(14,6,INDEX('Covid 19 Graphs'!$161:$190,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0)),1)
 
Upvote 0
Thank you so much, jasonb75! Your aggregate formula worked! I had never used that function before; I'll have to start using it. I did tweak the array to include up to row 211; this is what I needed though. Thank you again! Regarding the #NUM error, that was showing up after I moved the formula to a different worksheet without adding the data source worksheet reference again to either the INDIRECT or LARGE portion of the formula. I think it was trying to find the reference on the new worksheet, which would not have been correct. I just didn't know how to add the correct format for the data source worksheet reference on the new tab. This is a nice workaround though to avoid having to use INDIRECT. This works great!
 
Upvote 0
I have a related follow-up question. The formula jasonb75 provided is working great for the values I need. I thought I would then be able to tweak the formula to get the corresponding categories for each value, but I'm getting a #REF! error with my attempts. The data contributor provides the category column spanning across columns A and B, so I don't know if that is the issue or my formulas, or both. I'm not confident I'm setting the formula up correctly.

Here is one attempt:

=INDEX('Covid 19 Graphs'!$A161:$B211,AGGREGATE(14,6,INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0)),1),1)

I've also tried to wrap the AGGREGATE function in a ROW function for the row argument of the INDEX function, but that gives a formula error:

=INDEX('Covid 19 Graphs'!$A161:$B211,ROW(AGGREGATE(14,6,INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0)),1)),1)

And using another MATCH function for the row argument gives an #N/A error:

=INDEX('Covid 19 Graphs'!$A161:$B211,MATCH(AGGREGATE(14,6,INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0)),1),'Covid 19 Graphs'!$161:$211,0),1)

(I'm not confident in my array argument for the first MATCH function in the formula above. Since I'm also matching the column for the large function being performed, I didn't know how to be more specific with the array here).
 
Upvote 0
If you need to return corresponding data then you need to include ROW in the array.

Note that there is a random reference to Z1 at the end of the formula. This part should be looking at the cell with the original aggregate formula from post 2.

=INDEX('Covid 19 Graphs'!$A:$A,AGGREGATE(14,6,ROW($161:$190)/(INDEX('Covid 19 Graphs'!$161:$190,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))=Z1)))
 
Upvote 0
Thank you for your quick reply and continued help, jasonb75! I tried the new formula replacing Z1 with the cell reference with the original aggregate formula from post 2. The only other thing I changed was I made the first array more specific with row numbers since there is a lot of other data on that worksheet in column A, and I replaced row 190 with 211 in all the arrays. This time I got a #VALUE error:

=INDEX('Covid 19 Graphs'!$A161:$A211,AGGREGATE(14,6,ROW($161:$211)/(INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))=$C$78)))
 
Upvote 0
This time I got a #VALUE error:
Yeah, that will happen. The first part must be the entire column, the aggregate part eliminates any data outside of rows 161 to 211.

Setting the range of INDEX to A161:A211 would need a longer and unnecessarily more complex array to return the correct result.
 
Upvote 0
Good to know, thanks. I've removed the rows in that array now, but I'm still getting the #VALUE! error for some reason:

=INDEX('Covid 19 Graphs'!$A:$A,AGGREGATE(14,6,ROW($161:$211)/(INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))=$C$78)))

Any other ideas? What is the / before the second INDEX function? I wasn't understanding that part and meant to ask in my last post. Thanks again!
 
Upvote 0
oops, not sure how but I managed to delete an important bit.

=INDEX('Covid 19 Graphs'!$A:$A,AGGREGATE(14,6,ROW($161:$211)/(INDEX('Covid 19 Graphs'!$161:$211,0,MATCH(WeekSummary_Automation!$D$2-4,'Covid 19 Graphs'!$160:$160,0))=$C$78),1))

The formula used here is probably a bad example to try and learn from, simplifying it a little to help you see how it works.

=INDEX(A:A,AGGREGATE(15,6,ROW(161:211)/(B161:B211=Z1),1))

ROW(...)/(range=criteria) divides the row numbers by true or false depending on whether or not the criteria is met. Any rows that do not meet the criteria will result in #DIV/0! errors, aggregate finds the first row number from those that are not eliminated as part of that process.
 
Upvote 0
Great! Thank you again for your help! Sorry for my delayed reply. This new formula worked for what I needed. It took me a while to figure out the rest of the table, but I think I've got it now.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top