Click Entering Absolute Cell References into a Formula

When one is in the process entering a formula into an 
EXCEL spreadsheet it is possible to click on another cell 
to automatically have this cell reference placed into the 
formula. However this entry always is entered as a 
RELATIVE cell reference. Is there a way to have EXCEL 
insert an ABSOLUTE cell reference into the formula??

It seems to me that it would be so common and yet I have 
never been able to figure this one out.

Please Help
Michael Karas

0
mkaras (2)
7/18/2003 2:51:55 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
458 Views

Similar Articles

[PageSpeed] 52

Hi Michael!

Is this what you want?

Click the cell and then press F4.

F4 acts as a toggle going through the four reference options.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Michael Karas" <mkaras@CarouselDesign.com> wrote in message
news:06cf01c34cd7$8c98f560$a301280a@phx.gbl...
> When one is in the process entering a formula into an
> EXCEL spreadsheet it is possible to click on another cell
> to automatically have this cell reference placed into the
> formula. However this entry always is entered as a
> RELATIVE cell reference. Is there a way to have EXCEL
> insert an ABSOLUTE cell reference into the formula??
>
> It seems to me that it would be so common and yet I have
> never been able to figure this one out.
>
> Please Help
> Michael Karas
>


0
njharker (1646)
7/18/2003 2:59:53 AM
Just press the F4 key after clicking on the desired cell.

Rudy
>-----Original Message-----
>When one is in the process entering a formula into an 
>EXCEL spreadsheet it is possible to click on another cell 
>to automatically have this cell reference placed into the 
>formula. However this entry always is entered as a 
>RELATIVE cell reference. Is there a way to have EXCEL 
>insert an ABSOLUTE cell reference into the formula??
>
>It seems to me that it would be so common and yet I have 
>never been able to figure this one out.
>
>Please Help
>Michael Karas
>
>.
>
0
woltnerr (5)
7/18/2003 3:02:42 AM
Well....that works great. All along (for years actually) I 
knew there had to be _SOME WAY_ to do this but could never 
discover it in on-line the documentation. Instead I had 
always tried things like CTL-LfClick, CTL-ALT-LfClick etc 
etc etc all to NO avail. 

Thanks for the quick and excellent response!!!!
Michael Karas


>-----Original Message-----
>Hi Michael!
>
>Is this what you want?
>
>Click the cell and then press F4.
>
>F4 acts as a toggle going through the four reference 
options.
>
>-- 
>Regards
>Norman Harker MVP (Excel)
>Sydney, Australia
>Holidays and Observances Friday 18th July: Mexico (Day of 
Mourning
>death of Benito Juarez), Spain (Labor Day), Uruguay 
(Constitution Day)
>njharker@optusnet.com.au
>Excel and Word Function Lists (Classifications, Syntax 
and Arguments)
>available free to good homes.
>"Michael Karas" <mkaras@CarouselDesign.com> wrote in 
message
>news:06cf01c34cd7$8c98f560$a301280a@phx.gbl...
>> When one is in the process entering a formula into an
>> EXCEL spreadsheet it is possible to click on another 
cell
>> to automatically have this cell reference placed into 
the
>> formula. However this entry always is entered as a
>> RELATIVE cell reference. Is there a way to have EXCEL
>> insert an ABSOLUTE cell reference into the formula??
>>
>> It seems to me that it would be so common and yet I have
>> never been able to figure this one out.
>>
>> Please Help
>> Michael Karas
>>
>
>
>.
>
0
mkaras (2)
7/18/2003 3:39:00 AM
Hi Michael!

I did a search in Help for Excel 2002 using "absolute reference",
"relative reference" and "reference" as search strings and it is there
under "Switch between relative, absolute and mixed references".

Under contents it is:

Creating and correcting formulas > Using References > Switch between
relative, absolute and mixed references

But searching in Help is not the most user friendly or easiest to get
results from at times.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Michael Karas" <mkaras@CarouselDesign.com> wrote in message
news:007e01c34cde$20337600$a001280a@phx.gbl...
> Well....that works great. All along (for years actually) I
> knew there had to be _SOME WAY_ to do this but could never
> discover it in on-line the documentation. Instead I had
> always tried things like CTL-LfClick, CTL-ALT-LfClick etc
> etc etc all to NO avail.
>
> Thanks for the quick and excellent response!!!!
> Michael Karas
>
>
> >-----Original Message-----
> >Hi Michael!
> >
> >Is this what you want?
> >
> >Click the cell and then press F4.
> >
> >F4 acts as a toggle going through the four reference
> options.
> >
> >-- 
> >Regards
> >Norman Harker MVP (Excel)
> >Sydney, Australia
> >Holidays and Observances Friday 18th July: Mexico (Day of
> Mourning
> >death of Benito Juarez), Spain (Labor Day), Uruguay
> (Constitution Day)
> >njharker@optusnet.com.au
> >Excel and Word Function Lists (Classifications, Syntax
> and Arguments)
> >available free to good homes.
> >"Michael Karas" <mkaras@CarouselDesign.com> wrote in
> message
> >news:06cf01c34cd7$8c98f560$a301280a@phx.gbl...
> >> When one is in the process entering a formula into an
> >> EXCEL spreadsheet it is possible to click on another
> cell
> >> to automatically have this cell reference placed into
> the
> >> formula. However this entry always is entered as a
> >> RELATIVE cell reference. Is there a way to have EXCEL
> >> insert an ABSOLUTE cell reference into the formula??
> >>
> >> It seems to me that it would be so common and yet I have
> >> never been able to figure this one out.
> >>
> >> Please Help
> >> Michael Karas
> >>
> >
> >
> >.
> >


0
njharker (1646)
7/18/2003 7:33:22 AM
Reply:

Similar Artilces:

printing error handling issue? (when clicking cancel get 2501 error)
I have a report that when it is closed the DoCmd RunCommand acCmdPrint occurs ... but of course if the user clicks cancel, it gives me a 2501 error. How & where do I put code to surpress this error. I would just the close even to run the DoCmd RunCommand acCmdPrint and if cancel is selected just continue to close the report? TIA, _Bigred I understand that I have to do this, I actually don't understand HOW to do it, and where to put code to accomplish this?? 1) What code do I use? 2) Where do I put the code? Thanks, _Bigred "Duane Hookom" <duanehookom@NO_...

Lookup and Sum in same cell
SHNAME ENTDATE SYMBOL QTY RATE COMM C78 1/24/2005 CSCO 1100 0.018 $20 C78 1/28/2005 CSCO -5439 0.018 $100 C78 2/23/2005 CSCO 50 0.017 $1 C78 4/5/2005 CSCO -7892 0.018 $140 C78 4/13/2005 CSCO 3786 0.018 $69 I am trying to create a lookup formula that will search by SHNAME, find each entry and sum the COMM column in one cell: SHNAME COMM C78 $360 This worksheet is thousands of rows long with multiple rows for each SHNAME. Thanks in advance -- Potatosalad2 ------------------------------------------------------------------------ Potatosalad...

Cell Border Line Width
Is it possible to specify the cell border line width using Excel 2002? The "Border" tab in the "Format Cells" dialog box allows me to choose predefined line widths but I need a width other than what is offered. Is there a way to do this? Thanks! Adam Not with the borders property...you are restricted to xlHairline, xlThin, xlMedium, xlThick"....... You could *possibly* fake it with the AddLines method of the worksheet but I wouldn't really want to try.....;o) OJ ...

How can a hyperlink be opened by pressing Enter.
In Excel 2003 after inserting a hyperlink to open a picture file stored in the local drive the user needs to be able to open the file by pressing Enter on the cell that contains the hyperlink rather than by clicking on it with the mouse. This feature is enable in Excel 2000 but it the not work in 2003. (the file type or location does not matter). In short, how can a hyperlink be opened by pressing Enter in Excel 2003. Thank you in advance for your help, Carolina I think that this went away in xl2002. You could send a message to mswish@microsoft.com asking for this to come back. Carol...

iserror and ifs nested formulas
Please help! I am trying to get the formula below to return a value to the cell ONLY if the answer to ((c30-$30)/g$30) is greater or less than 20%. I am using the below formula. =IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30)," ")) Thanks Try =IF(ISERROR((C30-$G30)/$G30),"-",IF(ABS(C30-$G30)/$G30>0.2,(C30-$G30)/$G30," ")) -- HTH Bob "Vicki" <Vicki@discussions.microsoft.com> wrote in message news:22EA6740-B9B9-4834-8AEA-217DD36C3B4D@microsoft.com... > Please he...

Locked cells in IE
Hi, I need to open an Excel document from a web page. Active X controls are restricted and I cant pass the document as a parameter in a shortcut to the Excel executable because it needs to be generic (and would not e in this case). The problem Im having is that the Excel document contains locked cells that need to remain locked. When the document opens within the internet explorer interface the locked cells are not visible and the spreadsheet looks a bit disjointed. Does anyone know another way to open the document from a web page or how to make the cells visible? Many many thanks...

UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a single sheet. I would like to format a column of cells to be all upper case but not use the =UPPER() function. I'd like to format a cell to force any lower case char to upper case automatically when data is entered. Custom formatting as >CCCC doesn't work. Can this be done? Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2), then remove the reference to A2 when printing the result? I don't want the cell A2 to print. This I forget how to do. Hi you'll need VBA for this. ...

find last cell in column
Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be ..388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 ...

3d reference
Is it possible to suppress the "0" that is returned when referencing a blank cell in another worksheet? I have used an IF function to ensure blank cells in the past, but that requires a good bit of labor for even a medium sized worksheet. I tried various cell formats, but continue to get "0"s in the linked cell. You could use a custom number format or even Format|conditional formatting. But those will hide the cells that are returning an actual 0. I'd do that extra work: =if('sheet 99'!a1="","",'sheet 99'!a1) ==...

Tweaking cells
If I have cells that may remain blank, how do I get them to be blank instead of showing a 0? These cells are set for text, not numbers, so I don't understand why a number would show there by default. TIA Steven Connor Hi do you have formulas in these cells and if yes what kind of formulas? -- Regards Frank Kabel Frankfurt, Germany **{Steven}** wrote: > If I have cells that may remain blank, how do I get them to be blank > instead of showing a 0? These cells are set for text, not numbers, so > I don't understand why a number would show there by default. > > TIA >...

Setting up Solver reference in a protected VBAproject
Hello, I want to distribute a Excel 2007 workbookk that contains several macros. I need to protect the VBAproject so that the users cannot modify the code. However, in order to use some of the macros the user needs to enable the Solver Add-in, and for that purpose, he needs to have access to the unprotected VBAProject. Is there a way to setup the Solver Add-in while maintaining the VBA project protected (and without disclosing the password)? Any ideas would be appreciated. Regards, OMER ...

How do I enter temperature symbol in a chart legent
Can someone help me to enter a superscript font (temperature symbol) in a Excel spreed sheet legend box. The degree symbol ° is not a superscripted character, it's a regular character in many common fonts. Hold down ALT while pressing 0176 on the numeric keypad. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DS wrote: > Can someone help me to enter a superscript font (temperature symbol) in a > Excel spreed sheet legend box. Thank you Jon, you are great !!! "DS" wrote: ...

IF/OR formula
I have this formula in a cell: =IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a") I also want to apply this formula to the same cell: =IF(F71="XL","--","") Both formulas work independently, but the first one is for if NUMBERS are entered in column F and the second formula is for if specific TEXT is entered in column F. How could I combine these two formulas? I think I need to have the formula first identify the type of contents in column F in order for this to work...? Thanks. Would =IF(ISNUMBER(H71),IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a&quo...

Text cell complains about date
I have a column containing ratios like 3/4, 3/0, 5/2, etc. I have them all formatted as text. All of the cells that have a zero as the second number (3/0) get a little green triangle and a warning that I have entered a "date string with only 2 digits for the year". Huh? How is "3/0" a date string with 2 digits for the year? And, why is it complaining about a text field? More importantly, how do I get Excel to stop being so "helpful" and leave my damned text data alone -- unexamined? Remove the warning in Options, Tools>Options>Error Checking in 2003...

Formula #68
Hello, When ever I type a formula in a cell (For Ex: =sum(A63:A77)), I see the formula in the cell. I am not able to see the value (result) of the formula. I am not sure what setting was changed and how it was changed. Can you please let me know how to revert the settings so that I can see the resultant value of a formula in the cell? Thanks and Regards, Charles 1. clear the cell 2. Format > Cells... > Number > General 3. retype the formula (DON'T PASTE IT FROM ELSEWHERE) Your problem is probably that the format is Text. Good Luck -- Gary''s Student "Cha...

Which formula do I need?
Hi! Would appreciate any help you can send my way: I have a sales spreadsheet showing sales per month for last year versus the current year. The data is input for all of last year and the data for the current year would be available through the current month. There are totals at the end of each row and I'd like the total for the prior year to add through the current month. Example: First Row: JAN thru DEC sales Second Row: JAN thru AUG sales The total column for the first row would only add the sales from JAN - AUG. I tried using SUMIF but don't think I...

Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would like, in my fantasy, to just hightlight all of them and say "multiply by x" where x is an integer. Now, I know I can't do that because my microphone doesn't work, so, other then a macro, is there a way to do it? Thanks, -Jim In an unused cell enter the numnber that you want to multiply by - it can be an integer or a floating point number - then highlight the range to you want to mutiply and then right-click and select Paste Special > Multiply > Ok Don't forget to say "Multipy by x"...

change cells contents from vertical to horizontal
I need to copy from one workseet the values in cells that are arranged vertically into another worksheet and the values need to be arranged horizontally. Can this be done? If so, how? Select the data to be copied>Copy>Select target cell>Right click>PasteSpecial>select 'Transpose>OK If this post helps click Yes --------------- Jacob Skaria "GailH" wrote: > I need to copy from one workseet the values in cells that are arranged > vertically into another worksheet and the values need to be arranged > horizontally. Can this be done? ...

Error messege when I open or right click on a folder in public folders, ID no: c1030af0
Hi I've recently added another nic and ip-adr. to my exchange 2003 server. And after this new hardware and ip.adr Ive got a problem whith public folders. When I look at the properties on a subfolder in public folders with exchange system manager I'll get the following error: Exchange System Manager The operation failed due to an invalid fomat in the HTTP request. Verify that the host header is correct for the virtual server. ID no: c1030af0 Exchange System Manager I'm not sure how the host headers should look like for the virtual server. Any suggestion? Thanks in advanc...

Formula Question... LookUP
I am using the follwing formula to calculate the last entry in a range.. =LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 Details'!C416:C428) Is there a simple way to calculate the last but one ??? Any help greatly appreciated.. Thanks G, rephrase your question. I'm not sure what you mean by this. Are you trying to calculate its position on the worksheet? O -- Message posted via http://www.officekb.com Hi! >Is there a simple way to calculate the last but one ??? Does that mean if your formula returns the value in C420 you now want a formula ...

Creating new series requires div cell val by itself . How to do t.
I am trying to rebase a time series which requires dividing the first value in each series by itself. I keep getting circular error messages regardless of how I format the calculation. Is ther a way around this issue? Texas When you divide a number(and time is a number) by itself you will get 1. Is that what you want? You could use a couple of helper columns. Assume data in column A. In B1 enter =A1. In C1 enter =A1/B1. Gord Dibben Excel MVP On Mon, 21 Mar 2005 12:35:08 -0800, "Texastom" <Texastom@discussions.microsoft.com> wrote: >I am trying to rebase a time ...

can you single click to open files in word and excell?
Is it possible to set the default to a single click when opening word and excell files? There's a windows setting (at least in WinXP) that you can toggle to make a single click open the file. Windows Start button|settings|control panel|Folder Options|General Tab At the bottom of that dialog. lvinning wrote: > > Is it possible to set the default to a single click when opening word and > excell files? -- Dave Peterson I have it set to that level, but once I'm inside of word or excell, you're required to double click on a file name before it will open. Some other ...

Finding Min In Calculated Pivot Table Formula
I didn't have much luck on another list, so I thought I'd try this one. Any thoughts on the below would be appreciated. I have a pivot table with a calculated field for which the equation is [Sum of Dollars / Count of Instances]. So in turn I'm rendering the average cost for a list of items in a group. The table is set up such that each column contains a week number and the rows contain a list of items within a grouping. For example, I might be listing average cost of apples, oranges, and peaches for each week under a grouping called fruit. The next grouping is bread, where I...

sum cells
I am trying to total employee schedule so some cell have D/O day off, R/O requested off, so when I try to total all associates by the day I get #VALUE! in cell and total cell. Is there a way to total only numbers and ignore #VALUE! Try this: =SUMIF(A1:A10,"<>#VALUE!") Why not fix the formula that causes the error in the first place? What is the formula that does this: >when I try to total all associates by the day I get #VALUE! Biff "SteveD" <SteveD@discussions.microsoft.com> wrote in message news:7D8BF85A-F76E-4F35-95AC-50B77B128304@microsoft.com....

Cannot Enter Desired Value
When I enter a number (229.65) then hit "enter" or "tab", I ge "2.30E+02". What the heck is that and how do I turn it off -- Message posted from http://www.ExcelForum.com You probably accidentally formatted your cell as Scienfific. Remedy: Format>Cells>Number>General, or some other standard or custom format. -- Kind Regards, Niek Otten Microsoft MVP - Excel "myrrh >" <<myrrh.1236mk@excelforum-nospam.com> wrote in message news:myrrh.1236mk@excelforum-nospam.com... > When I enter a number (229.65) then hit "enter"...