Closed Thread
Results 1 to 19 of 19

auto-fill absolute references

  1. #1
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Thumbs up auto-fill absolute references

    EDIT: attention, my thread is misleading, see post #15

    How can I auto-fill absolute references (as exemplified below)?

    =$A$1
    =$A$2
    =$A$3
    =$A$4
    =$A$5
    =$A$6


    Dragging the handle and cell copying doesn't work but creates

    =$A$1
    =$A$1
    =$A$1
    =$A$1
    =$A$1


    PS: I hope there is an easier solution than diving into INDIRECT( ), ROW( ), OFFSET( ) etc., because I read http://www.excelforum.com/excel-gene...-possible.html and that's way beyond my IQ.
    Last edited by boarders paradise; 07-19-2011 at 08:54 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: auto-fill absolute references

    Hello,

    use

    =$A1

    and copy down.

    The $ sign makes the following row or column reference absolute. If you want the row number to adjust when it is copied down, then the row reference needs to be relative, i.e. without the $ sign in front of it.

    So:

    A1 copied down will change to A2, A3 etc
    A1 copied across will change to B1, C1
    $A1 copied down will change to $A2, $A3 etc
    $A1 copied across will stay $A1
    A$1 copied down will stay A$1
    A$1 copied across will change to B$1, C$1 etc
    $A$1 copied down or across will remain $A$1

    Remember: if you don't want it to change, put a $ sign in front of it. If you want it to adjust to the row number, make sure there's no $ in front of the number. If you want it to adjust to the column letter, make sure there's no $ in front of the column letter.

    cheers,
    Last edited by teylyn; 07-18-2011 at 07:34 AM.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: auto-fill absolute references

    Also, it would be good to learn Absolute and Relative references.
    It takes only few minutes and you can find it on youtube.

    Maybe this can help: http://www.youtube.com/watch?v=NmVMjQzseLA

  4. #4
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Re: auto-fill absolute references

    AFAIK I do understand the --> difference between absolute and relative references.

    I want absolute references as said in OP.

    $A$1 copied down or across will remain $A$1
    that's what I pointed out in my OP
    Last edited by boarders paradise; 07-18-2011 at 02:21 PM.

  5. #5
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Re: auto-fill absolute references

    Do you want me to explain, why I need absolute references?
    I can, no problem, but I don't think it is a prerequisite for answering the question, thus distracting from it.
    My reasons are similar to those of the user I linked to in my OP.
    Last edited by boarders paradise; 07-18-2011 at 02:21 PM.

  6. #6
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Re: auto-fill absolute references

    if auto-fill is not possible, how else can I - with easy means - create the following series?

    =$A$1
    =$A$2
    =$A$3
    =$A$4
    =$A$5
    =$A$6
    [...]
    =$A$100

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: auto-fill absolute references

    Do you want me to explain, why I need absolute references?
    Yes, that would help figure out why you don't want to use $A1 instead.

    You cannot fill down absolute references and expect them to behave like relative ones. That's why they're called absolute.

    Can you give an explanation why you need the row number to be absolute when you apparently want it to adjust to the current row?
    Last edited by teylyn; 07-18-2011 at 05:18 PM.

  8. #8
    Registered User
    Join Date
    07-18-2011
    Location
    DC, US
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: auto-fill absolute references

    put into cell A1, then you can auto fill
    =CELL("address",A1)

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: auto-fill absolute references

    @TheShinE, that does not produce a reference to a cell, that produces a text string.

  10. #10
    Registered User
    Join Date
    07-18-2011
    Location
    DC, US
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: auto-fill absolute references

    Quote Originally Posted by teylyn View Post
    @TheShinE, that does not produce a reference to a cell, that produces a text string.
    I think that is what op wants.
    Otherwise op can use the above text string with indirect function to create absolute ref too.

  11. #11
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Question why is this list illegitimtate ?

    Quote Originally Posted by teylyn View Post
    You [...] expect them to behave like relative ones.
    I never did nor said that.

    I am very appreciative of your help and of course willing to explain WHY I want to create the abovementioned list of formulas. But I am afraid that this could get a bit complicated and individual and I am not sure either I will be able to explain everything in an unconfusing way, so I am not sure anyone will help me through this once I start to get into details.

    Therefore I wanted it to keep it simple.
    As least as long as possible.

    Please permit 1 last attempt of mine of keeping it simple.
    If this attempt fails, I will tell the whole background story.

    This last attempt is a question:

    Why is it legitimate to create this list:

    =A1
    =A2
    =A3
    =A4
    =A5
    =A6

    ... while it seems that everyone of you here is arguing that creation of the following list is NOT legitimate???

    =$A$1
    =$A$2
    =$A$3
    =$A$4
    =$A$5
    =$A$6

    I think I can export the list without dollars (A1, A2, A3, ... etc.) to a text file, and then add the dollars with my regexp application (PowerGREP), then re-import the formulas to Excel.

    But I doubt that this is the way to go.

    So why is this list illegitimate and why are you hesitating to tell me how to create it?
    Last edited by boarders paradise; 07-18-2011 at 05:42 PM.

  12. #12
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: auto-fill absolute references

    This has nothing to do with anything being legitimate or not. An absolute reference is used if you don't want the reference to change when the cell is being copied. If you DO want the reference to adjust when the cell is copied, you'll use a relative reference. That's just plain vanilla best practice.

    I'm trying to understand why you need the row numbers to be absolute references.

    Can you explain exactly what will not work in your scenario when the row numbers are relative? Maybe you need to explain the bigger picture and post the formula you are actually using instead of =$A$1

    cheers

  13. #13
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Re: auto-fill absolute references

    Many thanks for your perseverance.

    Quote Originally Posted by teylyn View Post
    An absolute reference is used if you don't want the reference to change when the cell is being copied.
    That's right. And I neither want the reference to change (hence the absolute reference) nor to copy the cells.

    But somehow I have to create the list, right?

    =$A$1
    =$A$2
    =$A$3
    ...

    And filling out hundreds of cells MANUALLY is a bit exhausting, wouldn't you say?
    (I don't want this to sound provocative, I just don't know how else to put it).

  14. #14
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: auto-fill absolute references

    Sorry, but that's sounds like you're contradicting yourself. You say you want to copy the formula down and you want the row reference to adjust. To achieve that, you can use a relative row reference.

    Why is there a need for the result of the fill-down operation to have absolute row references? That's the part I don't understand. Why do you need =$A$100 in row 100? What will NOT work for you if the cell has =$A100 ???

  15. #15
    Registered User
    Join Date
    08-31-2009
    Location
    boarders' paradise
    MS-Off Ver
    Excel 2002
    Posts
    94

    Re: auto-fill absolute references

    ok, I thought I'd keep it simple and spare everyone the details,
    but since this approach apparently does not work
    and since I was asked to tell the background story, I will do this.

    I'll post it, once I have figured out how to summarize it as concisely as possible.

    Thank you so far.

    --------------------------------------------------------------------------------------------------------

    EDIT: I realize that - despite acting to the best of my knowledge - some of my assumptions were indeed wrong and I feel very ashamed for having bothered you with this useless thread, which - I see now - was going nowhere. Having acknowledged that I have to say to my defense that the built-in Excel 2002 help is very sparse. Therefore I came to the forums here and did my own research. Unfortunately I was mislead by some postings made in the forum here, particularly by the thread "Absolute Formula auto-fill", which I linked to in my OP but I don't think anybody has read:

    I need to keep the references absolute so that if a row is added above or below the reference it won't change the formula.
    First of all, the formula on the first page has to be an INDIRECT formula with an abosolute reference ($A$1) to the second page. The reason it has to be an abosulte reference is, incase a row is added or deleted above or below the reference. If a row is added or deleted, it will change the reference in the formula if it is not absolute. The reason the formula has to also be an INDIRECT formula is becasue even though the reference is absolute it will change if the reference cell is cut and pasted or drag and pasted to a new location. The problem is when I create an INDIRECT formula with an absolute reference, I can't auto-fill the formula to multiple cells and have the absolute reference update to the corosponding reference.
    This is where I got my idea from. This user and I, we were both trying to *forcibly* get our way to get this list:

    =$A$1
    =$A$2
    =$A$3
    =$A$4
    =$A$5
    =$A$6
    [...]
    =$A$100


    My idea (of how to artificially create that list) was:

    Quote Originally Posted by boarders paradise View Post
    I think I can export the list without dollars (A1, A2, A3, ... etc.) to a text file, and then add the dollars with my regexp application (PowerGREP), then re-import the formulas to Excel.

    But I doubt that this is the way to go.

    So why is this list illegitimate [...] ?
    .... whereas his idea to create that list was:

    So here is what I did.

    I created another worksheet like this. Please refer to the PDF attachment INDIRECT FORMULA.

    I typed the different parts of the formula into seperate cells.

    Typing the absolute reference as text in D1 allowed me to auto-fill down giving me $M$1, $M$2, $M$3, and so on.

    I typed in a combining formula, which you can see in cell F1, to combine all of the cells and create the complete formula I need for my other workbook as a result. Then I selected cells A1, B1, C1, D1, E1, and F1 and auto-filled down 1000 rows. Finaly, I selected and copied column F and then paste specialed the values only in column G.

    That left me with one problem. Even though column G contains the new formula, Excel reads it as text unless I double-click on each individual G cell and press enter. Then Excel reads it as the formula it's supposed to. I've got thousands of these formulas. Does anyone know of a way to make Excel read all them as a formula with just a few clicks of a button?
    While I have been misled by the thread I quoted here (and others),
    my thread - in turn - will mislead other users, so I would suggest my thread should be deleted entirely (except if you want it to be a reference example to show other users why the idea is wrong).
    Last edited by boarders paradise; 07-19-2011 at 08:47 AM.

  16. #16
    Registered User
    Join Date
    03-24-2008
    Posts
    11

    Re: auto-fill absolute references

    Since you do not want to use indirect() or row(), the only other option I can propose is VBA. You can use the folloiwng code:
    Please Login or Register  to view this content.
    Select a set of cells where you want the formulae (in a single column), and then run the above macro.
    Last edited by fiate2000; 07-18-2011 at 06:24 PM.

  17. #17
    Registered User
    Join Date
    11-04-2017
    Location
    Honolulu, Hawaii, U.S.A.
    MS-Off Ver
    2007
    Posts
    1

    Re: auto-fill absolute references

    I had been browsing online for method(s) to accomplish exactly what the original post wanted to achieve. And instead of "auto-fill" or "copy/paste" method which will not work correctly for absolute references, I found a post with a workaround solution that is both easy and fast; assuming it's more than just a few cells needing to be changed from relative to absolute references. Kudos to JBeaucaire!

    1. Auto-fill as you normally would, for now just leave the cells as relative references.
    2. Highlight the range of cells you want to change from relative reference to absolute reference.
    3. Insert the macro as shown in post #2: https://www.excelforum.com/excel-for...-of-cells.html
    4. MAKE SURE TO HIGHLIGHT THE RANGE BEFORE RUNNING THE MACRO

  18. #18
    Registered User
    Join Date
    12-21-2017
    Location
    Salt Lake City, UTAH
    MS-Off Ver
    Microsoft Office Home and Business 2016
    Posts
    1

    Re: auto-fill absolute references

    I know it has been a number of years ago, but I have been dealing with this same problem myself for quite some time also. I just now figured out a real easy solution:
    Go ahead and auto fill your cell references with only the Column specified as absolute and the row as relative (e.g. "=$C2").
    Then auto-fill down to however far you need to go.
    THEN, Select & highlight the filtered area (in your case, you said you needed to go 100 rows) (e.g. cells C1 through C100).
    Use Find & Replace Feature by Selecting the "Find & Select" button on the top toolbar and select "Replace".
    In the "Find what" field, type "$c"
    In the "Replace with" field, type "$c$"
    Click "Replace All" button on the bottom of that dialog box.
    Wham! Done! Easy-peasy! Knew there was a way!
    Let me know your feedback!

  19. #19
    Registered User
    Join Date
    01-03-2018
    Location
    Tampa, FL
    MS-Off Ver
    Office 2016
    Posts
    1

    Re: auto-fill absolute references

    BBBRaptor, I joined solely to say your fix worked for my 1500 row spreadsheet! Changed like 100,000 references in an epic function drive sheet.

    Unfortunately it does not solve the problem me and the original OP wanted to solve which was avoiding the cell reference changing when you insert or a delete a row above or below the absolute references. If I reference $A$1502 in cell A6 (this is actually what I do) and delete row 1500 it still changes the reference to $A$1501.

    But if you just want a ton of absolute references this works awesome thanks!

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1