Changing sheet references in formulas

Greetings! Is it possible to change all instances of a page name from 1A! to
2A! in all formulas on a page with a macro? Or is this just silly?

TIA,

Robbyn

You don't have to.
If you change the sheetname, all formulas (in the same workbook) will be
adjusted automatically.

--
Kind regards,

Niek Otten

"Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> Greetings! Is it possible to change all instances of a page name from 1A!
> to
> 2A! in all formulas on a page with a macro? Or is this just silly?
>
> TIA,
>
> Robbyn

Unless it is used in an INDIRECT() function

--
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:OcYAjpMBGHA.2668@TK2MSFTNGP14.phx.gbl...
> You don't have to.
> If you change the sheetname, all formulas (in the same workbook) will be
> adjusted automatically.
>
> --
> Kind regards,
>
> Niek Otten
>
> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
>> Greetings! Is it possible to change all instances of a page name from
>> 1A! to
>> 2A! in all formulas on a page with a macro? Or is this just silly?
>>
>> TIA,
>>
>> Robbyn
>
>

Try

ActiveSheet.UsedRange.Replace What:="'1A'!",
Replacement:="'2A'!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> Greetings! Is it possible to change all instances of a page
> name from 1A! to
> 2A! in all formulas on a page with a macro? Or is this just
> silly?
>
> TIA,
>
> Robbyn

Actually it is an indirect() function. Grin.

"Niek Otten" wrote:

> Unless it is used in an INDIRECT() function
>
> --
> Kind regards,
>
> Niek Otten
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:OcYAjpMBGHA.2668@TK2MSFTNGP14.phx.gbl...
> > You don't have to.
> > If you change the sheetname, all formulas (in the same workbook) will be
> > adjusted automatically.
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> > news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> >> Greetings! Is it possible to change all instances of a page name from
> >> 1A! to
> >> 2A! in all formulas on a page with a macro? Or is this just silly?
> >>
> >> TIA,
> >>
> >> Robbyn
> >
> >
>
>
>

Now how would a formula be able to recognize that some text is a sheet name?

BTW how is it used? As a literal in the formula or is the name in a cell as
text?

If there is something that identifies the text (or part of the formula)
exclusively as it being meant as an address, you can use Find/Replace or
Chip's tip

--
Kind regards,

Niek Otten

"Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
news:B2412B43-5047-46D1-B2B9-5FEEF3FF6A44@microsoft.com...
> Actually it is an indirect() function. Grin.
>
> "Niek Otten" wrote:
>
>> Unless it is used in an INDIRECT() function
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> news:OcYAjpMBGHA.2668@TK2MSFTNGP14.phx.gbl...
>> > You don't have to.
>> > If you change the sheetname, all formulas (in the same workbook) will
>> > be
>> > adjusted automatically.
>> >
>> > --
>> > Kind regards,
>> >
>> > Niek Otten
>> >
>> > "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
>> > news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
>> >> Greetings! Is it possible to change all instances of a page name from
>> >> 1A! to
>> >> 2A! in all formulas on a page with a macro? Or is this just silly?
>> >>
>> >> TIA,
>> >>
>> >> Robbyn
>> >
>> >
>>
>>
>>

Niek,

I copy grades from one sheet which calculates averages, etc. to another
which is formatted to print onto district-required roll sheets. Rows are
constantly being added and changed in the source sheet. So, I use the
indirect function to avoid REF problems in the second sheet.

=IF(INDIRECT("1A!H"&ROW()+11)=0,"",INDIRECT("1A!H"&ROW()+11))

I'm not sure if this is the most efficient way, but it works. I need to
copy and destination fill the above formula onto more sheets and I'm trying
to avoid manual labor.

"Niek Otten" wrote:

> Now how would a formula be able to recognize that some text is a sheet name?
>
> BTW how is it used? As a literal in the formula or is the name in a cell as
> text?
>
> If there is something that identifies the text (or part of the formula)
> exclusively as it being meant as an address, you can use Find/Replace or
> Chip's tip
>
> --
> Kind regards,
>
> Niek Otten
>
> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> news:B2412B43-5047-46D1-B2B9-5FEEF3FF6A44@microsoft.com...
> > Actually it is an indirect() function. Grin.
> >
> > "Niek Otten" wrote:
> >
> >> Unless it is used in an INDIRECT() function
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> >> news:OcYAjpMBGHA.2668@TK2MSFTNGP14.phx.gbl...
> >> > You don't have to.
> >> > If you change the sheetname, all formulas (in the same workbook) will
> >> > be
> >> > adjusted automatically.
> >> >
> >> > --
> >> > Kind regards,
> >> >
> >> > Niek Otten
> >> >
> >> > "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> >> > news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> >> >> Greetings! Is it possible to change all instances of a page name from
> >> >> 1A! to
> >> >> 2A! in all formulas on a page with a macro? Or is this just silly?
> >> >>
> >> >> TIA,
> >> >>
> >> >> Robbyn
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Chip,

Thanks for the reply. I gave it a shot and I'm getting Named Argument Not
Found error. My formula looks something like

=IF(INDIRECT("1A!H"&ROW()+11)=0,"",INDIRECT("1A!H"&ROW()+11))

with the column increasing in increments if that makes sense.


"Chip Pearson" wrote:

> Try
>
> ActiveSheet.UsedRange.Replace What:="'1A'!",
> Replacement:="'2A'!", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False, _
> ReplaceFormat:=False
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> > Greetings! Is it possible to change all instances of a page
> > name from 1A! to
> > 2A! in all formulas on a page with a macro? Or is this just
> > silly?
> >
> > TIA,
> >
> > Robbyn
>
>
>

You're probably using an older version of Excel than I am, and
that version has fewer arguments to Replace that the latest
version. Try


ActiveSheet.UsedRange.Replace What:="1A!", _
Replacement:="2A!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
news:13098E66-D25F-49A9-AD94-2A422C813D99@microsoft.com...
> Chip,
>
> Thanks for the reply. I gave it a shot and I'm getting Named
> Argument Not
> Found error. My formula looks something like
>
> =IF(INDIRECT("1A!H"&ROW()+11)=0,"",INDIRECT("1A!H"&ROW()+11))
>
> with the column increasing in increments if that makes sense.
>
>
> "Chip Pearson" wrote:
>
>> Try
>>
>> ActiveSheet.UsedRange.Replace What:="'1A'!",
>> Replacement:="'2A'!", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False,
>> SearchFormat:=False, _
>> ReplaceFormat:=False
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
>> news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
>> > Greetings! Is it possible to change all instances of a page
>> > name from 1A! to
>> > 2A! in all formulas on a page with a macro? Or is this just
>> > silly?
>> >
>> > TIA,
>> >
>> > Robbyn
>>
>>
>>

That did the trick! Thank you much!

"Chip Pearson" wrote:

> You're probably using an older version of Excel than I am, and
> that version has fewer arguments to Replace that the latest
> version. Try
>
>
> ActiveSheet.UsedRange.Replace What:="1A!", _
> Replacement:="2A!", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> news:13098E66-D25F-49A9-AD94-2A422C813D99@microsoft.com...
> > Chip,
> >
> > Thanks for the reply. I gave it a shot and I'm getting Named
> > Argument Not
> > Found error. My formula looks something like
> >
> > =IF(INDIRECT("1A!H"&ROW()+11)=0,"",INDIRECT("1A!H"&ROW()+11))
> >
> > with the column increasing in increments if that makes sense.
> >
> >
> > "Chip Pearson" wrote:
> >
> >> Try
> >>
> >> ActiveSheet.UsedRange.Replace What:="'1A'!",
> >> Replacement:="'2A'!", LookAt:=xlPart, _
> >> SearchOrder:=xlByRows, MatchCase:=False,
> >> SearchFormat:=False, _
> >> ReplaceFormat:=False
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> >> news:55E807BE-9B42-4318-9CB6-83C61287FFCF@microsoft.com...
> >> > Greetings! Is it possible to change all instances of a page
> >> > name from 1A! to
> >> > 2A! in all formulas on a page with a macro? Or is this just
> >> > silly?
> >> >
> >> > TIA,
> >> >
> >> > Robbyn
> >>
> >>
> >>
>
>
>