New Ytest
Sign out
Bimmerpost
Login
BMW E39 5-Series Forum | 5Post.com
BMW Garage BMW Meets Register Search Today's Posts Mark Forums Read  
Go Back   BMW E39 5-Series Forum | 5Post.com > BIMMERPOST Universal Forums > Off-Topic Discussions Board > Video + PC Gaming / Computing

Post Reply
 
Thread Tools Search this Thread
      10-27-2017, 06:48 AM   #1
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
1127
Rep
4,584
Posts

 
Drives: F30 335i
Join Date: Apr 2014
Location: Down South

iTrader: (2)

Help with Excel spreadsheet configuration/formula..

So I manage about 65 RN's and CNA's, and we currently use a 3 ring binder to write in absences, sick days, vacation, etc. Well in an effort to go paperless and still manage this task, I came across this template on Google that's called "Employee Attendance Tracker". It's an Excel template that is already kind of setup, but for some reason when I go to add in more employees on the employee list tab, the calculations kind of get messed up or don't work at all..

My question... Is anyone able to add about 70 more spots on tab 3 "List of Employees" that match the current format on that tab, and then have it formulate back to tab 1 "Calendar View" so all I have to do is add in their names on tab 3 and can see it on tab 1 in the drop down box..?? It's already set up that way now, but only has like four spots for employees names and when I add in more, they don't show in tab 1..

Here's the link to the template...
https://templates.office.com/en-us/E...ker-TM02780235

Side note: The template already has a bunch of irrelevant data that came with it, so all that stuff can be zero'd out if need be since I'm going to have to do that anyways.

Thanks in advance!
__________________
F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 08:06 AM   #2
amgraham
New Member
41
Rep
27
Posts

 
Drives: 2011 BMW X5 x35d
Join Date: Mar 2015
Location: Pittsburgh, PA

iTrader: (0)

Go to the "List of Employees" tab, click on Employee Names, then select the "Design" tab at the top. At the left will be "Resize Table". Expand the range to include your newly added names.
Appreciate 1
      10-27-2017, 08:07 AM   #3
1and1
Loves me some MHD!
1and1's Avatar
United_States
1852
Rep
1,711
Posts

 
Drives: faster than he should...
Join Date: Jul 2016
Location: Tally, FL

iTrader: (2)

Garage List
'09 135i  [10.00]
Easier to do it yourself and have control for future edits if needed- It's just using Data Validation.

To allow the list of names to be extended you just have to tell it the source list is longer.
1- select the name drop down cell (2C)
2- on the Data tab click on Data Validation
3- Change the Source line, increasing the last number reference
3a- Original: ='List of Employees'!$B$4:$B$8
3b- New: ='List of Employees'!$B$4:$B$108

This adds 100 more lines to the data source list- extending it down to B108, instead of just B8. As you add names to the list they will be added to the drop down. You will not just get blank entries in the drop down- it's smart enough to just show populated values.

The same change will update the Employee Leave Tracker tab automatically so you won't need to make additional edits on that tab.

Where it uses Named Ranges, for example Leave Types, the named range will expand dynamically as you add additional leave types.
__________________
1and1
135i - E82.N54 2009 BSM - MHD / xHP / DCI, PR CP & 7.5" Race IC / N55 mid & Borla
. . WL RSFI, Koni, Eibach & Dinan CP's, M3 CA's / EBC Red's & Firehawk 500's
X1 - E84.N20 2013 MGM - JB4 - The Wife's, now with new turbo & ewg
Appreciate 2
Soul_Glo13282.50
      10-27-2017, 08:42 AM   #4
Hawkeye
Brigadier General
Hawkeye's Avatar
No_Country
2070
Rep
4,365
Posts

 
Drives: '07 Z4 Coupe, '21 X3, '16 GMC
Join Date: Aug 2007
Location: Iowa

iTrader: (0)

Garage List
On the list of employees you can just hover over the bottom right corner of the table and drag it down to as many employees as you want.

The Employee data validation on the Calendar view tab is a named rage (lstEmployees) so it references the entire table column by default which you can see in the Formulas > Name Manager. There is nothing additional you should need to do aside from expanding the table and filling it in.

Edit: If you want to be able to check the formulas you can click in them to see what the main functions are, colored names are named ranges (mentioned above) and the other bits are TableName[TableColumnName] which are treated as an array of values from the referenced table and column.
__________________
2007 Z4 3.0si Coupe • 6 MT • Black Saphire Metallic • PP • SP
2016 GMC Sierra SLT Z71 Premium Plus 4x4
2017 Harley StreetGlide • Denim Black • V&H Tune
2021 BMW x30i • Phytonic Blue Metallic • Fully loaded

Last edited by Hawkeye; 10-27-2017 at 08:54 AM..
Appreciate 1
      10-27-2017, 08:54 AM   #5
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
1127
Rep
4,584
Posts

 
Drives: F30 335i
Join Date: Apr 2014
Location: Down South

iTrader: (2)

Quote:
Originally Posted by Hawkeye View Post
On the list of employees you can just hover over the bottom right corner of the table and drag it down to as many employees as you want.

The Employee data validation on the Calendar view tab is a named rage (lstEmployees) so it references the entire table column by default which you can see in the Formulas > Name Manager. There is nothing additional you should need to do aside from expanding the table and filling it in.
I'm heading back to my office right now and will give that a shot. Perhaps I was thinking tooooooo far outside the box lol..! Thanks for the insight brother..!
__________________
F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 09:01 AM   #6
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
1127
Rep
4,584
Posts

 
Drives: F30 335i
Join Date: Apr 2014
Location: Down South

iTrader: (2)

Quote:
Originally Posted by Hawkeye View Post
On the list of employees you can just hover over the bottom right corner of the table and drag it down to as many employees as you want.

The Employee data validation on the Calendar view tab is a named rage (lstEmployees) so it references the entire table column by default which you can see in the Formulas > Name Manager. There is nothing additional you should need to do aside from expanding the table and filling it in.

Edit: If you want to be able to check the formulas you can click in them to see what the main functions are, colored names are named ranges (mentioned above) and the other bits are TableName[TableColumnName] which are treated as an array of values from the referenced table and column.
Perfecto!!! Works just as described, kudos brother!
__________________
F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 09:02 AM   #7
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
1127
Rep
4,584
Posts

 
Drives: F30 335i
Join Date: Apr 2014
Location: Down South

iTrader: (2)

Quote:
Originally Posted by 1and1 View Post
Easier to do it yourself and have control for future edits if needed- It's just using Data Validation.

To allow the list of names to be extended you just have to tell it the source list is longer.
1- select the name drop down cell (2C)
2- on the Data tab click on Data Validation
3- Change the Source line, increasing the last number reference
3a- Original: ='List of Employees'!$B$4:$B$8
3b- New: ='List of Employees'!$B$4:$B$108

This adds 100 more lines to the data source list- extending it down to B108, instead of just B8. As you add names to the list they will be added to the drop down. You will not just get blank entries in the drop down- it's smart enough to just show populated values.

The same change will update the Employee Leave Tracker tab automatically so you won't need to make additional edits on that tab.

Where it uses Named Ranges, for example Leave Types, the named range will expand dynamically as you add additional leave types.
Implemented this change as well, thanks man I appreciate that!

I passed Excel for Dummies but I forgot so many of the shortcuts like this haha, really appreciate you guys helping me out here!
__________________
F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 09:03 AM   #8
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
1127
Rep
4,584
Posts

 
Drives: F30 335i
Join Date: Apr 2014
Location: Down South

iTrader: (2)

Quote:
Originally Posted by amgraham View Post
Go to the "List of Employees" tab, click on Employee Names, then select the "Design" tab at the top. At the left will be "Resize Table". Expand the range to include your newly added names.
Thank you!
__________________
F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
Post Reply

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -5. The time now is 09:42 AM.




5post
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
1Addicts.com, BIMMERPOST.com, E90Post.com, F30Post.com, M3Post.com, ZPost.com, 5Post.com, 6Post.com, 7Post.com, XBimmers.com logo and trademark are properties of BIMMERPOST