Setup Advice - maybe using Index/Match

[HWillis]

Hi there - I am after some advice on how to look at setting something up. Not really sure if Index/Match is the right way to go or if it would work?

I have a worksheet (A) that tracks everyday work items that have KPI due dates - all have a 'type' that uses vLookUp and other formulas that calculate the KPI dates. This is used daily.

Within the same workbook a different report may only require some certain 'type' and use say the first 4 columns info with additional columns for other information to have further input. I would like to keep this on a separate worksheet (B).

Is there a way to auto populate the worksheet (B) with the duplicate columns from worksheet (A)?

So if worksheet (A) had 'type' = 'apple', 'orange', 'pear'
worksheet (B) only required the 'apple' types + columns 2, 3, and 4 copied for each row it occurred for.

Hope this makes sense?! Any help would be greatly appreciated.

Many thanks, Helen.

 

[GraH]

Hi there are ways of doing this. This tutorial by Mike Girvin might work for you

 

[HWillis]

thank you - I will have a look and see if this is what I am looking for - many thanks for your reply and info.

 

[GraH]

You're welcome.

 

[HWillis]

Hi again - I am sorry but my skills are not up to this level - could you have a look at a simplified version please?
Many thanks in advance............ H.

NRTS example.xlsm
	A	B	C	D	E	F	G	H
1	Column D on NRTS worksheet
2	Task Work Type	if this is equal to any of these types on NRTS then copy columns A,B,D,E,F to REPORT2
3	Gateway - routine
4	Gateway - complex
5	Gateway - alterations
6
7
8
9	Date Received	Date Adequate	Task Work Type	PP No.	Description	#dwellings	#ongoing jobs	#construction jobs
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
REPORT2

Cells with Data Validation
Cell	Allow	Criteria
A3:A6	List	=Task_WorkType

NRTS example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3	Date Received	Date Adequate	Sign Off Level	Task Work Type	PP No.	Description	General Comments	CM9 FILE #	Accelo #	Acknowledgement Letter Sent	LGA	Planner	Current Location	DPE KPI (working days)	Northern KPI (days)	Days til overdue (DPE KPI then Northern KPI)	Due Date 1st Approver	Due Date RD	Northern Target Date	Due Date ED	DPE KPI Target Date	Date Completed
4	28/02/2020	28/02/2020	ED	Gateway - routine	PP_2019_CLARE_004_00	Lord Howe Island LEP 2010 Amendment No 6 - PP_2019_LHOWE_001_00		OBJ16/08878			Lord Howe Island	Rebecca Carpenter	ED	20	19	-9	13/03/2020	20/03/2020	26/03/2020	28/02/2020	27/03/2020
5	10/09/2019		RD	Gateway - routine	PP_2019_LISMO_007_00	Armidale Amendment 15 - PP_2019_AREGI_002_00 - Reducing Minimum Lot Size - 267 Long Swamp Road, Armidale	Further information being sought from Council about SEPP55 compliance - PP on hold 08/01/2020- Contamination report submitted 09/01/2020 - Advised Council to revise PP to include recommendations to contamination report and respond to questions about surrounding land	EF19/26313			Armidale Regional	Lucy Walker	Council	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
6	30/09/2019		RD	Gateway - routine	PP_2019_TWEED_005_00	Council Pound	Draft PP inadequate. Requested additional information. Council requested formal advice. Sent to 1st approver 05/11/19 08/11/2019 Letter sent to Council requesting revised draft	EF19/25525			Byron	Jenny Johnson	Council	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
7	18/11/2019	21/11/2019	ED	Gateway - complex	PP_2019_TWEED_004_00	P_2019_TWEED_004_00- Rezone Lot 1747 DP 1215252 in the Seabreeze Estate, Pottsville.	21/11/2019 - Determined to be adequate 24/02/2020 - Sent to TL for review	EF18/22749			Tweed	Rebecca Carpenter	COMPLETED	40	21	Complete	12/12/2019	20/12/2019	20/12/2019	16/01/2020	30/01/2020	6/03/2020
8	6/03/2020		RD	Gateway - routine		PP_2020_LISMO_001_00 - 389 Keen Street, East Lismore	10/03/2020- Add info requested. On hold.	EF20/9083			Lismore	Lucy Walker	Council	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
9	6/03/2020	11/03/2020	RD	Gateway - routine		PP_2020_BYRON_001_00 - rezone 54 Parrot Tree Place, Bangalow		EF20/9130			Byron	Jon Stone	COMPLETED	20	19	Complete	25/03/2020	1/04/2020	7/04/2020		8/04/2020	2/04/2020
10	10/03/2020		RD	Gateway - routine		PP_2020_BYRON_002_00 - Tiny House Development		EF20/18292			Byron	Jenny Johnson	Planner	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
11	10/03/2020		RD	Gateway - routine		PP_2020_BYRON_003_00 - Short-term Rental Accommodation	2020-03-19_Sent to policy team for review in re to inconsistency with SEPP Assessing adequacy	EF20/18293			Byron	Gina Davis	Policy Team	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
12	20/03/2020	20/03/2020	RD	s9.1 signoff		PP_2019_BALLI_003_00 - Lot 951 DP 1165266, 26 Boeing Avenue, Ballina		EF19/25612			Ballina		Planner	15	14	3	31/03/2020	3/04/2020	9/04/2020		14/04/2020
13	20/03/2020	20/03/2020	RD	s9.1 signoff		PP_2017_PORTM_008_00 - 4.4 signoff	2020-03-26_Sent to TL	EF17/13516			Port Macquarie Hastings	Gina Davis	COMPLETED	15	14	Complete	31/03/2020	3/04/2020	9/04/2020		14/04/2020	30/03/2020
14	23/03/2020		RD	Gateway - routine		PP_2020_CLARE_001_00 - Ulmarra-Nymboida Heritage Amendment		EF20/18763			Clarence Valley	Helen Willis	Council	20	19		13/01/1900	20/01/1900	26/01/1900		27/01/1900
15	16/09/2019	2/12/2019	RD	Gateway - routine	PP_2019_CLARE_004_00	- Part of Lot 102 DP1221192, Summerland Way, Koolkhan	11/12/2019 - Sent to TL	EF19/26541		yes	Clarence Valley	Rebecca Carpenter		20	19	-63	16/12/2019	6/01/2020	10/01/2020		13/01/2020
16	28/11/2019	2/12/2019	RD	Gateway - routine	PP_2019_BYRON_007_00	Byron LEP 2014 - - former Byron Bay Hospital Site	13/12/2019- Sent to TL 16/12/2019- Sent to RD 08/01/2020- Signed by RD	EF19/30431		Yes	Byron	Lucy Walker		20	19	-63	16/12/2019	6/01/2020	10/01/2020		13/01/2020
17	5/12/2019	9/12/2019	RD	Gateway - routine	PP_2019_TWEED_007_00	- Tweed Bowl's Club	18/12/2019 - Sent to TL 18/12/2019 - Sent to RD	EF19/30784		Yes	Tweed	Lucy Walker		20	19	-58	6/01/2020	13/01/2020	17/01/2020		20/01/2020
18	2/12/2019	5/12/2019	RD	Gateway - routine	PP_2019_LISMO_007_00	- Schedule 1 Amendment to permit a General Industry (erosion mat manufacturing) at 464 Broadwater Road, DUNGARUBBA - S-516	19/12/19 sent to TL 19/12/19 sent to RD	EF19/30538			Lismore	Helen Willis		20	19	-60	19/12/2019	9/01/2020	15/01/2020		16/01/2020
19	5/12/2019	5/12/2019	RD	Gateway - routine	PP_2019_BALLI_005_00	- Rural Industry Definition Change	18/12/2019 - Sent to TL 19/12/2020 - Sent to RD 14/01/2020 - GD approved	EF19/30672			Ballina	Kate Campbell		20	19	-60	19/12/2019	9/01/2020	15/01/2020		16/01/2020
20	15/12/2019	15/12/2019	RD	s9.1 signoff		Kyogle Amendment 13 - 4.4 signoff	RFS raised no objections	OBJ17/04525			Kyogle	Jon Stone		15	14	-59	7/01/2020	10/01/2020	16/01/2020		17/01/2020
21
22
23
24
NRTS

Cell Formulas
Range	Formula
N4:N24	N4	=IFERROR(VLOOKUP([Task Work Type],KPI_workingdays,2,FALSE),"")
O4:O24	O4	=IFERROR(VLOOKUP([Task Work Type],KPI_workingdays,3,FALSE),"")
P4:P24	P4	=IF(B4="","",IF(V4="",IF(U4="",NETWORKDAYS(TODAY(),S4,Holidays),NETWORKDAYS(TODAY(),U4,Holidays)),"Complete"))
Q4:Q24	Q4	=IFERROR(IF(OR([@[Sign Off Level]]="1st App",[@[Sign Off Level]]="RD",[@[Sign Off Level]]="ED"),WORKDAY([@[Date Adequate]],VLOOKUP([Task Work Type],KPI_workingdays,4,FALSE),Holidays),""),"")
R4:R24	R4	=(IF(OR([@[Sign Off Level]]="RD",[@[Sign Off Level]]="ED"),WORKDAY([@[Date Adequate]],VLOOKUP([Task Work Type],KPI_workingdays,5,FALSE),Holidays),""))
S4:S24	S4	=IFERROR(WORKDAY([@[Date Adequate]],[@[Northern KPI (days)]],Holidays),"")
T4:T24	T4	=IFERROR(IF(OR([@[Sign Off Level]]="ED"),WORKDAY([@[Date Adequate]],VLOOKUP([Task Work Type],KPI_workingdays,6,FALSE),Holidays),""),"")
U4:U24	U4	=IF([DPE KPI (working days)]=0,"",IFERROR(WORKDAY([@[Date Adequate]],[@[DPE KPI (working days)]],Holidays),""))

Named Ranges
Name	Refers To	Cells
Holidays	=Lists!$O$2:$O$67	Q4:U24
KPI_workingdays	=Lists!$A$2:$F$31	T4:T24, Q4:R24, N4:O24
Task_WorkType	=Lists!$A$2:$A$31	T4:T24, Q4:R24, N4:O24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P4:P50	Cell Value	=""	text	NO
P4:P50	Cell Value	>10	text	NO
R4:R24	Dates Occurring	this week	text	NO
R4:R24	Dates Occurring	next week	text	NO
R4:R24	Dates Occurring	last week	text	NO
P4:P50	Cell Value	between 5 and 10	text	NO
P4:P50	Cell Value	<5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C4:C50	List	=SignOffLevel
D4:D50	List	=Task_WorkType
J4:J50	List	=Yes_No
K4:K50	List	=LGA
L4:L50	List	=Planner
M4:M50	List	=CurrentLocation

 

[GraH]

Perhaps I can convince you to try Power Query/Get & Transform? What is your Excel version? Any version from 2010 and above on Windows should work (+ IE v9, if I"m not mistaken).
Don't let it scare you, as all, except 1 step, only involves some mouse clicks. No coding required.

1. In 2016 (365 subscription) start from the data ribbon, section "Get & Transform Data"
2. Select a cell from the data range
3. then click From Table/Range (your table has headers, so flag that)
4. PQ windows opens
5. Filter the column like you would in Excel (hardcoding now, we'll fix that later)
6. get rid of the columns you do not need in your result table, if any. Simply select them, right click with the mouse and select Delete Column from the context menu
7. load to excel as table, where you want it to land

If new data is added to your original table, then a refresh if this new table will work.

Making it respond to your selection requires a bit of extra work.

1. Select your task work type (A2:A6)
2. Again load from table/range and remember the headers
3. On the Transformation ribbon, select "Convert To List"
4. Name your query ListToFilter or something of the like
5. Save and load as "Only create connection"

Last few steps

1. On the data ribbon, click Queries and Connections" to open the Query Pane
2. You will see both your queries
3. Select the first one, click right and select "Edit"
4. Find your "Filter" step in the "Applied steps" section. If you do not see that, go to "view" and click the "Query Settings". Then it should show.
5. Delete this filter step (click red cross before the applied step)
6. Add a Custom Column via the ribbon "Add Column"
7. In the formula section type List.Contains(ListToFilter,[Task Work Type]) -> it will return true/false. See I've used the name of the query that holds your list of selection criteria.
8. Filter your table again on this column with "TRUE".
9. Save and load

Replace your selections via the drop down and then press refresh. If all goes well, the table is filtered accordingly. Let me know if you need more help.

 

[HWillis]

This is pretty amazing stuff. Thank you. I have everything working but - can the ListToFilter be dynamic. I have tried researching this but I am not sure.

So in my original workbook my Task Work Type is 30 items - can I use this as the ListToFilter based on what is filtered/selected each time someone needs to run the report?

Thank you so much for your help....... H.

 

[GraH]

This is pretty amazing stuff.

I know ...

I have everything working but - can the ListToFilter be dynamic. I have tried researching this but I am not sure.
So in my original workbook my Task Work Type is 30 items - can I use this as the ListToFilter based on what is filtered/selected each time someone needs to run the report?

Cool you made it that far. Thumps up already.
We need to elaborate on this. How exactly is this list being used by the person running the report? The offered solution may require a tweak or two.

Thank you so much for your help....... H.

More then welcome, it is why these types of fora exist. Don't forget to hit the like button

 

[HWillis]

We have a reference group going atm - after a meeting last Friday a new template has come out this morning - I will check out the new version everyone has agreed on and see what I need to sort.
Many thanks again for your continued help. Hope you had a lovely Easter.