Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 06-11-2018, 08:40 AM
LynnMac2016 LynnMac2016 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2016
Posts: 25
LynnMac2016 is on a distinguished road
Default Target Met formula

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
Reply With Quote
  #2  
Old 06-11-2018, 10:19 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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:
Code:
=IF(OR($A2="",$B2=""),"",(SUMIF(tRT[Target],tRT[RequestType],$A2)>=$B2))
in case you used Named Range:
Code:
=IF(OR($A2="",$B2=""),"",(VLOOKUP($A2,nRT,2,0)>=$B2))
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.
Reply With Quote
  #3  
Old 06-11-2018, 11:22 AM
LynnMac2016 LynnMac2016 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2016
Posts: 25
LynnMac2016 is on a distinguished road
Default Target Met formula

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
File Type: xlsx Target Workbook.xlsx (11.2 KB, 3 views)
Reply With Quote
  #4  
Old 06-11-2018, 12:36 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

I'll have a look tomorrow (it is 1:30 before midnight for me, and I haven't Excel available at moment too).
Reply With Quote
  #5  
Old 06-11-2018, 11:04 PM
Shashi Kant Shashi Kant is offline Windows 7 32bit Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Friend,


Kindly find the attachment, It might helpful for you...
Attached Files
File Type: xlsx Target Workbook.xlsx (11.0 KB, 2 views)
Reply With Quote
  #6  
Old 06-11-2018, 11:23 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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
File Type: xlsx TargetWorkbook_Names.xlsx (11.8 KB, 6 views)
File Type: xlsx TargetWorkbook_Tables.xlsx (14.4 KB, 3 views)
Reply With Quote
  #7  
Old 06-11-2018, 11:33 PM
Shashi Kant Shashi Kant is offline Windows 7 32bit Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

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
File Type: xlsx Target Workbook.xlsx (11.4 KB, 5 views)
Reply With Quote
  #8  
Old 06-12-2018, 12:16 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by Shashi Kant View Post
after reading your query that you are not including weekend and holidays.
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.

Quote:
Originally Posted by Shashi Kant View Post
i modified my formula, You just need to update the date which are actually holiday according to your office.
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.
Reply With Quote
  #9  
Old 06-12-2018, 12:48 AM
Shashi Kant Shashi Kant is offline Windows 7 32bit Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Friend,

Do you find any error in my formula?
Kindly check and let me know if it is not ok.
Reply With Quote
  #10  
Old 06-12-2018, 03:27 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by Shashi Kant View Post
Do you find any error in my formula?
The formula is OK. But holiday list may be truncated to 2 top entries (or corrected) - one holiday is repeated 13 times!
Reply With Quote
  #11  
Old 06-12-2018, 04:30 AM
Shashi Kant Shashi Kant is offline Windows 7 32bit Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

13 repeated will be filled by LynnMac2016
Reply With Quote
  #12  
Old 06-12-2018, 06:09 AM
LynnMac2016 LynnMac2016 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2016
Posts: 25
LynnMac2016 is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 06-12-2018, 11:11 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

It looks like along the way I was confused who was OP
Reply With Quote
  #14  
Old 06-13-2018, 08:31 AM
LynnMac2016 LynnMac2016 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2016
Posts: 25
LynnMac2016 is on a distinguished road
Default

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
File Type: xlsx Workload Tracker.xlsx (48.5 KB, 3 views)

Last edited by LynnMac2016; 06-13-2018 at 08:33 AM. Reason: Spelling errors
Reply With Quote
  #15  
Old 06-13-2018, 08:49 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
vba to replace old value by target value search almost key words matching manilara Excel Programming 6 11-19-2015 08:19 PM
Insert space before and after target text Marrick13 Word VBA 6 06-20-2014 07:46 AM
Envelope Printing is off target - need help please! mylan Word 2 10-24-2012 12:24 PM
Send OFT when target email arrives RandWald Outlook 0 11-11-2011 10:52 AM
Target line on a excel graph leroytrolley Excel 1 01-16-2009 04:19 PM


All times are GMT -7. The time now is 07:26 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft