Why doesn't [Yes to All] work?

[Eloise T]

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


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


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?

 

[Eloise T]

By the way, it doesn't matter if you're copying row(s) from Demo-1 to Demo-2 or vice versa.

 

[Peter Bartholomew]

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]

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]

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]

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]

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]

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]

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]

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]

@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]

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?