Those "1", "2" and "3" entries are not numbers you type in, they are all the formula
=COLUMN()
This puts the column number in each cell. Then instead of
=VLOOKUP(D1,Stock_Table,3)
You might use
=VLOOKUP(D1,Stock_Table,StockList!$C$1)
You can give StockList!$C$1 a name too, like Stock_Table_Price, so the formula becomes the much more readable
=VLOOKUP(D1,Stock_Table,Stock_Table_Price)
and then you can create names for Stock_Table_Description, and anything else you have added. Best to use "Stock_Table_Price" rather than just "Price" because if you add a second table with a price column, you'd have problems. Putting the table name as part of the column number name ensures uniqueness amongst different tables.
Now you can insert a new column, in between others, and won't have to change all your VLOOKUP formulae! Note that if you add a column at the end, it's the same thing as adding rows, so I suggest putting, say, a greyed-in column to mark the end of the table and end the Stock Table there. When you insert a column, you are kinda forced to insert a blank column before the greyed-in column, and that keeps the formulae intact. I personally use a greyed-in row instead of a terminator entry, since that limits the range of item ids, and is consistent. Then my tables are surrounded in a grey box.
Now, for the final step, just in case you decide to put the Stock List table somewhere other than starting in column A, the COLUMN formulae I mentioned should really be
=COLUMN()-COLUMN(Stock_Table)+1
which calculates the offset into the Stock Table for the current column.
Note that the range or name you use for the Stock_Table should NOT include this row of column numbers, it should be from the title line to the terminator line as before (i.e. now it would be StockList!$A$2:$C$7).
Edit: Oh yeah, one final thing. VLOOKUP, as shown, assumes a table ordered by the first column in ascending order, and any value you search for which isn't in the table will find the last available item before the searched value. So if you have item numbers 1 to 10, then 12 to 15, and you search for 11, you'll get the data for item 10. Not what you want. So add a fourth parameter to VLOOKUP, as zero, which says the search has to match exactly. It's slower, but finding an error caused by the wrong data being used would be nastier!
Edit 2: My edit 1 assumes that Excel works in the same way as OpenOffice in that respect. To be absolutely clear, look up the help on VLOOKUP and read what it says.
-- Edited by Rob-f58049 on Tuesday 29th of May 2012 09:24:59 PM
-- Edited by Rob-f58049 on Tuesday 29th of May 2012 09:28:48 PM
I'm having an issue with excel and hope someone on here can help?
I have 2 columns. One column will have the number 1 to indicate a sale for my client for a particular stock item. If I enter 1 in a cell I want the cell to the right of it in the next column to autmoatically say 7 (the net sale price of the item).
I'm trying to use an IF formula but hitting a brick wall at present. I've got as far as: =if(D1=1=7). This isn't working. Any ideas?
Although you specifically asked the answer for when the cell in the first column contains 1, reading the question more fully suggests what you probably want is something more future proof. An IF can be expanded on, which won't be too bad for a small number of items, but it can get silly.
I'd recommend you use a look-up table, so that you can also handle the prices for other stock items.
In which case, create a second tab on your spreadsheet, and on that tab create a table of stock items - eg:
(Note that this allows you to have a description that can be looked up, as well. You could also have other columns for other handy bits of information.)
Before the formula - note the presence of the last line; the terminator. This is nothing to do with Arnie, it's to allow the table to be expanded in a way that's simple to remember: If you want to add a new stock item, you insert it above the terminator. The formula below references the block of cells, including that line. If it wasn't there, it would be easy to add new stock items by just adding them on the next line(s) without updating the formula. With the terminator there (and included), when you insert a row above it, the formulae that reference the block are updated.
Now, back to the original tab, instead of using the not-very-future-proof IF in column 2, use a VLOOKUP. Assuming the column title "Item No" is in cell A1 on a tab called 'StockList', which means #1 is in A2, "Wibbled Wobble" in B2, etc., and using the same cell reference in the first tab as you did for the IF, the formula should be:
=VLOOKUP(D1,StockList!$A$1:$C$6,3)
The $ characters mean that when you copy the formula to the next line, the block isn't updated. Without them - ie with the formula as =VLOOKUP(D1,StockList!A1:C6,3) - if you copied it to the next line, it would become =VLOOKUP(D2,StockList!A2:C7,3) - which would drop the title line of the table, and the next line it's copied to would lose the first stock item off the list, the next copy would drop the second, etc.
Another way to get around that would be to use a named range for the stock table.
Highlight the table from the first column of the first item, to the last column of the last item. Click on "Formulas" on the menu bar, then on "Define Name" in the ribbon, then on "Define Name" in the little submenu. A dialogue appears: Type in a suitable name, such as "Stock_Table" and okay it.
With that done, your formula becomes:
=VLOOKUP(D1,Stock_Table,3)
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
Oh, yeah, if you want the blank cell shown if there's a formula in the second column, but nothing in the first, as Peasie did, then you can surround the VLOOKUP with an IF:
=IF(D1"",VLOOKUP(D1,Stock_Table,3),"")
This simply applies the VLOOKUP only if D1 is not an empty cell. You could also do it numerically with D1>0 if you think that's more suitable.
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
