Data from Master to Slave sheet excluding blank cells

SybrenV

New Member
Joined
Jul 26, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Dear Forum users,

Already a few days I am looking for a way to solve my excel question. I hope someone on this forum can point me in the right direction.

At the company I work for, we have 2 excel files. One file has a calendar containing all the holidays of about 50 employees, and is filled in by a colleague of mine.
After filling in, she manually fills in the holidays in my excel file, a calendar containing a schedule of which employee is working for which customer.

I am looking for a way to combine the 2 excel files into one file, where the holiday sheet is the master version, and the scheduling sheet is the slave. When my colleague fills in the holidays in her list the corresponding cell in mine list, has to be overridden with the value 'holiday'. Whatever I fill in my schedule does not matter and should not be displayed in the holiday calendar. I should not be able to override a cell containing "holiday" in my file.

I have played around with queries and now have a functioning "master" sheet. Whatever I will input in the master will show in the Slave sheet. But I need to get it to a point that if I input data into my slave sheet, this data does not get over ridden by the blank cells of my master sheet.

I hope this makes sense, if not, I have an example that you can download from following link:
Google Drive - Schedule + holiday Excel Sheet

Thanks in advance for anyone taking the time to help me.

Regards,

Sybren
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,386
Office Version
  1. 2016
Platform
  1. Windows
Hi SybrenV,
Your Google Drive is restricted.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,386
Office Version
  1. 2016
Platform
  1. Windows
You've set read-only so I can't see how you've done the "now have a functioning master sheet" but I suspect it's a formula retrieving what's in the other sheet which gets overwritten if you overtype the formula.

A cell can either be data or a formula so you'd need some VBA to give the functionality you request.

Without VBA the best I can suggest is a Conditional Format to warn you of the Holiday or Sick status on the other sheet.

Schedule + holiday.xlsx
ABCDEFGHIJ
2week 30 - Schedule
3MADIWOEDOVRIJZATZON
427/0728/0729/0730/0731/071/082/08
5DVX NO°TEAMNAAM1234567
6101JanHolidayHoliday
7508DirkHolidayHolidayHoliday
8203JosSick
9309JaquesSickSick
10406JaakSick
Schedule
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:J10Expression=Holiday!D6="Sick"textNO
D6:J10Expression=Holiday!D6="Holiday"textNO
 

SybrenV

New Member
Joined
Jul 26, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi Toadstool,

What I have done is used the data import tool in excel. By importing the data from sheet Holiday to sheet Schedule.
The problem is when I fill in data into the sheet schedule, it gets overwritten by the blank data in the sheet Holiday. Which is logical because an empty cell is an empty cell...

I could use VBA, I really don't mind going that route, but I have no idea where to start :).
I guess If i could code following functionality: When pressed an button 'update schedule sheet' then look in table "Holiday" for cells with a value other then blank. For this cell with an value use the cell "coordinates" and copy the data to the corresponding (same coordinates) into the other sheet.

Thank you for your help,

Sybren
 

Watch MrExcel Video

Forum statistics

Threads
1,127,465
Messages
5,624,902
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top