VLOOKUP?? Help

[littlemt]

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

 

[Aladin Akyurek]

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]

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
	A	B
1	Product	Qty
2	Item1	19
3	Item2	_
4	Item3	_
5	Item4	_
6	Item5	69
7	Item6	0
8	Item7	24
9	Item8	59
10	Item9	58
11	Item10	0
12	Item11	0
13	Item12	8
14	Item13	79
15	Item14	0
16	Item15	6
17	Item16	78
18	Item17	_
19	Item18	39
20	Item19	61
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
	A	B	C
1	Count	Product	Qty
2	11	Item1	19
3	_	Item5	69
4	_	Item7	24
5	_	Item8	59
6	_	Item9	58
7	_	Item12	8
8	_	Item13	79
9	_	Item15	6
10	_	Item16	78
11	_	Item18	39
12	_	Item19	61
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]

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]

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]

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]

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.

 

[littlemt]

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]

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.

 

[T. Valko]

Ok...

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

OK, here's your file with these formulas implemented. The formulas are slightly different than those I used in the sample file I posted.

zzzInventorytest.xlsx 23kb

http://cjoint.com/?AJzvvzxe50S