Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lostinformulas
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lostinformulas
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lostinformulas
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change scales from reference cells value for a chart in a separate sheet ers Charts and Charting in Excel 4 April 2nd 06 07:00 PM
How to chnge 35 relative cells to 35 absolute cells at one time. Susan A at Millennium Medical Excel Worksheet Functions 3 March 7th 06 09:12 PM
Reference & update destin. cells on 1st machine from source workbook on 2nd machine. [email protected] Excel Discussion (Misc queries) 6 February 28th 06 06:15 AM
Compare two cells from reference cells Mike K Excel Worksheet Functions 2 November 26th 05 03:07 PM
changing multiple cells from relative to absolute reference Mike Excel Discussion (Misc queries) 4 March 10th 05 03:11 PM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"