Paste special format empty cells too

[RAJESH1960]

Hello Everyone,

This sheet contains data which I have copied from the workings of a sheet and pasted here using paste special values. I have inserted an additional column to check the totals if they are correct. I have given the formula as shown above. The Problem is if the amount is blank, which is in most of the cases, the calculation shows an error. I have to delete the whole blank cells manually each one of them. As the data is huge, it will be time consuming to do so. Can this be avoided .? Is there any way I could paste the data in such a way that the blank cells are not formatted. ?

Query Do not format blank cells.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Check	A	B	C	D	E	Amt	G	Amt	I	Amt	K	Amt	M	Amt	O	Amt	Q	Amt	S	Amt	U	Amt	W	Amt	Y	Amt
2	-	01-02-2021	R	1		A	-3751.00	January	3752.00	Sunday	-1.00
3	#VALUE!	03-02-2021	P	2		B	-675.00	February	674.00	Monday	1.00
4	#VALUE!	04-02-2021	C	3		C	-3500.00	March	3538.00	Tuesday	-38.00
5	#VALUE!	06-02-2021	R	4		D	-18000.00	April	18114.00	Wednesday	-114.00
6	-	07-02-2021	P	5		E	-24000.00	May	10000.00	Thursday	10000.00	Sunday	4000.00
7	#VALUE!	09-02-2021	C	6		F	18000.00	June	-20000.00	Friday	2000.00
8	#VALUE!	11-02-2021	R	7		G	-1180.00	July	1176.00	Saturday	4.00
9	#VALUE!	16-02-2021	P	8		H	-3600.00	August	3601.00	Sunday	-1.00
10	#VALUE!	17-02-2021	C	9		I	-12.00	September	6.00	Monday	6.00
11	#VALUE!	19-02-2021	R	10		J	-5148.00	October	5147.00	Tuesday	1.00
12	#VALUE!	22-02-2021	P	11		K	-11100.00	November	11110.00	Wednesday	-10.00
13	#VALUE!	22-02-2021	R	12		L	-8285.00	December	8284.00	Thursday	1.00
14	-	26-02-2021	P	13		M	7786.00	January	-1493.00	Friday	-3045.00	Monday	-3248.00
15	#VALUE!	27-02-2021	C	14		N	-10678.00	February	10674.00	Saturday	4.00
16	#VALUE!	28-02-2021	R	15		O	20000.00	March	-100.00	Sunday	-19900.00
Query Paste special selection

Cell Formulas
Range	Formula
A2:A16	A2	=SUM(G2+I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2)

 

[jasonb75]

Try changing the + symbols to commas.

 

[RAJESH1960]

Try changing the + symbols to commas.

Jasonb, That is not the problem. When I paste the data with paste special in an application, it show an error "Following Errors found in validation" and also displays a message the totals do not match with the row number. This is because when I pasted the data with paste special, it formatted the empty cells too. I wanted to know is there a way to paste data without formatting the blank cells. Sometimes the data is in 10 columns, at times 11, or maybe sometimes also 20 in different rows.

 

[Fluff]

Another option

=SUMIFS(G2:AA2,$G$1:$AA$1,"Amt")

 

[jasonb75]

it show an error "Following Errors found in validation" and also displays a message the totals do not match with the row number.

Where was this information in post 1?

The answers provided are as good as the question.

 

[RAJESH1960]

Another option

=SUMIFS(G2:AA2,$G$1:$AA$1,"Amt")

Where was this information in post 1?

The answers provided are as good as the question.

Sorry, I meant that only, but in the next post I was able to explain clearly.

 

[Fluff]

Are you copy/pasting manually?

 

[RAJESH1960]

Are you copy/pasting manually?

Yes, Fluff. I do the workings in one sheet, and copy the whole data which includes blank cells too, then paste it to the application with paste special as most of the cells contain formulas, All the rows contain data, but some columns contain data and some are blank and that too not in a order. I have to filter each column with blank and delete unwanted format from the cells.

 

[jasonb75]

It is still not clear what the problem is or how you're creating it to begin with.

Pastespecial formats only pastes formats, it is not possible for that to cause an error.

Pastespecial values will paste the values of the source cells, if you copy empty cells you will paste empty cells, if you copy formula blanks you will paste nullstrings.

The #VALUE! errors in your formulas are likely caused by nullstrings from copying formula blanks. Either of the suggested formulas will ignore the nullstrings and tread the cells as being empty (zero value).

If there is another problem then you will need to be more specific.

 

[RAJESH1960]

It is still not clear what the problem is or how you're creating it to begin with.

Pastespecial formats only pastes formats, it is not possible for that to cause an error.

Pastespecial values will paste the values of the source cells, if you copy empty cells you will paste empty cells, if you copy formula blanks you will paste nullstrings.

The #VALUE! errors in your formulas are likely caused by nullstrings from copying formula blanks. Either of the suggested formulas will ignore the nullstrings and tread the cells as being empty (zero value).

If there is another problem then you will need to be more specific.

Maybe, I think, the application I am using, which is also an excel application used to import data from excel to another application, doesn't accept empty formatted cells. Not sure about it.