• 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.

Why doesn't [Yes to All] work?

Eloise T

Active Member
Please see the two attachments, Demo-1 and Demo-2. I have six Name Manager formulas in Demo-1. When I try to copy a single row (Columns E through F) from file Demo-2 to file Demo-1, I see: Note: SearchFor70
73414

If I click either [Yes] OR [Yes to All] I see: Note: SearchFor75, then Searchfor80, then SearchFor84, SearchFor85, and finally SearchFor90.
73419

In other words, [Yes to All] doesn't work and satisfy all six Name Manager formulas. So I have to click 6 times vs. just once for every single
line I add...something I'm doing for hours each day. In case it matters, I'm using Excel 2016 with Windows 7 Professional.

Please, can anyone tell me why [Yes to All] doesn't work?
 

Attachments

Peter Bartholomew

Well-Known Member
Good thing you do not have 100 of the Names!

Maybe it is something to do with the fact that the names are used in conditional formats. Since the cells you are copying contain data rather than formulae, you could just paste their values. Conditional formats can be reapplied separately.
 

Eloise T

Active Member
Good thing you do not have 100 of the Names!

Maybe it is something to do with the fact that the names are used in conditional formats. Since the cells you are copying contain data rather than formulae, you could just paste their values. Conditional formats can be reapplied separately.
YES, it is a VERY good thing I don't have 100!

...and I'm not sure I followed your train of thought on paragraph 2.
 

Peter Bartholomew

Well-Known Member
I was wondering whether Excel is processing each conditional format separately. That is mere speculation. What I did find was that Paste Special / Values does not trigger the dialogue, which is what you want assuming you do not need to alter the device IDs.
 

Eloise T

Active Member
I was wondering whether Excel is processing each conditional format separately. That is mere speculation. What I did find was that Paste Special / Values does not trigger the dialogue, which is what you want assuming you do not need to alter the device IDs.
I will check that out and report back. Thanks!
 

Eloise T

Active Member
I was wondering whether Excel is processing each conditional format separately. That is mere speculation. What I did find was that Paste Special / Values does not trigger the dialogue, which is what you want assuming you do not need to alter the device IDs.
Paste Special -> Values works except it treats the date as a 5-digit number instead of a proper looking date. e.g. 43329 vs. 08/17/2018.
Have any other suggestions? [Yes to All] really should work.
 

AliGW

Active Member
The 5-digit number IS a proper date in serial number form. Just change the cell formatting from general to short date. :)

OR when you paste special, select values and format.
 

Peter Bartholomew

Well-Known Member
As @AliGW says, it is just a formatting issue. You could also pre-format the column before you paste values to it. Format the entire column though, otherwise the final format change defines the used range and you are back with your bloated workbook problems.
 

Eloise T

Active Member
I just realized
The 5-digit number IS a proper date in serial number form. Just change the cell formatting from general to short date. :)

OR when you paste special, select values and format.
I tried Formatting the entirety of columns (K) in both Demo1 and Demo-2 to "Custom" mm/dd/yyyy then copied a row (E through L) and still got the date in serial number. Any other suggestions?
 

Peter Bartholomew

Well-Known Member
@Eloise T
That is certainly not what I get.
I believe there are at least two distinct options.
1. Paste values into pre-formatted destination cells adopts their number formatting.
2. Paste values and number formatting shows the numbers as in the source file but without the conditional formatting.
 

Eloise T

Active Member
BTW, I did as you suggested: Pastes values into pre-formatted destination cells to adopt their number formatting.

I'm guessing that since we got different results with the same files that Excel version / Windows version may be the root of the problem?
 
Top