Changing a cell Contents based on other cells

Jaypee666

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
 

Attachments

  • Excel-Query.JPG
    Excel-Query.JPG
    57.3 KB · Views: 4

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows
Hi JayPee666,

Here's my Sheet1 data

Book2
ABCDE
1SITE NAME
2Physical PC/Label NameMain UserRemote Teamviewer IdAnydesk IDPC Serial Number
3PC01User01AD8821XZ001-776
4PC02User021736254
5PC03User031773433AD8874
6PC04User04
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
ABCDEF
1TeamviewerAnydeskRename PCCreate Default User Account
2PC01User01NY
3PC02User02YN
4PC03User03YY
5PC04User04NN
6    
7    
Sheet2
Cell Formulas
RangeFormula
A2:B7A2=IF(Sheet1!A3="","",Sheet1!A3)
C2:D7C2=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:F999Cell Value="N"textNO
C2:F999Cell Value="Y"textNO
 
Solution

Jaypee666

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
@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

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I'm sorry but I don't understand.

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

JayPee666-v2.xlsx
ABCDE
1SITE NAME
2Physical PC/Label NameMain UserRemote Teamviewer IdAnydesk IDBuilding
3PC01User01AD8821Main
4PC02User021736254Hut2
5PC03User031773433AD8874Hut3
6PC04User04Main
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
ABCDEF
1TeamviewerAnydeskRename PCCreate Default User Account
2PC01User01NY
3PC02User02YN
4PC03User03YY
5PC04User04NN
Sheet2
Cell Formulas
RangeFormula
A2:B5A2=IF(Sheet1!A3="","",Sheet1!A3)
C2:D5C2=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))
 

Jaypee666

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Ok, let me try and dilute the mud above.

Sheet 1)
PC_Installs.xlsx
ABCDE
1SITE NAME
2Physical PC Label\NameMain UserRemote Teamviewer IDAnyDesk IDPC Location
3PC01User012121Building A
4PC02User0222565Building A
5PC03User03552Building B
6PC04User04SPARE
Sheet1
Cell Formulas
RangeFormula
E3:E6E3=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
ABCDEF
1TeamviewerAnyDeskCreate Default User AccountLocation
2PC01User01YNNBuilding A
3PC02User02YYNBuilding A
4PC03User03NYNBuilding B
5PC04User04NNN
Sheet2
Cell Formulas
RangeFormula
B2:B3,A4:B5B2=IF(Sheet1!B3="","",Sheet1!B3)
C2:D5C2=IF($A2="","",IF(INDEX(Sheet1!C$3:C$999,MATCH($A2,Sheet1!$A$3:$A$999,0))<>"","Y","N"))
E2:E5E2=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

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,391
Messages
5,724,075
Members
422,535
Latest member
navjeet

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