MS database problem, any help appreciated

Chocolate Chip

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.

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.

biZrb

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

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.

Clearlier

Chocolate Chip wrote: »

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.

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.

Chocolate Chip

Thank you very much!

Chocolate Chip

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

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!

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.

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!

beauf

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