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

 

[littlemt]

THANKS SO MUCH!!!!

You are a total life saver......one question...

Can I still have the Inventory QTY listed in Column 'I' appear in column 'a' of the 'HP Showing' sheet...don't mean to be a pain, but I need to know how many of each I item I have.

I tried to work it out myself, but, you lost me...REALLY REALLY lost me.

Littlemt

 

[littlemt]

Hi T.Valko,

So I have been looking more closely at it and trying to get the QTY in the first column without bothering you.

First of all; I want to say, it is amazing what you know. I have already learnt a lot.

I think I 'semi' get how you are making it work, but whenever I try to replicate your formula, I get #VALUE! error. I don't know what I am doing wrong, but does it have something to do withe the curly brackets around the outside of your function?

Here are my 'misteps' attached.

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

I hope you can help again.

Littlemt

 

[T. Valko]

Hi T.Valko,

So I have been looking more closely at it and trying to get the QTY in the first column without bothering you.

First of all; I want to say, it is amazing what you know. I have already learnt a lot.

I think I 'semi' get how you are making it work, but whenever I try to replicate your formula, I get #VALUE! error. I don't know what I am doing wrong, but does it have something to do withe the curly brackets around the outside of your function?

Here are my 'misteps' attached.

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

I hope you can help again.

Littlemt

The formulas to return the qty is a lot simpler than the formulas to extract the sku and item.

The formulas to extract the sku and item are array formulas.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

Here's your file with the qty formulas added.

zzzLittlemt(2).xlsx

http://cjoint.com/?AJAr4qxZApc

 

[littlemt]

THANK YOU! THANK YOU! THANK YOU!

I never would of worked that out myself.

Littlemt

 

[T. Valko]

THANK YOU! THANK YOU! THANK YOU!

I never would of worked that out myself.

Littlemt

You're welcome. Thanks for the feedback!

 

[littlemt]

Sorry to keep on asking for help.

I am trying to complete my worksheet, but have hit a wall if the SUMIFS function.

It's again coming up with a #VALUE error and I can't work out why.

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

If you look at the 'HP Sell List' sheet, in column 'a', I have tried to replicate the SUMIFS function to apply to another value.

Can you tell me why is this error occurring?

THANKS AGAIN!! This should be the last question

Littlemt

 

[T. Valko]

Sorry to keep on asking for help.

I am trying to complete my worksheet, but have hit a wall if the SUMIFS function.

It's again coming up with a #VALUE error and I can't work out why.

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

If you look at the 'HP Sell List' sheet, in column 'a', I have tried to replicate the SUMIFS function to apply to another value.

Can you tell me why is this error occurring?

THANKS AGAIN!! This should be the last question

Littlemt

All of the ranges need to be the same size for the SUMIFS function.

=SUMIFS(HP_Sell,SKU_,C4,Item,D4)

The HP_Sell range is defined down to row 460 while the other ranges are defined down to row 456.

Change the range HP_Sell to be =Inventory!$J$5:$J$456. (or change the other ranges to go down to row 460)

 

[littlemt]

THANKS AGAIN!!

How do you know all this?....there seems to be so much to learn

You're the best!

Littlemt

 

[T. Valko]

THANKS AGAIN!!

You're welcome!

How do you know all this?

I've been using computer spreadsheets since they were invented!

....there seems to be so much to learn

Yeah, there is. I'm still learning myself.

You're the best!

If you insist!