Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
I have a long formula that I have applied in multiple cell once it is in the cell I need this formula to be absolute. Is there an easy way or do i have to go into each cell and add the $?? =IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70=" Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes" ,'1'!$AP70="yes",'1'!$AT70="express"),1,"")) -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=551116 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
Try these. Ignores cells without formulas.
Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelative) End If Next End Sub Gord Dibben MS Excel MVP On Mon, 12 Jun 2006 12:31:06 -0500, lostinformulas <lostinformulas.29av8b_1150133705.7209@excelforu m-nospam.com wrote: I have a long formula that I have applied in multiple cell once it is in the cell I need this formula to be absolute. Is there an easy way or do i have to go into each cell and add the $?? =IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70= "Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes ",'1'!$AP70="yes",'1'!$AT70="express"),1,"")) Gord Dibben MS Excel MVP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
Gord Dibben, I haven't really ever use VB. Can you give me more details how to apply the code that you supplied. I know to go into VB Editor and sellect the worksheet that I want to Apply this to then I pasted the code that you supplied. What else Do I need to do? And how will I be able to see if it worked? Will the formula actually show up with the absolute refence or will it be behind the scene? -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=551116 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
I was able to run the Macro it work in some of the cells however others received and error message of #VALUE. The difference between the formulas that work and the one that didn't The code worked in the cells that had the following formula: =IF(AND('1'!$AP64="yes",'1'!$A64="yes"),1,"") The cells that didn't work had the following formula: =IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64=" HB",'1'!$D64="yes"),0.5,IF(AND('1'!$B64="yes",'1'! $AO64="yes",'1'!$AQ64="HB"),1,"")) Could it have something to do with the second If statement in the formula???? -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=551116 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... There are 4 macros in my original posting. Each will change references in a particular manner. First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macros by going to ToolMacroMacros. You can also assign these macros to a button or a shortcut key combo Gord Dibben MS Excel MVP On Mon, 12 Jun 2006 13:22:26 -0500, lostinformulas <lostinformulas.29axjp_1150136703.9567@excelforu m-nospam.com wrote: Gord Dibben, I haven't really ever use VB. Can you give me more details how to apply the code that you supplied. I know to go into VB Editor and sellect the worksheet that I want to Apply this to then I pasted the code that you supplied. What else Do I need to do? And how will I be able to see if it worked? Will the formula actually show up with the absolute refence or will it be behind the scene? Gord Dibben MS Excel MVP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
Changing cell references from relative to absolute should not give you the
#VALUE error. Gord On Mon, 12 Jun 2006 14:14:59 -0500, lostinformulas <lostinformulas.29azv0_1150139704.9181@excelforu m-nospam.com wrote: I was able to run the Macro it work in some of the cells however others received and error message of #VALUE. The difference between the formulas that work and the one that didn't The code worked in the cells that had the following formula: =IF(AND('1'!$AP64="yes",'1'!$A64="yes"),1,"") The cells that didn't work had the following formula: =IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64= "HB",'1'!$D64="yes"),0.5,IF(AND('1'!$B64="yes",'1' !$AO64="yes",'1'!$AQ64="HB"),1,"")) Could it have something to do with the second If statement in the formula???? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply Absolute Reference to multiplie cells
Microsoft should make the Relative and Absolute subroutines standard. I have
to generate sheets using a mix of absolute and relative references all the time, only to have to turn around and wish that they were all relative so I can have their functions apply to other data. This is a heck of a useful thread. "lostinformulas" wrote: I have a long formula that I have applied in multiple cell once it is in the cell I need this formula to be absolute. Is there an easy way or do i have to go into each cell and add the $?? =IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70=" Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes" ,'1'!$AP70="yes",'1'!$AT70="express"),1,"")) -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=551116 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change scales from reference cells value for a chart in a separate sheet | Charts and Charting in Excel | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions | |||
Reference & update destin. cells on 1st machine from source workbook on 2nd machine. | Excel Discussion (Misc queries) | |||
Compare two cells from reference cells | Excel Worksheet Functions | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) |