| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing References to Formulas | Excel Discussion (Misc queries) | |||
| copying formulas and changing cell references | Excel Discussion (Misc queries) | |||
| Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
| Copy formulas exact without references changing | Excel Programming | |||
| Changing Cell References in Formulas | Excel Worksheet Functions | |||
Linear Mode
