VLOOKUP?? Help

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt

Care to post a small sample of data?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi,

I really need help.

I have an inventory list on one sheet. On the next sheet, I want it to only list items that I have more than one of.

I am using this formula to do that:
=+IF(Inventory!I6>=1,Inventory!$I$6,"")

But, everything shows up. How do I get it to not show anything if it is zero or blank.

Thanks in advance.

Littlemt
Try this...

Data:

Book1
AB
1ProductQty
2Item119
3Item2_
4Item3_
5Item4_
6Item569
7Item60
8Item724
9Item859
10Item958
11Item100
12Item110
13Item128
14Item1379
15Item140
16Item156
17Item1678
18Item17_
19Item1839
20Item1961
Sheet1

In the formulas I use the following defined named ranges:
  • Product
  • Refers to: =Sheet1!$A:$A
  • QTY
  • Refers to: =Sheet1!$B$2:$B$20
On Sheet2...

Book1
ABC
1CountProductQty
211Item119
3_Item569
4_Item724
5_Item859
6_Item958
7_Item128
8_Item1379
9_Item156
10_Item1678
11_Item1839
12_Item1961
13___
Sheet2

Enter this formula in A2. This will return the count of records that meet the criteria.

=COUNTIF(Qty,">0")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)>A$2,"",INDEX(Product,SMALL(IF(Qty>0,ROW(Qty)),ROWS(B$2:B2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Qty,MATCH(B2,Product,0)-1))

Select both B2 and C2 and copy down until you get blanks.

Here's a small sample file that demonstrates this.

zzzLittlemt.xls 19kb

http://cjoint.com/?AJywxeB2zjn
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
THANKS SO MUCH!! I don't really understand what it all means, so I will have to digest it and apply it. I am grateful for you taking the time to help me.

I tried uploading my sheet here, if you are at all curious.

http://www.mediafire.com/?rwn2wt96zr9grya

i will give it a shot tonight.

THANK YOU THANK YOU THANK YOU AGAIN!!

Littlemt
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

THANKS SO MUCH!! I don't really understand what it all means, so I will have to digest it and apply it. I am grateful for you taking the time to help me.

I tried uploading my sheet here, if you are at all curious.

http://www.mediafire.com/?rwn2wt96zr9grya

i will give it a shot tonight.

THANK YOU THANK YOU THANK YOU AGAIN!!

Littlemt
I looked at your file...

Which column is the QTY column? Looks like there are 3 columns that contain some kind of qty value.
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
OOHH, sorry for not clarifying.

I am trying to display results that have more than 1 QTY in column 'I'. The 'HP show' column....so really, the only items that should not show up so far is the last three items.

Littlemt.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

OOHH, sorry for not clarifying.

I am trying to display results that have more than 1 QTY in column 'I'. The 'HP show' column....so really, the only items that should not show up so far is the last three items.

Littlemt.
Hmmm...

There are only 3 numbers greater than 1 in column I on the Inventory sheet and none of those entries are the last entries.

:confused:
 

littlemt

New Member
Joined
Oct 3, 2011
Messages
10
Goodness....sorry for not being clear. i would like the 'HP Showing List' sheet to show items that have a QTY of 1 or more in column "I" on the 'inventory' sheet.

So, the HP sheet (if I can made it do what I want) would list all items except for lines 7(sku: ADA-NT-5-07-F; Queen Panel Footboard), 12 (sku:ADA-NT-5-17-F; EK Panel Footboard), 38 (sku: AUS-ME-6-70-0; GATHERING TILE TOP TABLE), 39 (sku: AUS-ME-3-55-0, LADDERBACK BARSTOOL), and 41 (SERVER).

I hope this makes more sense.

Thanks again for your help. I really REALLY appreciate it :)

Littlemt
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Goodness....sorry for not being clear. i would like the 'HP Showing List' sheet to show items that have a QTY of 1 or more in column "I" on the 'inventory' sheet.

So, the HP sheet (if I can made it do what I want) would list all items except for lines 7(sku: ADA-NT-5-07-F; Queen Panel Footboard), 12 (sku:ADA-NT-5-17-F; EK Panel Footboard), 38 (sku: AUS-ME-6-70-0; GATHERING TILE TOP TABLE), 39 (sku: AUS-ME-3-55-0, LADDERBACK BARSTOOL), and 41 (SERVER).

I hope this makes more sense.

Thanks again for your help. I really REALLY appreciate it :)

Littlemt
Ok...

I'm going to post this as a reminder so I'll see it later when I return.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,186
Messages
5,857,845
Members
431,900
Latest member
Paradime0346

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
Top