variable consolidation ending tab name

[Seaq]

Hi all,

I am trying to figure out how to substitute the ending tab name used in the
sum consolidation formula, e.g. =SUM('04:03'!C46), where tab name "03" need
to be changed according to selection, INDIRECT function seems not working
here, please help! Thanks a lot,

Seaq

 

[Seaq]

Sorry this thread appeared twice as I got error posting reply from the screen

"according to selection" means I wish to input value in another cell so as
to quickly change the ending tab name

at this stage, I am replacing it manually,

Regards,

 

[Shane Devenshire]

I don't believe indirect can be used this way, I think you will need to
resort to VBA code. I presume you mean by "according to a selection" that
you have a drop down list somewhere, lets say C1. And you want the formula
to be in C2 of Sheet1.

Add the following code to the Sheet1 object in the VB editor:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, [C1])
If Not isect Is Nothing Then
[C2] = "=SUM(Sheet2:" & Target & "!A1)"
End If
End Sub



1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

 

[Seaq]

Thanks Shane.

As a VBA newbie so I pasted in the code and replaced the [c1] with my drop
down list cell, an error message replies " object required" for line Set
isect = Application.Intersect(Target, G1)? can you help?

 

[Jacob Skaria]

From workbook, right click on the sheet tab >View Code and paste the code
which Shane provided..and then try.

Remove the code from

If this post helps click Yes

 

[Seaq]

Actually I know where to paste the code to, i have modified the cell address
to suit my example as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, G1)
If Not isect Is Nothing Then
b6 = "=SUM(Sheet1:" & Target & "!A1)"
End If
End Sub

in th WB cell "G1", a drop down list (sheet2, sheet3,sheet4,sheet5) is made

in the WB cell "B6", keep it blank

and saved the WB, reopen it with macro enabled

When selecting from the drop down list, the error message goes as "object
required"

How should I solve this?

From workbook, right click on the sheet tab >View Code and paste the code
which Shane provided..and then try.

Remove the code from

If this post helps click Yes
---------------
Jacob Skaria

Thanks Shane.

As a VBA newbie so I pasted in the code and replaced the [c1] with my drop
down list cell, an error message replies " object required" for line Set
isect = Application.Intersect(Target, G1)? can you help?

 

[Jacob Skaria]

Right click the sheet tab. View Code and paste the below....Try and feedback

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("G1")) Is Nothing Then
Application.EnableEvents = False
Range("B6") = "=SUM(Sheet1:" & Target & "!A1)"
Application.EnableEvents = True
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria

Actually I know where to paste the code to, i have modified the cell address
to suit my example as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, G1)
If Not isect Is Nothing Then
b6 = "=SUM(Sheet1:" & Target & "!A1)"
End If
End Sub

in th WB cell "G1", a drop down list (sheet2, sheet3,sheet4,sheet5) is made

in the WB cell "B6", keep it blank

and saved the WB, reopen it with macro enabled

When selecting from the drop down list, the error message goes as "object
required"

How should I solve this?

From workbook, right click on the sheet tab >View Code and paste the code
which Shane provided..and then try.

Remove the code from

If this post helps click Yes
---------------
Jacob Skaria

Thanks Shane.

As a VBA newbie so I pasted in the code and replaced the [c1] with my drop
down list cell, an error message replies " object required" for line Set
isect = Application.Intersect(Target, G1)? can you help?

 

[Seaq]

Great, it works.

Thanks a lot.

BTW, in case when I deleted the B6 cell formula (on the WB which is brought
by the code automatically) by accident, it does NOT seem to reinstall again,
what shall I do then?

Regards,

Right click the sheet tab. View Code and paste the below....Try and feedback

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("G1")) Is Nothing Then
Application.EnableEvents = False
Range("B6") = "=SUM(Sheet1:" & Target & "!A1)"
Application.EnableEvents = True
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria

Actually I know where to paste the code to, i have modified the cell address
to suit my example as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, G1)
If Not isect Is Nothing Then
b6 = "=SUM(Sheet1:" & Target & "!A1)"
End If
End Sub

in th WB cell "G1", a drop down list (sheet2, sheet3,sheet4,sheet5) is made

in the WB cell "B6", keep it blank

and saved the WB, reopen it with macro enabled

When selecting from the drop down list, the error message goes as "object
required"

How should I solve this?

From workbook, right click on the sheet tab >View Code and paste the code
which Shane provided..and then try.

Remove the code from

If this post helps click Yes
---------------
Jacob Skaria


:

Thanks Shane.

As a VBA newbie so I pasted in the code and replaced the [c1] with my drop
down list cell, an error message replies " object required" for line Set
isect = Application.Intersect(Target, G1)? can you help?