A common scenario for me is I'll copy a formula and use it in a new sheet. Often (as in the img) the formula will reference the same cell over and over. In this case it is cell BF7. When I first pasted the formula in there, those four occurrences were pointing to AX7. What is the easiest way to change all for of those at once? I know that I can do "find-n-replace." If there were 20 occurrences, I would have done that. For this example, I merely drag-n-dropped the little colored square from AX7 to BF7, four times (once for each color).



I think I remember reading somewhere about a keyboard combo that will drag all the colored squares from one location to the other in one drag. Do you guys know how to do this?

It would help if we could work on the sheet. Pictures are not a great help

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The actual workbook has confidential info, but here is a simplified mockup...
Also I did a quickie screencast of myself click-n-dragging the colored boxes one at a time. Original Question was: Is there a way to drag all 4 at once?


https://www.screencast.com/t/iXaYaPwLyyke

Attached Files

Example of multiple references.xlsx (9.0 KB, 7 views)

The easiest way to avoid this is to use standardized templates so the data for that purpose is always in the same columns.

Beyond that, you could use relative references in R1C1 style rather than A1 style. That way instead of referring to cell BF7 you'd be directing it to the cell two columns to the left and no rows up or down. See also: Excel: Learn R1C1 Referencing to Understand Formula Copying - Excel Articles

Finally, I'd say find/replace would be really easy.

Ann

Thanks for the feedback everyone.
I actually found an article that covers this
Grab & Drag All Instances of Cell Reference in Excel Formula – Analyst Answers
but the guy in the article says it's impossible, and gives several alternatives.