Selected Sheet will update cell.
 

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.

Selected Sheet will update cell.
 

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)
--
Gary''s Student - gsnu200755

Selected Sheet will update cell.
 

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.

"Gary''s Student" wrote:

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)
--
Gary''s Student - gsnu200755

Selected Sheet will update cell.
 

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!!

" wrote:

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.

"Gary''s Student" wrote:

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)
--
Gary''s Student - gsnu200755