Where can I add ISNA to this formula??

[EasonGT]

=IF(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))

I've almost got what I need for a mind bending series of sorts that go from worksheet to worksheet. I need to add ISNA along with the ISBLANK above. Any help (if possible) is appreciated. I'm a newb... lol

 

[wigi]

=IF(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))

I've almost got what I need for a mind bending series of sorts that go from worksheet to worksheet. I need to add ISNA along with the ISBLANK above. Any help (if possible) is appreciated. I'm a newb... lol

Can't you put the VLOOKUP formulas in a (hidden) column?

That would reduce the number of functions calculated by Excel.

Then, you reference these cells like f.i.

=IF(OR(ISBLANK(Z1),ISNA(Z1)),"",Z1)

Cell Z1 houses the VLOOKUP (change if needed).

Wigi

 

[Matty]

Try:

=IF(OR(ISNA(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))

There might be a more efficient way of doing this though.

Matty

 

[Scott Huish]

Why the ISBLANK in the first place?
Why would you be returning blank from the VLOOKUP formula, and if you actually want to return blank from a value, put ="" in column D of your table, so that you actually return blank, then you don't need to check for it.

Then perhaps this:

=IF(ISNUMBER(MATCH(1,'D-Sort-R'!A96:A450,0)),VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0),"")

 

[Mike Blackman]

Hi Eason,

Try;

=IF(OR(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),ISNA(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))

 

[EasonGT]

Kinda funky what I'm trying to do, and it's been killing me. I hope I explain this well.

Store# Store Location Region District Sales Inventory
1953 B'ham, AL 1 7 $1.5 2.5%
0625 Atlanta, GA 5 2 $2.1 4.2%
1845 Nashville, TN 1 6 $1.4 3.2%

I have over 400 stores with a little more info than this. I'm attempting to have a worksheet that actually reads the Region AND District and puts those all together on one portion of the worksheet. Then it would move on to the next District within that Region until all the Region/District stores are listed and then it would move on to the next Region and so on. If this makes no sense, I apologize...

SEE IMAGES I ADDED BELOW

 

[Mike Blackman]

Hi,

Could you not simply sort the data using;

Data - Sort

You can sort by upto three criteria like this.

 

[EasonGT]

Trying to take this:

And "turn" turn it into the sheet below automatically when someone adds info. This would eliminate the user from adding data, then copying and pasting to another worksheet and then sorting the new worksheet.

 

[EasonGT]

Try:

=IF(OR(ISNA(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))

There might be a more efficient way of doing this though.

Matty

Thank You - So far that appears to be what I was wanting and it appears to be working. Of course someone may read this and decide I'm taking the long way around the mountain. THANKS!

 

[wigi]

Of course someone may read this and decide I'm taking the long way around the mountain. THANKS!

Of course, you're calculating 3 times a VLOOKUP formula whereas once would suffice...

Wigi