[prawleprovi]

Apologies if this seems daft, but I am not experienced in any way with Excel!

I have to collate information on a monthly basis in a spreadsheet. I have a seperate sheet that has pricing information on it i.e. Type x=£123.00, Type y=£321.00 and so on. I would like to be able to enter the code e.g. x into the main spreadsheet and the price be entered automatically from the other workbook. Hope this makes sense?

[spot]

How many codes to you have roughly? Do the codes also have descriptions as well as prices? The two ways of doing it are either as a pull-down list or as a text field. Either way you surely want the description to appear as well as the price, to show they picked the right product.

[prawleprovi]

I have 120 codes that relate to as many descriptions and prices.

[spot]

You could juggle around with these lines and adapt them to what you need... All the dollars are absolute references so that you can copy the formula from where you initially type it and it still works.

120 codes is too many for a pull-down selection. I'll assume your pricing table's on Sheet 2 and that it's:

Headers, A1="Type" A2="Description" A3="Price"
Data, A2:C121 contain the table data

So, for (say) row 8 in Sheet 1 that has (say) column F as the entered type, column G as the displayed description and column H as the displayed price,


G8 will test if anything's in F8 and if there is, it shows either "Invalid Type!" or the correct description

=IF(ISBLANK(F8),"",IF(ISNA(VLOOKUP(F8,$Sheet2!$A$2 :$C$121,2,FALSE)),"Invalid Type!",VLOOKUP(F8,$Sheet2!$A$2:$C$121,2,FALSE)))


H8 will test if anything's in F8 and if there is, it shows either nothing if F8's invalid or the correct price (which might or might not be zero)

=IF(ISBLANK(F8),"",IF(ISNA(VLOOKUP(F8,$Sheet2!$A$2 :$C$121,3,FALSE)),"",VLOOKUP(F8,$Sheet2!$A$2:$C$12 1,3,FALSE)))