VB alternative to if function?

[markellis88]

I use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.

IE:
Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..

I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.

Please Login or Register  to view this content.

We have now found after a year that four columns is too little for what now need. We need more like 12 columns.

The workbook is now at about 8 megs because I need to have this for each stock item and for each row on every day (66 rows by 55 columns). I run one sheet for each day of the month.
It works fine, and I will do it that way, it just opens such a margin for error and makes changing something real work, besides the really long tine it takes to save..

Would the best way forward be just to use the IF function and just embed another 8 statements in in the formula? Or is there an easier and smaller way?.

[kraljb]

Try using a SUM IF formula

But you'll need to offset slightly

If your spreadsheet is
|----A----|----B----|----C----|...|----X----|----Y----|
|-Invoice-|--Stock--|---Qty---|...|--Stock--|---Qty---|

It would be:
=SUMIF($B8:$X8,AH$6,$C8:$Y8)

Let me know if that works for you...
John

[mikerickson]

or =INDEX($Q8:$AA8,1,MATCH(AH$6,$P8:$Z8,0))

[markellis88]

Thanks they both work great!
The Sum If formula works a little better for me because it takes account of every instance of the stock code, whereas the Index function only takes account of the first one.

Thanks again!