Making info appear in another cell

[tasadin]

Sorry the title isnt better.

My wife walks and house sits dogs and we are trying to set up excel so
she can invoice easily. I have created a drop down list of the 12
different dog services eg Walk and Water, House visit, House sit, Board
overnight etc, and that worls really well, however is there a way that
when you choose one of these items from the drop down list excel will
automatically show the charge for that service in another cell so that
you dont have to input it manually.

I have created a drop down list of the prices which is sort of a
halfway house at the moment.

Any help would be appreciated, thanks in advance!!

 

[samprince]

i'd set the excel up so that you have in Column A the Job Undertaken
then the next columns the name of the job.
Column A ColumnB Column C
Job Undertaken, Walk Groom etc

In COlumn A you woudl enter what job was done,

in Coumn B, =IF($A2="Walk","$10","$0")
in Column C, =IF($A2="Groom","$15","$0")

Then Fill them down, so in each column a value would appear dependant
on what was entered into Column A. You Sum them at the bottom then
without having to do a pivot table.

 

[patele]

Well one way is to use Index and Match. You will have to have a list of
your services in one column and the prices for those services beside
them. These columns by the way can be hidden anywhere on the form. Lets
say for example I have the following list of services in column F Rows
1-5: Board Overnite, House Sit, Train, Walk, Water and in Column G Rows
1-5: $1.00, $2.00, $3.00, $4.00, $5.00. Then in cell A1 I have a data
validation drop down list of those services. My formula which I put in
cell B1 (can be what ever cell you want) would be:

=INDEX(G1:G5,MATCH(A1,$F$1:$F$5,1))

What ever service I click on in cell A1 will display the cost in cell
B1. Just make sure your data validation list and the list in column F,
1-5 are in the same order. You could also make your list a defined name
like say (PriceList) then instead of the above formula it would look
like this:

=INDEX(PriceList,MATCH(A1,$F$1:$F$5,1))

Let me know if this helps.

Ed

 

[tasadin]

Thanks a bunch for the help there guys really appreciate it ,
I had to juggle the index thing a bit and put a 0 at the end not a 1 to
get it to work. Just one other query tho, I have made a load of the
cells with drop down lists to try and accomodate however many dogs
might be dealt with during a week. But if you dont choose a list from a
cell the cost cell comes up NA which then screws up my total calc at the
bottom. So how do I make it produce a 0 or just be blank in the cost
section when the index formula has run if nothing has been selected
from the drop down list???

 

[mr_teacher]

Hi,

Would have thought that if you use the formula

=IF(ISNA(INDEX(G1:G5,MATCH(A1,$F$1:$F$5,1))),0,INDEX(G1:G5,MATCH(A1,$F$1:$F$5,1)))

it would sort things out for you? Might just have to put in the changes
that you did before though

Regards

Carl

 

[tasadin]

Thanks for all your help guys I have now cracked it and thanks to yo
learned a bit more about Excel too, you have been really helpful!!!!!: