+ Reply to Thread
Results 1 to 9 of 9

A couple of queries

  1. #1
    Registered User
    Join Date
    01-30-2007
    Posts
    14

    A couple of queries

    Hi, I have a couple of queries for anyone feeling like helping me!

    Firstly, I have rota registers to enter into two spreadsheets - one for how many hours our clients have had, one for how many hours the staff members have worked. I have set them up like so

    Spreadsheet 1: Client names along top, January dates along side

    Spreadsheet 2: Staff names along top, January dates along side.

    A rota register will have the service users name at the top - then is split into days, and the support worker will put in what hours they've personally done with them.

    What I want to know is - can I set up anything that will allow me to enter both client and staff at the same time, or do (as i suspect) I need to do it manually?

    My second question is can i change a cell ( i know how to do drop down lists) but so that if I have a list saying yes or no for example no will automatically come up red, and yes will automatically come up green?

    Thanks for reading - hope someone can help!!!!

  2. #2
    Registered User
    Join Date
    01-30-2007
    Posts
    14
    and thirdly - can I sort data ascending by the second word i.e. surname as oppose the first name without entering it surname, first name?

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Ok, for the first question I'm not quite sure what you mean, but for your second and third question, here goes

    For the Yes No
    Go to Format > Conditional Formatting, in the first condition put Cell value is equal to "No" (without the quotes) then format to Red, click Add then do same for second condition "Yes" format to Green

    To sort by surname, put an extra column in with this formula

    =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

    this will find the surname in column A, irrespective whether you have Mary Smith or Mary Jo Smith
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Registered User
    Join Date
    01-30-2007
    Posts
    14
    have just done this - but it'ssaying #value! (copy and pasted from your message!)

    Thank you for your help!

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Have you put a name in A1?

  6. #6
    Registered User
    Join Date
    01-30-2007
    Posts
    14
    No, first name is B1

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    If your name (i.e. Mary Jo Smith) is in B1, then then formula needs editing to that cell reference

    =RIGHT(B1,LEN(B1)-FIND("^^",SUBSTITUTE(B1," ","^^",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))

  8. #8
    Registered User
    Join Date
    01-30-2007
    Posts
    14
    is still saying the same.

  9. #9
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Can you provide a small zipped sample of your data, this will be easier to understand what you are trying to do, then hopefully a solution can be found.

    To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Sent to > Compressed file”, then attach this to your post.

+ 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