Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MS database problem, any help appreciated

  • 24-10-2014 1:50pm
    #1
    Registered Users Posts: 43


    I am trying to create something very simple in either Excel or Access and I am starting to feel really stupid! I am hoping someone on here can help me.

    Is it possible to create a database where you could enter data and it would prompt data to appear in the cell beside it. Like I would like to put a persons name in one cell, and their staff number appear beside it? Or put in the name of a duty, and the hours of the duty would appear in the cell beside it. As in: I pick "Night1" from a drop down menu and "8 hours" appears in the cell beside it automatically.

    Sorry if I am not explaining this very well. It is just such a simple thing and I cant manage it. I tried it in Access but its really complicating things. Excel does not look as nice, but it would do, it seems to just want to convert the data but not keep the original entry.

    What I need it for is logging people that are working overtime. It needs to be very simple looking so it will be used, and they can be paid properly.


Comments

  • Registered Users Posts: 509 ✭✭✭Zen 2nd


    This should be all possible in Excel.

    Drop down list: http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx

    IF statement: http://office.microsoft.com/en-us/excel-help/if-HP005209118.aspx

    You can only nest up to 7 IF statements though, so if there is more than 7 nights you might have to reevaluate how you want the data entered.


  • Registered Users Posts: 617 ✭✭✭biZrb


    You could use a vlookup table to auto populate the second column.


  • Registered Users Posts: 43 Chocolate Chip


    Thank you Zen 2nd, that is great. There are hundreds of staff though, do you think that could still work in Excel?

    biZrb, I dont know what a vloopup table is? Sorry I did warn you I was a bit stupid :) would you mind explaining it to me if you have the time.

    Thanks again I really appreciate the replies.


  • Registered Users Posts: 4,454 ✭✭✭Clearlier


    I am trying to create something very simple in either Excel or Access and I am starting to feel really stupid! I am hoping someone on here can help me.

    Is it possible to create a database where you could enter data and it would prompt data to appear in the cell beside it. Like I would like to put a persons name in one cell, and their staff number appear beside it? Or put in the name of a duty, and the hours of the duty would appear in the cell beside it. As in: I pick "Night1" from a drop down menu and "8 hours" appears in the cell beside it automatically.

    Sorry if I am not explaining this very well. It is just such a simple thing and I cant manage it. I tried it in Access but its really complicating things. Excel does not look as nice, but it would do, it seems to just want to convert the data but not keep the original entry.

    What I need it for is logging people that are working overtime. It needs to be very simple looking so it will be used, and they can be paid properly.

    What you're asking for is relatively straightforward but I suspect that you'll need to think through what you're trying to do a bit more. Anyway, to answer your question for the example of an employee's name and their staff number:

    1. Create a list of employee names with their staff numbers in the column beside it. For this example we'll sat that you have data in columns A and B.

    2. Decide where to enter the staff person's name that you want to find. We'll say cell D1 for the purposes of this.

    3. Choose another cell where you want the employee number to appear and type in the following formula: =INDEX(B:B,MATCH(D1,A:A,0))

    The above will work but as I said you'll probably need to think through what you actually want to achieve as on it's own the above doesn't do very much.


  • Registered Users Posts: 509 ✭✭✭Zen 2nd


    What way will the staff have access to the Excel file? Depending on your answer, using an excel sheet may or may not be a bad idea.

    Also with that amount of staff an IF statement wouldn't cut it, what other posters have said is more useful.


  • Advertisement
  • Registered Users Posts: 43 Chocolate Chip


    Thank you very much!


  • Registered Users Posts: 43 Chocolate Chip


    IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


  • Registered Users Posts: 3,495 ✭✭✭Lu Tze


    Zen 2nd wrote: »

    You can only nest up to 7 IF statements though, so if there is more than 7 nights you might have to reevaluate how you want the data entered.

    This is definitely gone from the newer excels, i have nest 30 something if statements in 2010, not sure what the new limit is!

    Just mind those close brackets!


  • Registered Users Posts: 509 ✭✭✭Zen 2nd


    Lu Tze wrote: »
    This is definitely gone from the newer excels, i have nest 30 something if statements in 2010, not sure what the new limit is!

    Just mind those close brackets!

    Ah cool, I just googled the solution. I'd say with that amount of data, if statements would get very tricky if changes were required.


  • Registered Users Posts: 3,495 ✭✭✭Lu Tze


    Zen 2nd wrote: »
    Ah cool, I just googled the solution. I'd say with that amount of data, if statements would get very tricky if changes were required.

    Indeed, doing mass balance on a daily time step for approximately 100 years, luckily had a separate input sheet where i could change variables which were all linked in the if statements. I could see my laptop crying as tens of thousand of cells updated whenever i changed an input!

    Probably should have been done in something else!


  • Advertisement
  • Closed Accounts Posts: 22,651 ✭✭✭✭beauf


    for hundreds of staff records, I would have gone with a database which would be more robust.


Advertisement