Need help to remove FALSE and shorten the formula

Please provide a sample file.

See "HOW TO ATTACH YOUR SAMPLE WORKBOOK" at top of page.

Thanks AliGW and JohnTopley. I have attached a sample workbook.

Use the same approach for Manpower Costs AND avoid using merged cells when formula are required.

Thanks JohnTopley!

May I know how to add another Match function in the same formula? Column K is also dependent on the selection at Column D. Currently the formula tries to match Column B & C, but not Column D.

Use

=IFERROR(INDEX(Costs!$C$2:$E$14,MATCH(Prototype!$C6&D6,Costs!$A$2:$A$14&Costs!$B$2:$B$14,0),MATCH($B6,Costs!$C$1:$E$1,0)),"")

This must be entered with Control+Shif+Enter

Hold down CTRL and SHIFT kets and press ENTER

You will see { ....} round the formula

Thanks JohnTopley for your help! The formula works perfectly!

May I also take the opportunity to ask some questions?

How do I create a dependent drop down list to check inputs from 2 cells? The current data validation dialog box has character limit, thus I am unable to enter all the possible arguments. My Column D is dependent on Column C, but I need to break down Manpower Cost from the original “Across All Care Settings” to “Specialist Outpatient Clinic”, “Inpatient Ward”, “Operating Theatre” and “Endoscopy Room”. However, with this change, it will affect the drop-down list in Column D, causing it unable to show the list of Activity under "Type of Activity" worksheet. I have kind of summarised the expected result for Column C and Column D.

Image 1.PNGImage 2.PNG

See the attached.

A new Sheet "Workarea" has the dynamic lists generated for the various combinations of C and D. The "Maintenanc Cost" is simple a copy of the "Tpe of actvity" Sheet"

In "Workarea"

B2

=IFERROR(INDEX(Costs!$B$1:$B$14,SMALL(IF((Costs!$A$1:$A$14=Workarea!$G$2)*(INDEX(Costs!$C$1:$E$14,0,MATCH(Workarea!$B$1,Costs!$C$1:$E$1,0))<>"Not Applicable"),ROW($1:$14),""),ROWS($1:1))),"")

Enter with CTRL+SHIFT+ENTER

Similar in C & D

In G2: this has the value selected in column D of "Prototype"

In "Prototype" a small VBA reoutine;

Please Login or Register  to view this content.

To see/add code click on "Developer" then "Visual Basic". Click Sheet1 ("Prototype") and code will be on right-hand side window

When data is entered in "Prototype", the data in "Workarea" will change under the appropriate cost heading

NOTE: this now an XLSM (macro workbook)

To avoid repeating the "Type Of Activity" data under "Manpower Costs" in the "Workarea" sheet, I set "Manpower Cost" named range to that in "Type of Activity".

I'll leave you to do the change in the workbook I sent.