Changing a cell Contents based on other cells

[Jaypee666]

Hi all,

Got what I will assume is gonna be an easy enough answer to you Excel gurus, and there are probably a million examples readily available from Google but I simply can't seem to phrase my query properly, so I'll try here, bear with me.

I have a workbook with a number of sheets that track various information/progress of setting up new pc's for an organisation. Now it works perfectly fine for what it does, but I am hoping to improve on the entry by having some of the cells update once information is entered elsewhere in the Workbook.

I have attached a screen grab of 1 section that I simplified with snippets from sections of 2 worksheets that will give the visual aide to my written query.

For ease of reference Rows 1-7 would be Sheet1 and Rows 8-14 would be on Sheet2

So A3-A6 basically lists the name of the PC as entered of it's company assigned label, which is automatically updated on A10-A13, have that workinng no problem.

The red cells of B10-E13 get a "Y" entered when that particular task has been carried on the relevant pc, and change to a green for a quick visual guide for what is left to be done to the pc.

What I am looking to do is when the Teamviewer ID for example is entered in its relevant cell, that the corresponding cell on the the other sheet would update to a "Y"

Example: Teamviewer ID is entered in C3, then B10 would update from an "N" to a "Y" and if D5 has an AnyDesk address entered C12 would update to a "Y" as the software has obviously been installed.

I hope this is a bit clearer than mud and look forward to your responses.

I enjoy learning new things with Excel, so this will be a nice addition to my limited arsenal.

Thanking you all in advance

 

[Toadstool]

Hi JayPee666,

Here's my Sheet1 data

Book2
	A	B	C	D	E
1	SITE NAME
2	Physical PC/Label Name	Main User	Remote Teamviewer Id	Anydesk ID	PC Serial Number
3	PC01	User01		AD8821	XZ001-776
4	PC02	User02	1736254
5	PC03	User03	1773433	AD8874
6	PC04	User04
7
Sheet1

Here's the status check (but I don't know what the criteria are for "Rename PC" or "Create Default User Account" so I've left them blank, but I'm sure you can work it out):

Book2
	A	B	C	D	E	F
1			Teamviewer	Anydesk	Rename PC	Create Default User Account
2	PC01	User01	N	Y
3	PC02	User02	Y	N
4	PC03	User03	Y	Y
5	PC04	User04	N	N
6
7
Sheet2

Cell Formulas
Range	Formula
A2:B7	A2	=IF(Sheet1!A3="","",Sheet1!A3)
C2:D7	C2	=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:F999	Cell Value	="N"	text	NO
C2:F999	Cell Value	="Y"	text	NO

 

[Jaypee666]

@Toadstool thank you

That is exactly what I was looking for.

It works on my test/simplified sheet, now to make it work on a copy of the proper one before implementing it on the live workbook.

 

[Jaypee666]

I have one follow up query which I'm guessing is just a minor change to the code, but I can't figure it out properly.
Say for the purposes of this example, we were to change [PC Serial Number] to [Building] and I want the [Building] info from Sheet2!E2 to populate to Sheet1!E3 when that information is entered, what I have at the moment is this:
" =IF($A2="","",IF(INDEX(Sheet1!E$3:E$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"",Sheet1!$E3,"N")) " which works fine so long as all the info in cell Sheet1!A3 = Sheet2!A2, but If I were to change the info in Sheet2!A2 Sheet1 doesn't update. How do I make Sheet1!$E3 get it's info based on the info in Sheet2!$E2 for the correct entry in A2, even if that has changes. I'm sure the issue is with [ Sheet1!$E3 ] but I can't figure it out.

Any help appreciated

 

[Toadstool]

I'm sorry but I don't understand.

Here's PC Serial Number changed to Building in the data:

JayPee666-v2.xlsx
	A	B	C	D	E
1	SITE NAME
2	Physical PC/Label Name	Main User	Remote Teamviewer Id	Anydesk ID	Building
3	PC01	User01		AD8821	Main
4	PC02	User02	1736254		Hut2
5	PC03	User03	1773433	AD8874	Hut3
6	PC04	User04			Main
Sheet1

Sheet2!A2 is the first Physical PC/Label Name pulled from Sheet1 so if you change it then the other lookups won't work.
Can you paste an example of how you want it to look?

JayPee666-v2.xlsx
	A	B	C	D	E	F
1			Teamviewer	Anydesk	Rename PC	Create Default User Account
2	PC01	User01	N	Y
3	PC02	User02	Y	N
4	PC03	User03	Y	Y
5	PC04	User04	N	N
Sheet2

Cell Formulas
Range	Formula
A2:B5	A2	=IF(Sheet1!A3="","",Sheet1!A3)
C2:D5	C2	=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))

 

[Jaypee666]

Ok, let me try and dilute the mud above.

Sheet 1)

PC_Installs.xlsx
	A	B	C	D	E
1	SITE NAME
2	Physical PC Label\Name	Main User	Remote Teamviewer ID	AnyDesk ID	PC Location
3	PC01	User01	2121		Building A
4	PC02	User02	22	565	Building A
5	PC03	User03		552	Building B
6	PC04	User04			SPARE
Sheet1

Cell Formulas
Range	Formula
E3:E6	E3	=IF($A3="","",IF(INDEX(Sheet2!F$2:F$5,MATCH($A3,Sheet2!$A$2:$A$5,0))<>"",Sheet2!$F2,"SPARE"))

Sheet 2)

PC_Installs.xlsx
	A	B	C	D	E	F
1			Teamviewer	AnyDesk	Create Default User Account	Location
2	PC01	User01	Y	N	N	Building A
3	PC02	User02	Y	Y	N	Building A
4	PC03	User03	N	Y	N	Building B
5	PC04	User04	N	N	N
Sheet2

Cell Formulas
Range	Formula
B2:B3,A4:B5	B2	=IF(Sheet1!B3="","",Sheet1!B3)
C2:D5	C2	=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))
E2:E5	E2	=IF($A2="","",IF(INDEX(Sheet1!F$3:F$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))

So, as you can see, this all works as it should, but if for some reason PC01 needed to be replaced with PC04 which for this purpose is a spare then PC04 would be setup is PC01's place.

Sheet 1 is meant to be more of an at a glance, where is what PC, which ones are spare, so on Sheet 2 if PC04 were to replace PC01 then on Sheet 1, E3 would become possibly blank and E6 would say Building A

I hope this helps to clear up what I am aiming to do. I feel it should be possible, but happy to accept if not.

There is a logic to the madness, and it would be applied across a number of Columns not just the one

Thanks again in advance.

 

[Toadstool]

Ah! I see you taken out my formula on Sheet2 for "Physical PC Label\Name" to keep the sheets in step, so I guess you're going to maintain the lists manually but please remember if you change that list you'll also need to manually change the Location on Sheet2.

It would be best to keep all data entry on Sheet1 or you'll run into problems when you make changes.

 

[Jaypee666]

Actually I might keep Sheet 1 as Quick look sheet and have it pull the data needed from the other sheets.

Thanks again for your help.