Difficulty with =MATCH, conditional formatting and copy/pasting

MattJC7

New Member
Joined
Jul 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Thanks in advance for any help. I've been working on a ward rota excel project to reduce repetitive administrative tasks in my department. So far it's worked well, as you can see there is functionality to highlight staff in red on the main rota when they're in the annual leave rota underneath. I've attached a picture of my code/example in the sheet from our in house document to help people use the sheet. E4 is the start of the main rota, E87 is the start of the annual leave section.

MrExcel pic.jpg


The roadblock with this sheet comes down to when people copy and paste (despite me asking them not to and there being a message box on opening asking not to), because it copies conditional formatting and stops it from working well - as seen in above image.

A colleague's friend was able to mitigate this by using one match conditional formatting applied to all columns as opposed to my 10 which was brilliant, and I learned a lot. However, we then had to add a notes section underneath each day, see below pic, to detail when someone is starting at say, 11am. This then broke the single rule he set up and required the columns to have 10 different conditional formatting rules due to the merged cells. We have looked at cell comments for this but we need the information available on paper, and when it's printed the notes section is the only way to do this.

MrExcel2.png


An old colleague recommended a macro to clear all conditional formatting and place it back in correctly, which is easy enough but this is dependent on others not inserting rows - and we may need to be able to do this as it's hard to define a maximum number of rows needed. I brainstormed that I could have a section where people fill in the row numbers present in the spreadsheet when they need to fix it, then a macro would use these values to select the ranges and input the correct conditional formatting that way - however I don't have the VBA skills yet to do this.

I wanted to throw this out there and see if anyone has any super efficient methods that I've completely missed, can I optimise the conditional formatting? Can I remove everyone's "ctrl" and "v" keys in the department so they can't paste?!

Thanks,
Matt (Excel noob!)
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Just starting to read your concerns... but my first leap off point is to simplify your conditional formatting rules...
Wouldnt =MATCH(E4,E$87:E126,0) work if applied to E4:N58

That might save you a lot of maintenance...

Will keep going through your question
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Instead of having intermittent notes in your document...
Why not have a NOTES column at the end of the week or between each day... that allows for better person by person notes, the portential of a Pivot table, and removes your current concern while allowing people to insert new rows.
 

MattJC7

New Member
Joined
Jul 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Instead of having intermittent notes in your document...
Why not have a NOTES column at the end of the week or between each day... that allows for better person by person notes, the portential of a Pivot table, and removes your current concern while allowing people to insert new rows.

This is a great idea, and I think would allow us to have the formula you discussed in your previous comment? The only issue I anticipate is that we print the sheet in portrait and it just about fits.
I’m fairly new to pivot tables, in this specific scenario, what benefit would we gain?
thanks for your help!
 

MattJC7

New Member
Joined
Jul 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Just starting to read your concerns... but my first leap off point is to simplify your conditional formatting rules...
Wouldnt =MATCH(E4,E$87:E126,0) work if applied to E4:N58

That might save you a lot of maintenance...

Will keep going through your question
Oh lord..... I messed up the formula. I added $ to the columns in the MATCH formula. I've been maintaining 10 rules for the past month because of $2!!!

Thank you. Life saver. This fixes almost all of my problems with this sheet.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Glad it solves your problems.

Two quick things.
1) Pivot tables... no specific need right now, but yo are setting your self up for when they are beneficial
2) Re printing Portrait... Take each of the columns with notes and "Group" them individually
Select the column... Data Tab, far right in Outline Section, Group button
Now you have a collapsible bar along the top for each notes column... Click the 1 and 2 at the far left above the spreadsheet to expand and collapse... now you can print 1 page wide but have notes accessible when needed
 

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,302
Members
410,675
Latest member
DLongmountain
Top