![]() |
|
|||||||
|
|
|
|
|
|
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 ...
|
![]() |
|
|
Thread Tools |
|
#1
|
|||
|
|||
|
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? |
|
#2
|
||||||||||||
|
||||||||||||
|
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.
__________________
My System: Tim
|
|
#3
|
|||
|
|||
|
I have 120 codes that relate to as many descriptions and prices.
|
|
#4
|
|||
|
|||
|
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))) |
![]() |
|
| Bookmarks |
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Word/Excel, quick question | ashmehta | Office Suites & Applications | 6 | 22nd May 2008 09:56 |
| Excel question | HistoryGirl | Office Suites & Applications | 1 | 8th Apr 2008 08:09 |
| Excel Formula Help | Jonathon28 | Office Suites & Applications | 6 | 22nd Feb 2008 08:36 |
| Excel help | Dj Ram | Office Suites & Applications | 2 | 19th Feb 2008 11:22 |
| Excel between languages? | PranX | Office Suites & Applications | 10 | 12th Sep 2007 15:09 |
| Thread Tools | |
|
|