Hi,
Sorry if this is already covered. But when I search the forum for "@" I get nothing; and when I search for "at" I get practically everything.
I have this code, and it's working great except for the "UNIQUE" formula. It works, but it doesnt list, you know what I mean? It stops at 1 result instead of listing all the unique results. Basically a unique list will be a list of unique dates, not just 1 date.
For the code below, an @ sign preceeds the UNIQUE formula, like so :
Formula Bar shows this:
=@UNIQUE(FILTER($F$11:INDIRECT("F"&(COUNTA($B$11:$B$2500))+11-1),$C$11:INDIRECT("C"&(COUNTA($B$11:$B$2500))+11-1)="P"))
I dont want that @-sign cause it limits the formula, like I said, to 1 result :
When I apply the UNIQUE formula in a fixed cell via a macro, like so :
It works - I get this in the Formula Bar:
=UNIQUE(FILTER($R$11:INDIRECT("R"&(COUNTA($B$11:$B$2500))+11-1),$B$11:INDIRECT("B"&(COUNTA($B$11:$B$2500))+11-1)=" WOTRAN ") )
What's this UNIQUE formula do? Whenever column B = " WOTRAN " (means 'writeoff' in our company) then give me the cell info in column R, then further pair that info down so only unique values from column R are listed. And do this for the data between rows 11 and the bottom of the list as determined by column B (column B was chosen cause it always contains data to the bottom of the list whereas other columns may contain blanks here and there).
Notice no @ preceding the "=UNIQUE...".
When I use the longer macro at top, the formula bar shows this:
=@UNIQUE(FILTER($F$11:INDIRECT("F"&(COUNTA($B$11:$B$2500))+11-1),$C$11:INDIRECT("C"&(COUNTA($B$11:$B$2500))+11-1)="P"))
What's this UNIQUE formula do? Whenever column C = "P" then give me the cell info in column F, then further pair that info down so only unique values from column F are listed. And do this for the data between rows 11 and the bottom of the list as determined by column B (column B was chosen cause it always contains data to the bottom of the list whereas other columns may contain blanks here and there).
Granted the formulas are a little different only because I'm looking for different data. But I think the @-error, which is my concern here, stems from the way I'm producing the formula. I think the Worksheet.Range.Cells method in the first code (if I'm using that terminology correctly, I'm a noob in that respect) is somehow causing a different outcome vs. the Range.ActiveCell stuff in the second code. That's the only thing I can see.
Why am I using different methods? Cause the location of the summary information is going to show at the bottom of an Excel list, which may be 20, 30, 50, 100, 200 rows long. So I have to keep the row variable, and then produce my summary relative to the bottom of the list. If there's 60 rows then the variable NR will be 63 - my summary start row.
Thanks.
Thoughts?
Bookmarks