Ask Experts Questions for FREE Help !
Ask
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #1

    May 1, 2007, 05:38 PM
    2003 Excel - Ethnic Background Formula
    Hi, I am trying to put together a "Workbook" in my 2003 Excel. I have one sheet with the TOTAL sorted by Race and Living Situation.

    Then I have the sites Totals... that I connected to the TOTAL.

    Now I am trying to make a "everyday" use sheet... where the worker enters Example AA for African American and then it connects to the Sites Total page and adds there... and then connects to the TOTAL page and so on... the number will continue to rise...

    Is this possible? I can't figure out how to do it...
    colbtech's Avatar
    colbtech Posts: 748, Reputation: 66
    Senior Member
     
    #2

    May 2, 2007, 02:39 AM
    Yes, see attached sheet. Pressing help when the formula is selected will explain how it works. Any problems, we're here to help
    Attached Files
  1. File Type: zip Book1.zip (1.6 KB, 68 views)
  2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #3

    May 2, 2007, 06:55 AM
    I'm having trouble understand what you want. Are you trying to get a total by site and then grand total of each ethnicity on a daily basis? Or what?
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #4

    May 2, 2007, 03:33 PM
    Sorry it was vague... Ok let me think of how to explain better...

    I have 3 Sheets in my Work Book. I kind of worked backwards...

    Sheet #3- Grand Total for all ethnicities. Separated by Race...
    1 -African American,
    2- German,
    3- Hawaiian, etc.. for all centers combined...

    Sheet #2 - Grand Total for one center ( might not be adding another center)

    Sheet 3 and 2 I would like to "Lock" so the workers can't mess with those... the numbers will just change as a result of adding clients to Sheet #1

    Sheet #1 - The workers will be using this sheet constantly. Adding the information.. Example... Question of Ethnicity... The person they are entering is Hawaiian... so they would type HI or the number 3 and it would automatically calculate from that cell to the Sheet #2... and then Sheet #2 updates sheet #3... I have Sheet #2 and #3 connecting already.. but it is the joining of sheet #1 in there that I am wondering if it is possible.

    So in the end if on Sheet #1 I have 4 Different Clients listed as being Hawaiian, 2 Clients German and 1 African American then on the Sheet #2 and #3 it will have 4 Hawaiian, 2 German, and 1 African American... automatically...

    Was this clearer? =)
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #5

    May 2, 2007, 03:58 PM
    This would actually be better done in a database. Do you have Office Pro?

    On Sheet #1 are they just going to add people line by line?
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #6

    May 2, 2007, 07:42 PM
    Yes they will just be adding people line by line. Is this just super crazy to even try to attempt? =) Be truthful.. hehe..
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #7

    May 3, 2007, 05:17 AM
    No its not, nor is it too difficult, though its harder in Excel then it would be in Access. So I ask again, do you have Office Pro with Access?
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #8

    May 4, 2007, 12:54 PM
    I do have ACCESSbut I am unfamiliar with it. I have not had a need to use it. I should go play with it and see what I can get...
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    May 4, 2007, 01:19 PM
    As I said, this would be VERY easy in Access. You could create a table:

    tblDailyClients
    DailyClientID (Primary Key Autonumber)
    EthnicID (Foreign Key)
    LogDate

    You would also have a lookup table:

    tluEthnic
    EthnicID (PK Autonumber)
    Ethnicity

    Now you would create a form where there date is filled in automatically by setting its default value to Date(). The user then choose Ethnicity from a drop down list.

    Finally you would have a report that is grouped by Day and Ethnicity with a count for each grouping.

    Even without Access knowledge, you could put this together in less than an hour.
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #10

    May 7, 2007, 12:58 PM
    Thank you, I will try it in Access and I'll write back after (or during if I run into a problem) and let you know how it is going =) YOU GUYS ARE SOOO HELPFUL!!
    WvR's Avatar
    WvR Posts: 16, Reputation: 3
    New Member
     
    #11

    May 17, 2007, 04:38 AM
    You can simply use the count if function in excel

    on sheet 2 in the cell where you want the total of Hawaiin people for instance type the following formula
    =COUNTIF(Sheet1!B:B,"HI")
    where Sheet1!B:B = the column on sheet 1 where you typed the HI for Hawaiin - change this column in the formula to the one you actually have the HI in

    You repeat this for all the ethnic groups in their different cells and change the "HI" to whatever your indicator for that ethnic group is

    Hope this help
    Please let me know
    Jhanna_Hi's Avatar
    Jhanna_Hi Posts: 12, Reputation: 1
    New Member
     
    #12

    May 17, 2007, 10:32 AM
    Ah... this is around the line of what I started thinking of... the =IF factor. I have recently accepted a job elsewhere and tomorrow (Friday May 18) is my last day here so I will do what I can with this and then someone else can take it.

    I really appreciate everyone's answers, I tried meddling with the ACCESS but I couldn't figure it out, hehe... But thank you so much... Can I put a "chain" of IF factors? Like =IF(cell = HI then connect to this other cell)?

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Display formula in Excel [ 4 Answers ]

Hi, Can you please show me how to display all formulas in a spreadsheet? I already calculated the data using formulas in a spreadsheet, now I just want display the formulas. Are there any keyword shortcuts. Many thanks.

Excel formula pros needed [ 5 Answers ]

Im pretty good with Excel formulas but not pro enough for this. Is there a way to write a formula that will search for all instances of a certain date on a sheet and if it finds the date, it will take the cells value next to it and add them together?

Excel formula [ 3 Answers ]

I have a column that you manually enter a number; either 0 (zero) or any number greater than zero. I have another column with the formula of: =DAYS360(AN22,AQ22) If the resulting formula figure/number is greater than the manually entered number then I want a third column to equal: $0.00 But...

Excel 2003 issue [ 2 Answers ]

Hi I have a problem with the auto calculation feature in excel. It doesn't seem to be working. I have to use the super calculation feature, crtl+F9 or F9 in order for the calculation to work. Do you know why this is? I'm running excel 2003 (Office 2003 SP1) and the file was saved in excel XP....

Excel Formula needed [ 1 Answers ]

I am keeping an inventory in Excel. Let's say I start with 100 units of widgets, I take 20 widgets from the shelf, but return 10 wdgets later because I didn't use them. I need a formula that shows the 100, then I want to show that I took 20, and then have Excel show that I returned 10. Can...


View more questions Search