Data entry simplification

**ohlan** · 08-03-2010, 04:19 PM

I have about 2,000 entries to go and I was wondering whether I could type in the description of the product in certain cells and have those descriptions correspond to price amounts that would then go into various formulas in other cells. For example, Lense #1 costs $100, Lense #2 costs $200, Material #1 costs $50... and if I type Lense #1 in cell 1 and Material #1 in cell 2 I want a third cell to tell me $150. I don't have the proper Excel training to know how to do this. Thanks for anyone's help!

**Whizbang** · 08-03-2010, 04:38 PM

Attached is an example.

Essentially I created a price table. I then named the lenses and materials ranges to be used as a data validation list. I also named the whole prices table for shorter formulas.

Then I created the data sheet and threw in the formula

=INDEX(Prices,MATCH(A2,Lenses,0)+1,MATCH(B2,Materials,0)+1)

Index takes a row number and a column number and returns the corresponding value from a table. So, to get the row and column number, i used match to search down and accross using the named ranges. I added 1 to each result because the ranges did not start in row or column # 1.

**The Exceller** · 08-03-2010, 04:54 PM

I have attached another sample for you which also uses the data validation technique but lays out sections of your costs based on the products you enter into specific cells. You can expand this however you like using a master list of what certain things cost. Let me know if you have any questions.

**ohlan** · 08-04-2010, 08:59 AM

Thank you very much for your response!

I think my example may have been a little too simple though. Would there be a way to use this method given if the data had 3-4 details that determined its price?

**ohlan** · 08-04-2010, 09:09 AM

I should phrase that a little better. Each lense has a different base cost depending on whether it has been mounted or not. And then it has an additional price depending on which material is used. So I really need to have two different tabs for the method Whizbang suggested, or for the data validation method I would need to reference two different lists. How could I go about setting that up? Thank you!

**JBeaucaire** · 08-04-2010, 09:37 AM

Ohlan, notice how the two respondents so far posted an example spreadsheet so you could SEE their suggestions in action?

You should do the same. Create and post a clearly demonstrative sample workbook. Represent a good set of sample data (don't oversimplify) and then mockup 2-3 examples of the results you would want to see from that sample data.

This lets us see the actual layout of your resource data, the layout of your expected results, and then we can help automate the content of the results. Help us help you.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**ohlan** · 08-04-2010, 10:49 AM

Ok, good idea. Attached is an example of what I have to work on. I'm trying to get an estimate of the damage done by mispricings. After the description of the product, I have the amounts that were charged for different aspects of the lenses, followed by the total, and then I have the amounts that should have been charged, the total, and the difference between the two. I was hoping that I could simplify this second part such that I could simply type the description and the prices actually charged, and have the prices that should be charged automatically appear. Any suggestions are greatly appreciated.