I thought I was pretty smart when it comes to spreadsheets in Excel, but I'm not. I'm trying to work in the MS Excel Time & Attendance template, but running into formula/formatting issues.
Anyone willing to help out?
Thanks

I'm no expert, but can usually figure stuff out. What's the issue?

Just a hunch, but if you're trying to calculate hours based on clock in/out times, format the cells using he 24 hour (military time) format. When you add or subtract those, it gives you hours. That doesn't work for overnight shifts, however. For that you need a formula. See this link: https://www.techrepublic.com/a...orked-for-any-shift/

I don't understand why it's so complicated.

Also found this from MicroSoft. Good luck.
https://support.office.com/en-...a3-b13e-737715505ff6

quote:

Originally posted by KurtZ66:
I'm no expert, but can usually figure stuff out. What's the issue?

It'll only let me add a max of five names to be used in the drop down menu.

Not what I thought. Not sure how the template is set up, but try re-creating the drop down. In the cell you want the drop down to appear, click on the Data tab. Then the Data Validation drop down.
Then Data Validation again. In the Allow drop down, select List. Then, in source, select a column in another tab in the workbook where you've pre-filled the names. Click Enter and OK.

That cell will now have an arrow and a slide to show all the values you entered in the column you pre-filled.

Hope that helps.

quote:

Originally posted by KurtZ66:
Not what I thought. Not sure how the template is set up, but try re-creating the drop down. In the cell you want the drop down to appear, click on the Data tab. Then the Data Validation drop down.
Then Data Validation again. In the Allow drop down, select List. Then, in source, select a column in another tab in the workbook where you've pre-filled the names. Click Enter and OK.

That cell will now have an arrow and a slide to show all the values you entered in the column you pre-filled.

Hope that helps.

This. My guess is your data validation is an absolute reference and you're entering names below the list range.

I'm an Excel Whisperer. What you need to do for lists is to set up a Dynamic Named Range with a name that you will enter in the Validate dialogue. The benefit to this method is you can add names on the roster sheet or delete names and the list will automatically reflect the new list.

The details are below:

Here's the setup. If my assumptions are different, you'll have to adjust accordingly or let me know and I can adjust. We can also do it offline by phone if you want.

I'm assuming you already have the names already listed on a separate sheet in the Excel file. Let's call that sheet Roster. I'm assuming the names are in column A beginning in Row 2 with the Column Header in Row 1.

Click on the sheet where you have the names listed or create the sheet. Click on Formulas tab then Name Manager then click New. For the Name, enter EmployeeName (No spaces, you can use underscore if you prefer.)

Scope: Workbook

Enter the following formula in the Refers to: box without the quotes. "= OFFSET ('Roster'!$A$2, 0, 0, COUNTA ('Roster'!$A:$A) - 1, 1)"

Click OK. If you're used to Excel, you know you can click on the cell A2 and column A:A as you write the formula then add the $ signs afterwards.

Click on the cell where you want to use the drop down list. Then in the Data Validation dialogue, you know to Allow "List." Then in the source box, enter "= EmployeeName" without the quotes. Then you're set.

If you're doing this on a sheet formatted as a Table, there's some extra steps to get the table to reflect the new formula but I'm assuming you're not using a data table where you're using the validated list.

quote:

Originally posted by Rey HRH:
I'm an Excel Whisperer. What you need to do for lists is to set up a Dynamic Named Range with a name that you will enter in the Validate dialogue. The benefit to this method is you can add names on the roster sheet or delete names and the list will automatically reflect the new list.

The details are below:

Here's the setup. If my assumptions are different, you'll have to adjust accordingly or let me know and I can adjust. We can also do it offline by phone if you want.

I'm assuming you already have the names already listed on a separate sheet in the Excel file. Let's call that sheet Roster. I'm assuming the names are in column A beginning in Row 2 with the Column Header in Row 1.

Click on the sheet where you have the names listed or create the sheet. Click on Formulas tab then Name Manager then click New. For the Name, enter EmployeeName (No spaces, you can use underscore if you prefer.)

Scope: Workbook

Enter the following formula in the Refers to: box without the quotes. "= OFFSET ('Roster'!$A$2, 0, 0, COUNTA ('Roster'!$A:$A) - 1, 1)"

Click OK. If you're used to Excel, you know you can click on the cell A2 and column A:A as you write the formula then add the $ signs afterwards.

Click on the cell where you want to use the drop down list. Then in the Data Validation dialogue, you know to Allow "List." Then in the source box, enter "= EmployeeName" without the quotes. Then you're set.

If you're doing this on a sheet formatted as a Table, there's some extra steps to get the table to reflect the new formula but I'm assuming you're not using a data table where you're using the validated list.

This is way over-my head. But I appreciate the help.

quote:

Originally posted by JR78:

This is way over-my head. But I appreciate the help.

If you're working with templates and validated lists, it wouldn't be over your head.

The words may look daunting but if you just open Excel and follow each line, you'll be done in under two minutes relaxed.

quote:

Originally posted by Rey HRH:
I'm an Excel Whisperer. What you need to do for lists is to set up a Dynamic Named Range with a name that you will enter in the Validate dialogue. The benefit to this method is you can add names on the roster sheet or delete names and the list will automatically reflect the new list.

The details are below:

Here's the setup. If my assumptions are different, you'll have to adjust accordingly or let me know and I can adjust. We can also do it offline by phone if you want.

I'm assuming you already have the names already listed on a separate sheet in the Excel file. Let's call that sheet Roster. I'm assuming the names are in column A beginning in Row 2 with the Column Header in Row 1.

Click on the sheet where you have the names listed or create the sheet. Click on Formulas tab then Name Manager then click New. For the Name, enter EmployeeName (No spaces, you can use underscore if you prefer.)

Scope: Workbook

Enter the following formula in the Refers to: box without the quotes. "= OFFSET ('Roster'!$A$2, 0, 0, COUNTA ('Roster'!$A:$A) - 1, 1)"

Click OK. If you're used to Excel, you know you can click on the cell A2 and column A:A as you write the formula then add the $ signs afterwards.

Click on the cell where you want to use the drop down list. Then in the Data Validation dialogue, you know to Allow "List." Then in the source box, enter "= EmployeeName" without the quotes. Then you're set.

If you're doing this on a sheet formatted as a Table, there's some extra steps to get the table to reflect the new formula but I'm assuming you're not using a data table where
you're using the validated list.

sorry for butting in however just wanted the OP to know those steps are simple, you should be able to do it following the instructions step by step

However,

Rey that worked great, I have a spread sheet with multiple work sheets housing data records.

A key one being location ID so I took your steps and created the drop down list as you said. Works great however I need to expand it a bit, if you can help great.

However ID alone isn't sufficient, I need to see the client name (too many ID's to be positive I've grabbed the correct ID, can it be made to only insert the ID but also display the name (a second field on the same client sheet) and/or populate the name field on this worksheet

The worksheet I"m operating in is named Claims, the two fields are FH_ID and FH_Name sourced from the FH worksheet, it would be nice to be able to click, scroll input the id and populate the FH_ID and FH_name...

quote:

Originally posted by HRK:


sorry for butting in however just wanted the OP to know those steps are simple, you should be able to do it following the instructions step by step

However,

Rey that worked great, I have a spread sheet with multiple work sheets housing data records.

A key one being location ID so I took your steps and created the drop down list as you said. Works great however I need to expand it a bit, if you can help great.

However ID alone isn't sufficient, I need to see the client name (too many ID's to be positive I've grabbed the correct ID, can it be made to only insert the ID but also display the name (a second field on the same client sheet) and/or populate the name field on this worksheet

The worksheet I"m operating in is named Claims, the two fields are FH_ID and FH_Name sourced from the FH worksheet, it would be nice to be able to click, scroll input the id and populate the FH_ID and FH_name...

Two ways, you can achieve this.

1) Assuming the IDs are unique (one per specific customer) and that on the source sheet the ID column is to the left of the Name sheet, you can insert a column on the sheet with the pull downs for the name (I can't imagine you don't have a column for the name already) and use a vlookup formula to pull the name.

"= VLOOKUP ($A2, 'Data Sheet'!$A:$B, 2, FALSE)" and populate that down the column. The $A2 should change to $A3, $A4, etc. down the rows. I'm also assuming column A in the data sheet is ID and column B is the client name. If not, just replace the columns and the 2 before the false to however many columns the client name column is away from the client id. This will pull up the client name for the ID value in column A. Or another way of looking at the formula is $A2 in the client worksheet is the Client ID and you would put the VLOOKUP formula in the cell for the Client Name if this is just one worksheet for one client type of thing instead of a table which is my default mode of thinking.

If on the data sheet the client ID column isn't on the left of the client name column, we would just need a little bit more complicated formula but I'll wait for you to confirm.

2) The other way would be to create a new column in the data sheet that combines the ID and Name using the following formula: '= $A2 & " - " & $B2' You'll need to keep the two inside quotation marks in the formula. Then you can pull the list off this column and see the ID - Name combination as you scroll down the drop down list.

But I'm assuming you have other formulas that use the list column that you'll have to change in order to use the ID value only. And that's just added work and makes the other formulas more complicated, so my first preference is option #1 as it's the least work. But it's your choice.

Yes we have unique ID per client, and there is a second column with the location name on the client worksheet along with columns for all client contact Data.

What I would like to see is when I select the list button in the ID field on the sales sheet is that I see both the ID and the name so I know I picked the correct ID but just populate the ID in the field. (I can't remember all the ID/Name Associations and they are not always on the order when they first come into the office)

Is that possible

quote:

Originally posted by HRK:
Yes we have unique ID per client, and there is a second column with the location name on the client worksheet along with columns for all client contact Data.

What I would like to see is when I select the list button in the ID field on the sales sheet is that I see both the ID and the name so I know I picked the correct ID but just populate the ID in the field. (I can't remember all the ID/Name Associations and they are not always on the order when they first come into the office)

Is that possible

Let me noodle on this and I'll get back to you. My first thought is VBA programming but I want to see if there's a simpler method.

Thanks, the simpler the better LOL

There is definitely a brain-trust event going on here. However, can I just send it to one of you, versus me trying to input code?

quote:

Originally posted by JR78:
There is definitely a brain-trust event going on here. However, can I just send it to one of you, versus me trying to input code?

Sure, email it to me please. Email is in my profile. Thanks.

quote:

Originally posted by HRK:
Thanks, the simpler the better LOL

I had it after a couple of minutes but I had to run errands.

It's going to be based on option 2.

I'm going to assume Claims is just a table with columns for the different information. If Claims is more like a "form," the solution is still the same, the cell references would be different as expected.

On FH worksheet, add the column ID - Name. I'm assuming FH_ID is column A and FH_Name is column B. I'm also assuming ID values do not have any spaces. We're going to use the first space as the marker separating ID from Name. Enter the formula = $A2 & " - " & $B2. A sample value would be "101123 - Rey HRH"

Create the Dynamic Named Ranged for this column as previously provided. Let's call it ID_Name.

In the Claims sheet, you'll need to add a cell (if form) or a column (if table). Label this column anything like "Key." In the cells below the column, Do the Data Validation for list calling for the ID_Name dynamic named range. If this is a form and you don't want the value to be visible after you select it, just change the font color in the cell to white or whatever the cell color is. If you don't care, then the value will simply be visible. This will allow you to scroll through the list with both ID and Name visible.

I'm assuming the new column in the table was inserted as Column A and the ID column and Name column were pushed out to columns B and C respectively.

The ID Column which is now column B, enter the following formula: = Left ($A2, Find (" ", $A2) - 1). This formula will pull the ID number from the key column which starts at position 1 and ends one position before the first space.

Then all the other previous formulas should automatically reference the ID number in column B.

You could add the new key column at the end of the columns in the Claims sheet but that means you'll have to scroll to the right first.

quote:

Originally posted by HRK:
Thanks, the simpler the better LOL

I’m just following up whether you saw my proposed solution and if it works for you. You don’t have an email in your profile else I would have followed up using that route.

Well there should be an email there, I'll update it, I haven't put in practice yet due to work flow, try and give it a go asap