#16
|
|||
|
|||
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 ) 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. |
#17
|
|||
|
|||
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] 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) |
#18
|
|||
|
|||
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 |