Data from Master to Slave sheet excluding blank cells

[SybrenV]

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

 

[Toadstool]

Hi SybrenV,
Your Google Drive is restricted.

 

[SybrenV]

Hi SybrenV,
Your Google Drive is restricted.

Thanks for the heads up, should be sorted!

Sybren

 

[Toadstool]

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
	A	B	C	D	E	F	G	H	I	J
2				week 30 - Schedule
3				MA	DI	WOE	DO	VRIJ	ZAT	ZON
4				27/07	28/07	29/07	30/07	31/07	1/08	2/08
5	DVX NO°	TEAM	NAAM	1	2	3	4	5	6	7
6	10	1	Jan	Holiday	Holiday
7	50	8	Dirk	Holiday	Holiday	Holiday
8	20	3	Jos	Sick
9	30	9	Jaques	Sick			Sick
10	40	6	Jaak	Sick
Schedule

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:J10	Expression	=Holiday!D6="Sick"	text	NO
D6:J10	Expression	=Holiday!D6="Holiday"	text	NO

 

[SybrenV]

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