Mirror both ways

[Ben Clegg]

Hi
Can someone help?

I have an excel workbook with 2 sheets (SH1 & SH2). The sheets are password protected so SH2 cannot see the information in SH1


In SH1 I have data in several rows
SH2 I have mirrored only certain rows (to hide certain information)
What I need to do is add a column in SH2 where information can be input and reflect into Sh1

I have tried mirroring in both directions and this seems to sort of work, the problem I am getting is if I sort a column in SH2 (which is mirrored to SH1)when I go back to look at SH1 the column mirrored from SH2 is now out of sync ?

In other words if I filter a column is messes up the information.

Very grateful of any help here
Cheers
Ben

 

[NiMip]

Does each data row have a unique value or anything you could use to identify that specific data row? I'm assuming you're directly referencing cells to create the mirroring, and that probably works fine in one direction. But if you have a unique identifier it might be better to use a VLOOKUP or combination of INDEX and MATCH mirroring back again. Does that make sense?

 

[Ben Clegg]

It does have unique numbers as it's actually a stock sheet. Vlookup. Unfortunately I've not used that before? although I'm willing to learn.
What would I need yoi do?

 

[NiMip]

Ah no worries. I've built a pretty simple scenario here, hopefully you can change it to suit your database. Basically a VLOOKUP searches through a range to find the value you're looking for, and then returns the corresponding value from a specified number of columns to the right.

Alternatively, if the data you want is actually to the left, you can you INDEX and MATCH to return the position of a value in one column, and tell you what the value is at that position in another column.

[TABLE="class: grid, width: 1093"]
<colgroup><col span="3"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Unique ID[/TD]
[TD]Data Value[/TD]
[TD]Mirrored Added Data[/TD]
[TD]Or Another Option[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]=VLOOKUP(B3,'Sheet 2'!$B$3:$D$17,3,FALSE)[/TD]
[TD]=INDEX('Sheet 2'!$D$3:$D$17,MATCH(B3,'Sheet 2'!$B$3:$B$17,0))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 533"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Sheet 2[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Mirrored Unique ID[/TD]
[TD]Mirrored Data Value[/TD]
[TD]Added Data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Jerry[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Jack[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Jack[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Jeff[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]Jerry[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Joe[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]Jack[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Jerry[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]Jeff[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Joe[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]Jeff[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Ben Clegg]

NiMip
Thanks for the reply , I think I understand this and will give it a try .
Thanks again for your help here , much appreciated

Ben

 

[NiMip]

They take a little bit of getting your head around, but once you're familiar with them they're great formulas to have in your arsenal! You're welcome and I hope you can translate it to your spreadsheet easily enough.

 

[Ben Clegg]

Hello NiMip
I tried the Vlookup and it works, however Im now not able to add rows on sheet 1, that reflect on sheet 2.

Can you help
The link to the actual file is here

https://1drv.ms/x/s!ApQATITlZS7QsvQk2dtXHZkex-1l5g



Ben

 

[NiMip]

Do you mean you'd like to add extra rows to the data set and they're not popping up on the other sheet? You'll have to fill the formulas down another row every time you add new data.