• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Any Cut & paste without cutting original to keep both formula locations?

Harry0

Harry0

Member
Copy past shifts the formula many times
while
Cut and past does not, but it also removes the original formula and you can only paste 1 time.

What method do you use to duplicate the original formula without shifting its formula location ?

Thanks
 
I am not sure where this discussion is going but since you ask:
I use array formulas wherever I can with all references provided by defined names (these are usually chosen to refer to absolute range references but are often formulas or array constants).

That begs the question of why you should wish to perform the same calculation in two places; some further editing of the additional copies would appear to be in order if they are to serve any useful purpose.
 
What method do you use to duplicate the original formula without shifting its formula location ?
Click to expand...

Place the cursor INSIDE the cell and drag copy the formula. Place your cursor INSIDE the destination cell and paste.
 
Harry0 said:
Copy past shifts the formula many times
while
Cut and past does not, but it also removes the original formula and you can only paste 1 time.
Click to expand...
Actually to my findings this not true when you cut and paste in the same and even a new workbook, the formula is still present. It does so when you paste from the clipboard (see picture).
61686

Copying as already suggested "in cell" or "in formula bar" would give this.
61690

Thus enabling to paste the formula wherever you need it. This can be pasted as many times as you need. Even if you have copied other stuff later.
 
Thanks but...
Why its needed? when you have to copy "mass" amount of different cells and then use "find /replace" 1 number from them than to copy past to change over a dozen numbers in the formula.

So individually doing it in the formula bar works but only for a few or takes forever.

The $ is a good idea but when not used or can not be used due to already needing the formula to be flexible for other duplication that have been done.

As for clipboard I dont see that or at least in an older version (or other temporary programs) but seem like a good idea. Even the online excel has many types of paste but not that one. Even copying cross programs does not seem to want to function right.

I guess "maybe" opening up another workbook /tab and copy past it in the same cell and then cut and past. But still not simple when a sheet is over bloated. Its like too many maneuvers over a simple thing seems to be a recurring theme.
 
Its like too many maneuvers over a simple thing seems to be a recurring theme.
Click to expand...

Possibly. Either that or your workbooks are not efficiently constructed. That's something I'd be asking of myself if I were having to manage what you seem to be describing.

Sometimes it's better to start over.
 
I think we need to see an instance of the problem to take it much further. There may be some structure in your problem that could be exploited. One possible pattern is to have a recurring array formula e.g.
=10*x +a*y
where x and y are absolute ranges but 'a' is a parameter. If 'a' is a relative reference, each array will return a different set of values.

The again, the best solution might be to have one formula that runs through all the sub-tables with a record number to ensure each record knows its position in the overall scheme of things.
 
You must log in or register to reply here.
Back
Top