Creating absolute references including active sheet name in the formula

[Maria J-son]

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

 

[Bob Phillips]

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

perhaps

 

[Maria J-son]

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

 

[Bob Phillips]

Can you give me an example of what you mean?

 

[Maria J-son]

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