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

Changing sheet references in formulas
 

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" wrote in message
...
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

Changing sheet references in formulas
 

Unless it is used in an INDIRECT() function

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
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

Changing sheet references in formulas
 

Actually it is an indirect() function. Grin.

"Niek Otten" wrote:

Unless it is used in an INDIRECT() function

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
Actually it is an indirect() function. Grin.

"Niek Otten" wrote:

Unless it is used in an INDIRECT() function

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
Actually it is an indirect() function. Grin.

"Niek Otten" wrote:

Unless it is used in an INDIRECT() function

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
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" wrote in message
...
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

Changing sheet references in formulas
 

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" wrote in message
...
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" wrote in message
...
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