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

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

perhaps

--
HTH

Bob Phillips

"Maria J-son" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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


"Bob Phillips" <[email protected]> skrev i meddelandet
news:%[email protected]...
> =INDIRECT("Sheet1!$A$4")
>
> perhaps
>
> --
> HTH
>
> Bob Phillips
>
> "Maria J-son" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Can you give me an example of what you mean?

--
HTH

Bob Phillips

"Maria J-son" <[email protected]> wrote in message
news:%[email protected]...
> Hi Bob,
> Do you know if there is a similar function in vba for ranges, address etc?
> Regards
>
>
> "Bob Phillips" <[email protected]> skrev i meddelandet
> news:%[email protected]...
> > =INDIRECT("Sheet1!$A$4")
> >
> > perhaps
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Maria J-son" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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" <[email protected]> skrev i meddelandet
news:[email protected]...
> Can you give me an example of what you mean?
>
> --
> HTH
>
> Bob Phillips
>
> "Maria J-son" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Bob,
>> Do you know if there is a similar function in vba for ranges, address
>> etc?
>> Regards
>>
>>
>> "Bob Phillips" <[email protected]> skrev i meddelandet
>> news:%[email protected]...
>> > =INDIRECT("Sheet1!$A$4")
>> >
>> > perhaps
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Maria J-son" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>