Formula Help

[elsone31]

Using Excel 2013

I have a workbook that has a tab for New data, Old Cumulative and working.

I want to subtract the data on the New Data and the Cumulative tabs, that have matching headers. I was able to write formula(located in the workbook the Working sheet starting in cell F2) to do that. The problem is I can not come up with a way to get the data that is ONLY on the new data tab to populate.

I have tried using an IF statement in conjunction with match, posted below. They work separately, but not together.


So far so good Finds new data
=IF(MATCH(F1,'Old Cumulative'!F1:L1,0),INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))-INDEX('Old Cumulative'!$F$2:$IV$4000,MATCH(Working!$E2,'Old Cumulative'!$E$2:$E$4000,0),MATCH(Working!F$1,'Old Cumulative'!$F$1:$IV$1,0)))

2nd part Returns data if no new data was added to TEK
INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))

I would appreciate any assistance with the formula as well as ideas to get it into a macro.

Thanks in advance for any assistance.

[mancubus]

add an error handler, such as IFERROR function.

formula in B2 in working sheet:

=IFERROR(INDEX('New Data'!$B$2:$IV$38,MATCH(Working!$A2,'New Data'!$A$2:$A$38,0),MATCH(Working!B$1,'New Data'!$B$1:$IV$1,0)),0)-IFERROR(INDEX('Old Cumulative'!$B$2:$IV$38,MATCH(Working!$A2,'Old Cumulative'!$A$2:$A$38,0),MATCH(Working!B$1,'Old Cumulative'!$B$1:$IV$1,0)),0)


copy B2 to C2:J2
copy B2:J2 to B3:J38


=IFERROR(YOUR_FORMULA_HERE,VALUE_TO_RETURN_IF_YOUR_FORMULA_RETURNS_ERROR)

VALUE_TO_RETURN_IF_YOUR_FORMULA_RETURNS_ERROR is 0 in the formula i posted.

[elsone31]

This worked perfectly, thank you!

Is there a way to modify my formula to not subtract if the cell on the New data tab is empty?

E

[mancubus]

you are welcome.

the reasoın for iferror function is to handle this.t
"iferror" returns zero and as you know (as per zero property in +/-), adding 0 to a number or subtractiong 0 from a number returns the number itself.