Workbook has what I believe is corrupt formula

james bulb
james bulb used Ask the Experts™
on
Hi

I have a workbook that nearly does what I want it to do but I think the formula corrupt.

In sheet called Tableau if data missing from Watford sheet if comes up "NO MATCH"  and when data missing from Tableau sheet but in watford sheet it should also show as "NO MATCH"  but at moment it is showing REF!

Im also trying to set it up so that I get rid of sheet called "new rec" and I dont want any references to this.


Any Ideas?

File attached with formulas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Attaching a file is a two step process, and a great many people (including me) fail on the second step. Personally, I think the process is needlessly complex--but it is the process and has been for some years.

  • First you click the Attach File link at the bottom of the Comment box, click Browse and use the file browser to locate the file then click Open.
  • The second step is to click the Upload File button and make sure you add some text as description. Without the descriptive text, the upload will fail.

Author

Commented:
Did it not load? Done what you said

Author

Commented:
cubic-etc-workbook----Editable.xlsm

Give that a try. Loaded on I phone
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Part of your problem is the fact that worksheet TABLEAU has a space character at the end of its name. This means you must surround the worksheet name in single quotes when building formulas.

I replaced the formula in Watford cell F2 with the following, and copied it down:
=IF(E2=IFNA(VLOOKUP(A2,'TABLEAU '!A:E,5,FALSE),0),"OK",IF(E2=IFNA(VLOOKUP(A2,'TABLEAU '!A:E,5,FALSE),0),"Previous","NO_MATCH"))

Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I checked the Formulas...Name Manager dialog, and found that you had 3 named ranges. Two of them point to other workbooks, while the third points to an unspecified cell on Watford and hence has a #REF error value. I deleted all three named ranges as well as worksheet New Rec from the attached workbook. And for good measure, I renamed worksheet "TABLEAU " to "TABLEAU".

Note: you may note @ in the formulas in the attached workbook. I am using a newer version of Excel than you are (Office Insider Fast), and it has the dynamic arrays feature. One consequence of this feature is that functions which potentially return arrays are prefixed with @ when you want them to return only a single value.
cubic-etc-workbook----Editable.xlsm

Author

Commented:
Big big thanks . You are a credit to experts exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial