Creating absolute references including active sheet name in the formula
 

Hi,

If you have cells with absolute formulas - how do you make them to add the
active worksheet into the formula as well?

I needed this because in code I move the formulas in the first sheet to
cells in another second sheet and still want to have the right reference
formula.

- OK, I need it to add a sheet reference to the first sheet. If one rng in
sheet1 have "=$A$4" I need it to be "Sheet1!$A$4" in sheet3.

Sometimes the rng contain a reference to another third sheet and it work
fine, we still get the reference to the right sheetname( Sheet3!A4" will be
Sheet3!$A$4" ).

BUT with this code when the reference is pointing within the first sheet
("=A4") , it will start pointing to the reference cells in the second sheet
"=$A$4"! , when it should be Sheet1!$A$4"

Can anybody help me with that? With code add the activesheet in front?


/Regards

Creating absolute references including active sheet name in the formula
 

=INDIRECT("Sheet1!$A$4")

perhaps

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi,

If you have cells with absolute formulas - how do you make them to add the
active worksheet into the formula as well?

I needed this because in code I move the formulas in the first sheet to
cells in another second sheet and still want to have the right reference
formula.

- OK, I need it to add a sheet reference to the first sheet. If one rng
in
sheet1 have "=$A$4" I need it to be "Sheet1!$A$4" in sheet3.

Sometimes the rng contain a reference to another third sheet and it work
fine, we still get the reference to the right sheetname( Sheet3!A4" will
be
Sheet3!$A$4" ).

BUT with this code when the reference is pointing within the first sheet
("=A4") , it will start pointing to the reference cells in the second
sheet
"=$A$4"! , when it should be Sheet1!$A$4"

Can anybody help me with that? With code add the activesheet in front?


/Regards

Creating absolute references including active sheet name in the formula
 

Hi Bob,
Do you know if there is a similar function in vba for ranges, address etc?
Regards


"Bob Phillips" skrev i meddelandet
...
=INDIRECT("Sheet1!$A$4")

perhaps

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi,

If you have cells with absolute formulas - how do you make them to add
the
active worksheet into the formula as well?

I needed this because in code I move the formulas in the first sheet to
cells in another second sheet and still want to have the right reference
formula.

- OK, I need it to add a sheet reference to the first sheet. If one rng
in
sheet1 have "=$A$4" I need it to be "Sheet1!$A$4" in sheet3.

Sometimes the rng contain a reference to another third sheet and it work
fine, we still get the reference to the right sheetname( Sheet3!A4" will
be
Sheet3!$A$4" ).

BUT with this code when the reference is pointing within the first sheet
("=A4") , it will start pointing to the reference cells in the second
sheet
"=$A$4"! , when it should be Sheet1!$A$4"

Can anybody help me with that? With code add the activesheet in front?


/Regards

Creating absolute references including active sheet name in the formula
 

Can you give me an example of what you mean?

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi Bob,
Do you know if there is a similar function in vba for ranges, address etc?
Regards


"Bob Phillips" skrev i meddelandet
...
=INDIRECT("Sheet1!$A$4")

perhaps

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi,

If you have cells with absolute formulas - how do you make them to add
the
active worksheet into the formula as well?

I needed this because in code I move the formulas in the first sheet
to
cells in another second sheet and still want to have the right
reference
formula.

- OK, I need it to add a sheet reference to the first sheet. If one
rng
in
sheet1 have "=$A$4" I need it to be "Sheet1!$A$4" in sheet3.

Sometimes the rng contain a reference to another third sheet and it
work
fine, we still get the reference to the right sheetname( Sheet3!A4"
will
be
Sheet3!$A$4" ).

BUT with this code when the reference is pointing within the first
sheet
("=A4") , it will start pointing to the reference cells in the second
sheet
"=$A$4"! , when it should be Sheet1!$A$4"

Can anybody help me with that? With code add the activesheet in front?


/Regards

Creating absolute references including active sheet name in the formula
 

Hi,
I started a new thread with a better explanation/examples of the problem.
I'd be grateful if you can help.
/Regards


"Bob Phillips" skrev i meddelandet
...
Can you give me an example of what you mean?

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi Bob,
Do you know if there is a similar function in vba for ranges, address
etc?
Regards


"Bob Phillips" skrev i meddelandet
...
=INDIRECT("Sheet1!$A$4")

perhaps

--
HTH

Bob Phillips

"Maria J-son" wrote in message
...
Hi,

If you have cells with absolute formulas - how do you make them to add
the
active worksheet into the formula as well?

I needed this because in code I move the formulas in the first sheet
to
cells in another second sheet and still want to have the right
reference
formula.

- OK, I need it to add a sheet reference to the first sheet. If one
rng
in
sheet1 have "=$A$4" I need it to be "Sheet1!$A$4" in sheet3.

Sometimes the rng contain a reference to another third sheet and it
work
fine, we still get the reference to the right sheetname( Sheet3!A4"
will
be
Sheet3!$A$4" ).

BUT with this code when the reference is pointing within the first
sheet
("=A4") , it will start pointing to the reference cells in the second
sheet
"=$A$4"! , when it should be Sheet1!$A$4"

Can anybody help me with that? With code add the activesheet in front?


/Regards