Forum Discussion
Formula help please
Hi. I need some help writing a formula. I have only basic formula skills and I need to write a formula that if a particular number is entered in a cell then the next cell will show a value for that. I am updating a spreadsheet for a horse show entry form which previously has had the entrant manually enter the class number and manually enter the entry fee value. I'd like for the form to automatically drop the value in once the class number is entered. Some classes have a letter also ie 2W and there about 30 different class numbers, a lot have the same entry fee but there is some variation.
The way the form is set up they enter one class number per line eg
Class 1 $8
Class 3 $10
Class 5 $12
Class 8 $30
Hope someone can help me!! Thanks! :)
Vanessa
3 Replies
Hi Vanessa McKay,
Good Morning.
It is very easy. In one sheet, Please list out all classes in "Column A" and in next column specify the entry fee. This is base data for bringing entry fee to where you want.
Now, go to the cell where you want the result and put formula.
Eg.
If you have base data in "Sheet1" and you specifying class name in "Cell A2" in "Sheet2", put the formula in B2.
=VLOOKUP(A2,Sheet1!A:B,2,0)
Thank you! I have done this and it worked well. The next issue I have come across is that my "total" formula adding the cost of all the entries is not working now. I have allowed 40 lines for the entry of class No's and if all 40 are completed then my sum works but where they aren't it my entry fee column shows #N/A which I'm assuming is why my total sum formula isn't working. If you could advise how to fix that, I'd really appreciate it. Thanks
Hi @Vanessa McKay!
Try this:
=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,0);"0")
This will replace your #N/A with 0 and the adding will work.
/Anders
