|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
Thank you!
|
|
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 |
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 |
matching names in column A with names in column C | bob999999 | Excel | 1 | 04-28-2013 11:21 AM |