Technique For Same cell reference Across Many Cells:

  • Technique For Same cell reference Across Many Cells:

    This may be a dumb question, but I don't know how it's done.

    When I need to copy a part of a formula that references the same cell, yet do so across many cells, and I use copy/paste, the formula cell reference changes with the paste.

    For example:

    Cell A1 has a value

    Cell B1 =D1*A1

    Cell B2 =D2*A1

    Cell B3 =D3*A1


    If I cut and paste B1 to B2 to B3, etc., the formula will automatically change to:

    B2 = D2*A2
    B3 = D3*A3

    How do I keep a constant for A1 throughout all the cells, without having to go back by hand and changing every cell to the correct A1 reference? It's not too bad if I'm do 20 or 30 references, but if it's a row with hundreds of references, this is too cumbersome.

    Thank you! EDR

  • Hi Paddy!

    Thank you very much for your time. Yes, I found the information in the help file. That was a quick and easy fix. Appreciated!

    With regard to your post on the "other" forum, it seems locking it was a bit hasty and perhaps rude? Unless both forums are run by the same company (which is not clearly evident), perhaps users can benefit from the thread on both forums (if truly independent) as the information, although "duplicated" to you, may not be to many others.

    Moreover, as you know, some forums are "slow and few and far between", and I didn't know what to expect form this forum, so found another forum and asked the same question(s). I stated in my first post that I was new. Personally, I don't see any harm in this. Even so, I would have found my "niche" in the responses, accuracy and speed of either forum and would have dropped the other and gone with one as perhaps a "regular" poster. Believe me, I had no long term intent to post duplicates. Not worth my time. Nothing wrong with putting feelers out there to see which forum may be better suited to my tastes.

    Nothing harmful or offensive in the content of the posts either. :bisou:

  • An alternative to using the F4 function to create an absolute reference of $A$1 would be to use a named range.

    Named ranges also make it far easier to read and "debug" formulas.

    Robert Hind
    Perth - Western Australia

  • Thank you r_hind!

    Dave, understood and respected! I read it. No problem. If I may, however, please allow me to state my opinion(s).

    1. Clearly, I would not have the time to write duplicates across several forums for every question I may ever have. I clearly stated that I was new and (to Paddy) that I was "feeling out" a forum's response time and accuracy to see which forum I'd like to be a member of. Asking a duplicate question is the perfect test in my mind and as a member of MANY forums, I've seen this tactic used.

    2. In terms of "wasting" someone's free time ... people only post by choice. No one has their arm twisted. And, as repeatedly stated in both forums (to Paddy), I seriously doubt the MAJORITY of members cross between both forums (especially as fast as Paddy does).

    3. MOST IMPORTANTLY, part of the PURPOSE for a forum is the archives, so someone can search for a potential resolution to a problem. Even if the answer to the question was answered by John Doe on one forum and answered by Jane Doe on another forum (it happens all the time anyway), the potential benefits to all DIFFERENT MEMBERS of each individual forums, far outweight the time invested (of which was chosen voluntarily by the responder anyway).

    Granted, to someone like Paddy who jumps between the two (clearly frequently), I could see that being a PAIN IN THE REAR to her, but she is not the majority.

    My two cents. As an outside, unbiased opinion (and someone who is a member of many boards), I think you folks are placing too much emphasis on something that is a waste of your time. You could place that energy into other areas.

    I will choose one or the other forum and go with it. Thanks for your patience. Have a great evening! EDR :spin:

  • Dave,

    This will be my last comment on this matter, as it is clearly "off topic" and seems somewhat ridiculous.

    I just noticed Paddy D is a moderator on the other forum. Therefore, not sure why she is "hanging out" over here, or what her role is over here?

    She suggested posting a link for one forum in the thread of another forum to inform people that it is going on elsewhere. This alone, seems somewhat a conflict of interest. Granted, these forums are designed to help people, and therefore, aren't true "competitors" or "money-makers."

    However, you were to quick to point out that you are not affiliated with the "other" forum, and since you two forums aren't posting big clear links to eachother, it seems having a MODERATOR from another forum, of all people, soliciting another forum, seems at minimum, a semi-conflict of interest.

    Granted, while we're all here to help eachother, there's no doubt that the "Super Moderators" take pride in their forums and would like them to have "the most hits", "the most members", etc. This is why most forums don't advertise other similar forums.

    While a true forum is "open" and therefore, content cannot be edited (unless vulgar or pornographic), cross-referencing to other sites and pitching are bound to happen. However, to ENCOURAGE it, seems strange to me.

    Finally, on a semi-related note, I have to admit, while I don't know Paddy personally, and therefore, cannot make GRAND ASSUMPTIONS as to the kind of person she/he is, in my vast "forum user" experience(s) over the years, I've met a "few" people like that -- that are moderators, members of the one or more similar forums, are quick to respond, quick to judge and have borderline offensive tones. Never have liked those types. Most of them are burnt out, and it's evident in their hasty, impatient responses. Take a break! Take a week off from moderating. Have a beer. It's all good! :) Moving on now...

  • for what it's worth, I think a "thanks" is due to you for taking the time to express your opinions on this matter, it always healthy to get feedback on all aspects of the board.

    One worry discussed earlier was that users may feel they are being pressured into "choosing" permanently between the different forums available on the net. Personally, I hope you don't feel this is necessary and that we'll see you again soon on Ozgrid.

    I also moderate on the "other site". Perhaps I'll see you there again too, I don't know.



  • Erm

    Ive said it once and ill say it 1000 times, just link them up and most guys will be able to get the best they can from every area.

    Seems only fair.

    For years i posted on other sites and linked to Dave Hawley " Ozgrid and all i got was abuse, ???

    Then there was no such forumn, and my commets were never listened to!

    Ho hum, but i beleive link em up is only polite.


  • Thanks Chris and Jack in the U.K.!

    Chris, I've gotten ZERO from the other board (in past 24 hours - other than Paddy and I going at it), and therefore, already deleted the link to that forum. Whereas I've received plenty of kindness over here (Paddy included over here).

    Any intelligent person would make the obvious decision. Ozgrid! :thumbcoo:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!