CODEWITHSUNDEEP
excelexcel-formula
David Richards
David Richards

Reputation: 33

Filter Out Blank Value Cells on Another Sheet In Excel

In Excel, I am trying to filter out empty rows from the USER INVENTORY MODEL sheet and show them all, without empty rows, in the TESTING sheet. I have found a website that shows how to do this if the columns are next to each other but not when they are on different sheets. Can anyone tell me how to modify the formula below to work on another sheet?

Below is the formula as I have it now"

=IFERROR(INDEX('USER INVENTORY MODEL'!B$2:B$300,SMALL(IF('USER INVENTORY MODEL'!B$2:B$300<>"",ROW('USER INVENTORY MODEL'!B$2:B$300)),ROW(2:2))),"")

Upvotes: 1

Views: 259

Answers (1)

user4039065
user4039065

Reputation:

Try,

=INDEX('USER INVENTORY MODEL'!B:B, AGGREGATE(15, 6, ROW('USER INVENTORY MODEL'!B$2:INDEX('USER INVENTORY MODEL'!B:B, MATCH("zzz", 'USER INVENTORY MODEL'!B:B)))/SIGN(LEN('USER INVENTORY MODEL'!B$2:INDEX('USER INVENTORY MODEL'!B:B, MATCH("zzz", 'USER INVENTORY MODEL'!B:B)))), ROW(1:1)))

I have assumed that 'USER INVENTORY MODEL'!B:B contains text values, not numbers.

Upvotes: 1

Related Questions