Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2017, 10:55 AM
cmgtlg cmgtlg is offline Adding names of users from one column to another and color change to annual cap Windows 7 32bit Adding names of users from one column to another and color change to annual cap Office 2013
Novice
Adding names of users from one column to another and color change to annual cap
 
Join Date: Dec 2014
Location: New York
Posts: 5
cmgtlg is on a distinguished road
Default Adding names of users from one column to another and color change to annual cap


I have attached a sample file of what I'm trying to accomplish and hope that you can assist. I'm using Excel 2016 and have multiple tabs indicating each month.

In the Apr 17 tab, I need the users name in Column B to also appear in Column P for each instrument used.

Also, in the Q1 tab, I'd like to list the number of users in Column C and the initials of the users in Column D.

There is a cap to the amount folks will be charged for use so I need the color to change once the user has reached the annual cap amount.

What is the best way to do these tasks?

Thank you.
Attached Files
File Type: xlsx Sample ALM_Core_2017-18 (Detail).xlsx (99.9 KB, 11 views)
Reply With Quote
  #2  
Old 04-25-2017, 10:09 PM
xor xor is offline Adding names of users from one column to another and color change to annual cap Windows 10 Adding names of users from one column to another and color change to annual cap Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

In the Apr 17 tab, I need the users name in Column B to also appear in Column P for each instrument used.

In P2: =IFERROR(INDEX($B$2:$B$200,MATCH(L2,$E$2:$E$200,0) ),"")

Also, in the Q1 tab, I'd like to list the number of users in Column C and the initials of the users in Column D.

What's wrong with the formulas you already have at Q1!C2:C8 or is it something else you mean?

What do you want at Q1!D2:d8?
Reply With Quote
  #3  
Old 04-27-2017, 08:16 AM
cmgtlg cmgtlg is offline Adding names of users from one column to another and color change to annual cap Windows 7 32bit Adding names of users from one column to another and color change to annual cap Office 2013
Novice
Adding names of users from one column to another and color change to annual cap
 
Join Date: Dec 2014
Location: New York
Posts: 5
cmgtlg is on a distinguished road
Default

In the Q1 tab, in column C2:C8, I'd like to total the number of users for April, May and June. BUT, if the user is the same for all three months, I'd like it to enter "1" rather than 3.

In column D28, I'd like the initials of the users for the April, May and June to automatically fill from the monthly tabs.

In cells D1113, I'd like the totals for each item to autocalculate. If the cap is reached for any of the items, I'd like the amount to appear in red or highlight in a different color so I can send stop billing when the cap is reached.

Hopefully this makes sense to you. Thank you!
Reply With Quote
  #4  
Old 04-27-2017, 09:31 AM
xor xor is offline Adding names of users from one column to another and color change to annual cap Windows 10 Adding names of users from one column to another and color change to annual cap Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Please try to upload a file with so much data that it is possible to make some testing. Provide some data in May and June sheet and fill manually expected results in appropriate cells.
Just two users in just one sheet is very modest in order to get an exact idea of what you want.
Reply With Quote
  #5  
Old 04-27-2017, 12:26 PM
cmgtlg cmgtlg is offline Adding names of users from one column to another and color change to annual cap Windows 7 32bit Adding names of users from one column to another and color change to annual cap Office 2013
Novice
Adding names of users from one column to another and color change to annual cap
 
Join Date: Dec 2014
Location: New York
Posts: 5
cmgtlg is on a distinguished road
Default

Sample ALM_Core_2017-18 (Detail).xlsxPlease see attached updated sheets.

I've also noticed that when typing the user name in Column B, some of the items in Column C are not coming through correctly.

So sorry for all of this, it seems the more I try to get what I want, something else happens.

Thank you!

Last edited by cmgtlg; 04-27-2017 at 12:28 PM. Reason: added attachment
Reply With Quote
  #6  
Old 04-27-2017, 10:22 PM
xor xor is offline Adding names of users from one column to another and color change to annual cap Windows 10 Adding names of users from one column to another and color change to annual cap Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

In sheet Apr 17 I have shown a possible formula in column C. Note that Supervisor and user are defined names (Press Formulas on the Ribbon, then Name Manager). I have also added an IFERROR to your formulas in columns F and J. You can delete the IFERROR construction again if you want to see the error messages. If you like what I have done you can make something similar in sheets May 17 and Jun 17.

I hope Q1 now shows what you expected.
Attached Files
File Type: xlsx ALM_Core_2017-18.xlsx (96.3 KB, 11 views)
Reply With Quote
  #7  
Old 04-28-2017, 05:46 AM
cmgtlg cmgtlg is offline Adding names of users from one column to another and color change to annual cap Windows 7 32bit Adding names of users from one column to another and color change to annual cap Office 2013
Novice
Adding names of users from one column to another and color change to annual cap
 
Join Date: Dec 2014
Location: New York
Posts: 5
cmgtlg is on a distinguished road
Default

Thank you!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Read multiple xml files in excel - Column names and Tag - Column alignment sumshen Excel Programming 0 04-17-2017 12:55 AM
Adding names of users from one column to another and color change to annual cap Tool to know color names - powerpoint Mac - Accessibility zaddig PowerPoint 1 11-05-2016 03:12 AM
Color Coding Users in Shared Calendar reyrey1332 Outlook 0 08-26-2014 07:03 AM
Adding names to sheet for printing WV8VFD Excel 4 12-01-2013 11:02 PM
Adding names of users from one column to another and color change to annual cap matching names in column A with names in column C bob999999 Excel 1 04-28-2013 11:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft