Link entries between sheets

Francois Bekker

New Member
Joined
May 28, 2015
Messages
7
I have 2 sheets in Excel. The one sheet contains monitoring data which is identified by a trap no. There are several entries for each trap. Therefore the trap number is repeated. My problem is that there are inconsistencies in the trap number which can only be identified manually. My idea is to have a lists of the traps in a second sheet, then remove all duplicate trap numbers, to see only a list of all the unique trap numbers. In this way the inconsistencies will be much easier to spot.

What I want excel to do is when I fix a trap number in sheet 2 it must fix all the entries of that specific trap in sheet 1 automatically.

Is this possible?


Extract from Sheet 1: the column "Area_Plaas_Boord_Trap" is the trap name which was combined using the following formula "=(c2&"-"&d2&"-"&f2&"-"&g2)


A B C D E F G H I J K L M N
YearWeekAreaPlaasVrugsoortBoord/verwysingTrap no.SMWMWWNVB.INVFCMArea_Plaas_Boord_Trap
20116GrabouwBosbou Huise30790n/an/aGrabouw--Bosbou Huise-3
20119GrabouwBokkie Huis10210170n/an/aGrabouw--Bokkie Huis-10
201111GrabouwBokkie Huis1005120n/an/aGrabouw--Bokkie Huis-10
201112GrabouwBokkie Huis100000n/an/aGrabouw--Bokkie Huis-10
20157GrabouwPL/RGBD402B00000n/aGrabouw--BD-402B
200941VilliersdorpA. McDonaldKantoor0000n/an/aVilliersdorp-A. McDonald-Kantoor-
200943VilliersdorpA. McDonaldKantoor0000n/an/aVilliersdorp-A. McDonald-Kantoor-
200944VilliersdorpA. McDonaldKantoor0000n/an/aVilliersdorp-A. McDonald-Kantoor-
200945VilliersdorpA. McDonaldKantoor0000n/an/aVilliersdorp-A. McDonald-Kantoor-

<colgroup><col><col span="2"><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>


Example of sheet 2 which contains only the unique trap names (duplicates removed).

Area_Plaas_Boord_Trap
Grabouw--Bosbou Huise-3
Grabouw--Bokkie Huis-10
Grabouw--BD-402B
Villiersdorp-A. McDonald Kantoor-

<colgroup><col></colgroup><tbody>
</tbody>


What I want to do is the following.

When I change a trap name in sheet to it must change all the entries of that trap name in sheet 1.

For example I change the trap name "Villiersdorp-A. McDonald Kantoor-" in sheet 2 to the following: "Villiersdorp-A. McDonald Kantoor-555"

All the "Villiersdorp-A. McDonald Kantoor-" traps in sheet 1 should change to "Villiersdorp-A. McDonald Kantoor-555".

Seeing that column N in sheet 1 is produced by column C, D, F and G it would be great if these columns can also change when the fix is done in sheet 2.

Thank you in advance.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

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