Excel overlapping day of week and times
Hi there, Where I am currently working we have spreadsheets listing course lectures and seminars. As some courses/seminars cannot conflict with others, I need to find a way to identify which courses/seminars conflict. A sample spreadsheet is below. The day and time cannot conflict with another day/time that is the same or has overlapping times. I need a column at the end to identify all the exact and overlapping courses and seminars, not just by the times but by the day as well. I have been able to come up with a calculation to find overlapping times and another to find the same days/times however I am lost at how to create one calculation to do both. I am using Office 16. Subj Num DU CType Sec Days Time LING 1F25 1 LEC 1 M 1500-1700 LING 1F25 1 SEM 1 1 T 1600-1700 LING 1F25 1 SEM 2 1 W 1500-1600 LING 1F25 1 SEM 3 1 F 1600-1700 LING 1F25 1 SEM 4 1 F 1000-1100 LING 1F25 1 SEM 5 1 F 900 -1000 LING 1F25 1 SEM 6 1 W 1600-1700 LING 1F25 1 SEM 7 1 R 1400-1500 LING 5P01 3 LEC 1 R 1100-1400 LING 5P02 3 LEC 1 T 1100-1400 LING 5P03 2 LEC 1 T 1200-1500 LING 5P04 3 LEC 1 W 1100-1400 LING 5P05 2 LEC 1 M 1400-1700 LING 5P07 2 LEC 1 R 1400-1500 LING 5P08 3 LEC 1 M 1400-1700 LING 5P85 2 LEC 1 W 1100-1400 Any help would be greatly appreciated. Arlene 1070
Conditional Formatting - how to minimize formatting rule duplication with editing
Hi all, I built a spreadsheet for management to track project deliverables, and am running into two issues. I attached a slimmed down version of the spreadsheet for review. 1. The file contains several conditional formatting rules. When I turn it over for use, the end user has to insert rows in the table at times as new projects come in. I think this is causing the conditional formatting rules to explode. As a result, I have lots of rules that pertain to only one or a few cells, instead of the entire range as initially created. Is there a way to prevent this from happening? 2. The Projected columns contain rather complicated nested formulas for projecting due dates. I've asked the end user to NOT manually enter values into these columns, as it erases my formula, but with little luck. Is there a way to protect the integrity of these cells while allowing the end user to still add/remove rows from the table? Any guidance on these issues is greatly appreciated!! Thanks, CaseyDate Formatting
I wonder if anyone could help me please? Its probably really simple but I can't for the life of me get Excel to do as I would like. On all our company graphs/pivots etc, date is displayed as 18/06 (yy/mm) I have tried using a custom format but it always defaults to a full date 01/06/2018 in the display bar even though displaying correctly in the data It causes havoc for my pivots. How can I have the column format my dates as YY/MM to display in the data and in the display bar? Please? Thank you ClairImport Hyperlinked Text into a Data Validation Drop Down Menu
I am interested in creating a Data Validation drop down menu that contains report names (text) with an embedded hyperlink. The way I'm doing this now is creating a table on another sheet, listing all the reports (Report 1, Report 2, etc) within that table, and then embedding hyperlinks on all those report names. Then, on another sheet, I'm creating the drop down menu through Data Validation that sources those report names. Right now, the drop down menu appears, and I can choose the reports, but the embedded hyperlinks don't carry over. Is there a way to do this? I've found some answers online that suggest workarounds using HYPERLINK pointers, but this isn't what I want to do. Any help appreciated, thanks!Unable to open .xls that have protected sheets or protect workbook structure
Hi, Since last week, I am no longer able to open .xls files that have protected sheets or workbook structure. Before that everything was working well. I can open those excel files on other computers with the same version of excel. Excel 2016 Version 1901 (build 11231.20130) I have try the following things: - quick Repair - Online Repair - Ignore other application that use Dynamic Data Exchange (DDE) - removed all add-ins and Com - enable Macro - remove the 3 check box of the Protected View screen - Remove the file block settings - Uninstall office completely from the computer with the Microsoft fix-it program and did a fresh re-install - Deleted the user profile from the computer and even try with a different user. - can open other .xls file that does not contain protect functionality in it. - I tried to open the file from the network or locally on my computer. When I open the problematic workbook, it freeze completely with CPU at 30% and memory at 3.2 gig and go up to 3.6 gig before closing. I have 16 gig of ram on my computer. If I open Excel then I open the file, I get a grey background with the menu and formula bar. the I click in the grey area and I get a message saying "Not enough system resources to display completely" Anybody as seen that and know the fix? Thanks
Excel 2016 Conditional Formatting - Multiple Tabs
I have a spreadsheet that has over 100 tabs on it. The tab I want to format is our Balances tab, which shows which invoices have not yet been paid (high level view of what's outstanding). This Balances tab just shows the dollar amounts outstanding by client, not any dates or anything. The dollar amounts are based on the invoicing tab less the payment tab (two other tabs, so a formula) and those tabs are based on the actual fields from each individual tab from the workbook. I am hoping that within each of the 100+ tabs that we have per client, the Balances tab cells can change color for the dollar amounts that are past due (RED) or are upcoming in the next week (BLUE). Still with me? If so, the 100+ tabs have the details of each invoice, including columns with due date, amount due, payment received date, payment amount (to name a few). Does anyone know how to do conditional formatting in a summary tab, which has to look back across numerous sheets in one workbook?
