Hi



I need to build in a formula that will show if a target is met depending on what is selected from a drop down list in a cell. Each item on the list has a different target. The only way I can get it to work is by having a separate target met cell for each of the 10 items but ideally I would want one formula and one cell showing the result. Not sure I am wording this clearly enough...been working on this all day with no joy.

Here is my drop down list (named range of TypeOfRequest):-

Replacement cards
Work patterns
Password reset
New users
Query
Managers access
Location access
Update/merge record
Close record
Further info requested

Replacement cards and New users have a target of 5 working days to be actioned and all others 2 working days.

The list is in cell A2 and the number of working days taken is in cell B2 and the target met (Yes or No) is in cell C2.

I had tried tweaking a formula that I had used previously for something similar but different list but with no joy, this was:-

=IF(DA3="","",IF(((MATCH(DA3,DocumentType,0)>3)*(E Y3<=5)+(MATCH(DA3,DocumentType,0)<=3)*(EV3<=40))," Yes","No"))

Any thoughts.

Thanks in advance.

Lynn

Create an additional sheet e.g. RequestTypes with columns RequestType, Target.

Define the created table as Table (e.g. tRT), or define a dynamical named range (e.g. nRT) which includes datarange of created table.

As a bonus, you can define the data column of RequestType in tRT or 1st column of nRT as named range, and use this named range in your original table as Data Validation List source to select TypeOfRequest values.

Your formula OnTarget formula will be
in case you used defined Table:
in case you used Named Range:
The formula returns TRUE when number of workdays is less or egual compared with target for reguest type, and FALSE when otherwise. When either TypeOfrequest or number of workdays remaind empty, an empty string is returned.
There is no limitations for target values - all requests may have same target, or every request type can have different target. It is up to you how you define them in RegustType sheet.

Thank you so much for the quick response. This is way beyond anything I know but gave it a go and have attached the results. I think it's nearly there.

Instead of returning FALSE or TRUE, can it return Yes or No?

Also, think I may have done something wrong as the drop down list also has my target values??

Once last thing, is this working out network days as we only work Mon-Fri? I also need to factor in the public holidays so they are not counted.

So sorry for list of queries but hope you can help.

Not sure it's relevant but I'm using Excel 2016.

Thanks.

Lynn

Attached Files

Target Workbook.xlsx (11.2 KB, 10 views)

I'll have a look tomorrow (it is 1:30 before midnight for me, and I haven't Excel available at moment too).

Dear Friend,


Kindly find the attachment, It might helpful for you...

Attached Files

Target Workbook.xlsx (11.0 KB, 10 views)

Here are 2 variants with same outcome - one using Named Ranges, another using defined Tables (and a Named Range lRT as Data Validation Source, because Data Validation doesn't allow to use Table formulas).

Attached Files

	TargetWorkbook_Names.xlsx (11.8 KB, 12 views)
	TargetWorkbook_Tables.xlsx (14.4 KB, 10 views)

Dear Friend,


after reading your query that you are not including weekend and holidays,

i modified my formula, You just need to update the date which are actually holiday according to your office.


kindly find the attachment...

Attached Files

Target Workbook.xlsx (11.4 KB, 11 views)

Did you look into my last post and at 2 attachments there? In main table is a column Workdays, where the number of working days between 2 dates is calculated (with holidays accounted). And this Workdays value is compared with Target.

Copy your holiday dates into table on sheet Holidays in ether of workbooks I provided (I entered some dummy dates into this table to avoid formulas returning some error). That's all. From there you can carry on.

Dear Friend,

Do you find any error in my formula?
Kindly check and let me know if it is not ok.

The formula is OK. But holiday list may be truncated to 2 top entries (or corrected) - one holiday is repeated 13 times!

13 repeated will be filled by LynnMac2016

Guys,

I cannot thank you enough - all 3 examples make total sense and more importantly - work!

This has assisted me greatly and also my understanding of Excel further!

Happy days!

Lynn

It looks like along the way I was confused who was OP

Hi

I'm back!

I managed to get a colleague's spreadsheet working with the examples provided above which is great. So I thought I would try and use something similar for my own requirements which are slightly different e.g. different tasks and different targets.

I have attached my spreadsheet here and need a formula that will work out the cells from AJ to AR but have had no luck trying to tweak the 3 examples kindly provided for my needs.

Also, if something is received after 12 noon, I don't want that day counted in the number of working days.

I have also broken my master tab up into separate tables, blue is for logging mail, orange is for the completion of task and green is for document type. Is this the problem cos I'm not precious about keeping the sections as a table. I'd be happy with one big table!

I hope this all makes sense.

Can you help?

Lynn

Attached Files

Workload Tracker.xlsx (48.5 KB, 9 views)

Unless there are different columns for different tasks, you don'g gain anything with separate tab's. But:
a) Formulas which read data from several tasks are going more complex;
b) It depends on user, but I myself think, that more different entry sheets means more possibilities for entry errors by confused user.

As I don't have Excel at home, I can't look at your table now.