| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating absolute references including active sheet name in the formula
=INDIRECT("Sheet1!$A$4")
perhaps -- HTH Bob Phillips "Maria J-son" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating absolute references including active sheet name in the formula
Hi Bob,
Do you know if there is a similar function in vba for ranges, address etc? Regards "Bob Phillips" skrev i meddelandet ... =INDIRECT("Sheet1!$A$4") perhaps -- HTH Bob Phillips "Maria J-son" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating absolute references including active sheet name in the formula
Can you give me an example of what you mean?
-- HTH Bob Phillips "Maria J-son" wrote in message ... Hi Bob, Do you know if there is a similar function in vba for ranges, address etc? Regards "Bob Phillips" skrev i meddelandet ... =INDIRECT("Sheet1!$A$4") perhaps -- HTH Bob Phillips "Maria J-son" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating absolute references including active sheet name in the formula
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" skrev i meddelandet ... Can you give me an example of what you mean? -- HTH Bob Phillips "Maria J-son" wrote in message ... Hi Bob, Do you know if there is a similar function in vba for ranges, address etc? Regards "Bob Phillips" skrev i meddelandet ... =INDIRECT("Sheet1!$A$4") perhaps -- HTH Bob Phillips "Maria J-son" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I write a formula with absolute cell references | New Users to Excel | |||
| Convert selected formula references from relative to absolute | Excel Discussion (Misc queries) | |||
| Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
| Creating sheet references | Excel Worksheet Functions | |||
| Absolute References in cell formula | Excel Worksheet Functions | |||
Linear Mode
