Selected Sheet will update cell.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

I need help to write a code that can do this:

Sheet 1
Sheet 2
Sheet 3 - Central

IF sheet 1 is selected, then Sheet 3!A1 will equal to Sheet1!G1.
IF sheet 2 is selected, then Sheet 3!A1 will change and equal to Sheet2! H1.

.... and the process will continue, sheet 1 or sheet 2.

Thanks.
 
On Sheet3, let us use cell A2 as the reference. A2 will be set to either
Sheet1 or Sheet2 via typing or data validation.


Then in A1:
=IF(A2="Sheet1",Sheet1!G1,Sheet2!H1)
 
Dear Gary's Student,

That won't work, too manual I guess.
A VBA code will be much better.

Sheet 3 will be hidden and the other 2 sheets will be visible. Thus when I
select either page 1 or page 2, sheet 3 (designated cells) will change
according to the sheet selected.

I have tried recording using the macro recorder, but somehow it is not that
ideal.

Please help!!! Thanks.
 
This is the code for sheet 1, the code for sheet 2 is similar with a little
adjustment.

Sub Sch1()
On Error Resume Next
Sheets("Sch-1").Visible = True
Sheets("Sch-1").Activate
Sheets("SCH-Ctrl").Visible = True
Sheets("SCH-Ctrl").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = "='Sch-1'!RC[6]"
Sheets("SCH-Ctrl").Visible = False
Sheets("Sch-1").Activate
Range("G1").Activate


Sheets("Key In").Visible = False
Sheets("Sch-2").Visible = FALSE

End Sub


I don't wanna use this but instead, both sheet 1 & 2 is visible, ready to be
selected and SCH-Ctrl (sheet 3) will change according to the selection or
either sheet 1 or sheet 2.

Thanks!!
 
Back
Top