Summarize Data from Underlying Sheets Using JoinText

[torourke17]

Hello,

I have a formula that is able to look at an underlying sheet and summarize a variety of exception codes to a single summary sheet. The formula excludes duplicate exception codes. If there are no exception codes it returns a "-". The issue I'm running into is that each day, the range of results can change. It could be K17:K23 one day, but it could be K17:K100 the next day, the data will always start in K17. Instead of having to adjust the formula every day, I want the formula to grab any values within column K, but when I do K:K, it returns an error. Below is a formula and attached is a picture of what I'm working with. Further, I want to be able to take this formula and drag it down to capture a dozen+ different accounts (underlying tabs), where column A = the tab name. Lastly, I'd also like the ability for the formula to excludes the "-" and the column header "Exception Code".

It's a huge ask, but I've found this site to be extremely helpful.

"=TEXTJOIN(", ", TRUE, IF(MATCH('101'!K17:K23, '101'!K17:K23, 0)=MATCH(ROW('101'!K17:K23), ROW('101'!K17:K23)),'101'!K17:K23, ""))"
"=TEXTJOIN(", ", TRUE, IF(MATCH('102'!K17:K23, '102'!K17:K23, 0)=MATCH(ROW('102'!K17:K23), ROW('102'!K17:K23)),'102'!K17:K23, ""))"

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With your formula

=TEXTJOIN(", ", TRUE, IF(LEN('101'!K18:K500)>1,IF(MATCH('101'!K18:K500, '101'!K18:K500, 0)=MATCH(ROW('101'!K18:K500), ROW('101'!K18:K500)),'101'!K18:K500, ""),""))