BMW M3 Forum (E90 E92)

BMW Garage BMW Meets Register Search Today's Posts Mark Forums Read


Go Back   M3Post - BMW M3 Forum > BIMMERPOST Universal Forums > Off-Topic Discussions Board > Video + PC Gaming / Computing
 
Lease SwApp
Post Reply
 
Thread Tools Search this Thread
      10-27-2017, 07:48 AM   #1
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
903
Rep
4,452
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!
__________________
13' F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 09:06 AM   #2
amgraham
New Member
25
Rep
24
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, 09:07 AM   #3
1and1
Loves me some MHD!
1and1's Avatar
United_States
1021
Rep
1,060
Posts

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

iTrader: (1)

Garage List
'09 135i  [5.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 S1+ / xHP S2 / PR CP & IC /
. . . . . RSFI, Koni & Eibach / EBC Red's & Firehawk 500's
X1 - E84.N20 2013 MGM - JB4 - The Wife's
Appreciate 2
      10-27-2017, 09:42 AM   #4
Hawkeye
Brigadier General
Hawkeye's Avatar
No_Country
601
Rep
4,154
Posts

Drives: '07 Z4 si Coupe, '08 R6
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 Heated M Seats Clutch Stop Stubby
2008 R6 Raven Black GPR Steering Stabilizer Comp Werks FE Hotbodies Flush Mount Blinkers
2016 GMC Sierra SLT Z71 Premium Plus 4x4 K&N CAI Toyo G2 LT305/55R20

Last edited by Hawkeye; 10-27-2017 at 09:54 AM.
Appreciate 1
      10-27-2017, 09:54 AM   #5
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
903
Rep
4,452
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..!
__________________
13' F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 10:01 AM   #6
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
903
Rep
4,452
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!
__________________
13' F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 10:02 AM   #7
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
903
Rep
4,452
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!
__________________
13' F30 335i
Melbourne Red Metallic
M-Sport Package
Appreciate 0
      10-27-2017, 10:03 AM   #8
BeastMode335i
Maximus Decimus Meridius
BeastMode335i's Avatar
United_States
903
Rep
4,452
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!
__________________
13' 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 -4. The time now is 08:45 AM.




m3post
Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
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