+ Reply to Thread
Results 1 to 6 of 6

Making info appear in another cell

  1. #1
    Registered User
    Join Date
    08-01-2006
    Posts
    3

    Making info appear in another cell

    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!!


  2. #2
    Forum Contributor
    Join Date
    05-05-2006
    Posts
    143
    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.

  3. #3
    Registered User
    Join Date
    06-28-2006
    Posts
    29
    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

  4. #4
    Registered User
    Join Date
    08-01-2006
    Posts
    3

    Almost there

    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???

  5. #5
    Forum Contributor
    Join Date
    05-11-2006
    Posts
    104
    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

    Quote Originally Posted by 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???

  6. #6
    Registered User
    Join Date
    08-01-2006
    Posts
    3
    Thanks for all your help guys I have now cracked it and thanks to you learned a bit more about Excel too, you have been really helpful!!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1