Target Met formula

ArviLaanemets · #16 06-13-2018, 10:42 PM

There I no clue based on which columns are calculated values for AJ:AR, but I think for AJ this must be something like

Code:

=NETWORKDAYS(
          Table2[@[Date received]]+
                    IF(
                              AND(
                                        Table2[@[Time received]]>0,5;
                                        Table2[@[Date received]]<Table4[@[Date completed acceptance slip received from employee]]);
                              1;
                              0
                    );
          Table4[@[Date completed acceptance slip received from employee]];
          Holidays
)

NB! When time received > 12:00, then the calculation of working days starts from next day, except cases, when task is completed on same date.

Btw. With Table formulas, it is reasonable (to make reading and editing formulas easier):
1. to avoid special characters and spaces in column names;
2. to keep column names as short as possible.

ArviLaanemets · #17 06-13-2018, 11:36 PM

Some additional considerations.

1. Rename all defined Tables with meaningful names (currently they are default names like Table2, Table3, etc.). (Whenever you rename a defined Table, or column header in it, this change is made automatically in all Table formulas in your workbook.)

2. Define your pick-lists as Tables. Replace your named range formulas for pick-lists with formulas like

Code:

=TableName[ColumnHeader]

The result is, that your pick-lists will be dynamic. Whenever you add a new entry/entries at bottom of Table (no empty row left between Table and new entry), the table is extended automatically, and as result the according named range is extended too. Essential is this for pick-list's Team and Holidays;
No need for pick-lists for data validation lists which have small number of fixed selections (like Yes/No). You can enter such list directly into Data Validation, like "Yes;No" (without equal sign, and without quotes, and I'm not sure about delimiter for your settings).

3. I just did see, that there are several pick-lists with repeated values (Input Target, Authorsing Target, Document Target). Are those linked to task/document type values in pick-lists at left of them? When yes, then make Input Task, Authorising Task and Document Type Tables 2-column ones. Task/Document Type named ranges are not affected, as they are defined for column, and Target's are calculated using VLOOKUP(), like

Code:

=VLOOKUP([@TaskColumn],tTaskTable,2,0)

LynnMac2016 · #18 06-19-2018, 06:29 AM

Hi

Sorry for not getting back sooner, I've been offline for a while.

I'm struggling a bit with the terminology and I think I'm trying to be to complicated.

Here's what I need...each document type has a target e.g. Letter 1 has a 5 day target and Letter 2 has 30 day target.

I've converted all tables to ranges, thinking this might be easier.

I've attached my workbook.

Again, any advice, thoughts etc. greatly appreciated.

Thanks in advance.

Lynn

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