Another convoluted Can I do this question ...

[nckwnchstr]

Good morning, or afternoon.

I'm trying to figure out how to link 2 diffent sheets together, with several different tables / columns. The purpose behind this is to have a real-time tracker for housing where i'm at, and to limit the number of places i have to input data to mitigate human error or time spent changing things.

What I have to work with is something similar to this:

A B C D E F G H

1	Date	PAX	HA-1	HA-2	HA-3	HA-4	HA-5	HA-6
2	23 July 20	34	+30			+4
3	24 July 20	25			+25
4	25 July 20	65		-65
5	26 July 20	125			-25	-100
6	27 July 20	100					+50	+50
7	28 July 20	56	+6	+50
8	29 July 20	37	+37
9	30 July 20	89		+89
10	31 July 20	54				+54

(sheet is labeled Unit Flow, and cell labeled as Date is cell A1)

I'd like to be able to link a sheet laid out like the one above with one that a corresponding table on a sheet labeled Totals. There are 4 very large tables that have a lot of data in them, but for simplicity sake it will look like this


AZ BA BD BG BJ BM BP

1	Date	HA-1	HA-2	HA-3	HA-4	HA-5	HA-6
2	23 July 20	400 (will add 30)	525	467	511 (will add 4)	125	254
3	24 July 20	430	525	467 (will add 25)	515	125	254
4	25 July 20	430	525 (will subtract 65)	492	515	125	254
5	26 July 20	430	460	492(will subract 25)	515 (will subtract 100)	125	254
6	27 July 20	430	460	467	415	125 (will add 50)	254 (will add 50)
7	28 July 20	430 (will add 6)	460 (will add 50)	467	415	175	304
8	29 July 20	436 (will add 37)	510	467	415	175	304
9	30 July 20	473	510 (will add 89)	467	415	175	304
10	31 July 20	473	599	467	415 (will add 54)	175	304

What I would like is for the Totals sheet to be linked to the Unit Flow sheet so that it automatically adds or subtracts the corresponding number to the correct cell that correlates on the Tables sheet. If there is no data in the Unit Flow cell i need the data in the Tables sheet to continue to populate to the cell below it with the current number reflecting no addition or subtraction. These tables are linked to several bar and line graphs that project unit flow out for as long as i have data (right now they're out to 30 SEP 2020), and i want to only have to update the Unit Flow chart and have the bar graphs and line graphs all read the tables and update accordingly.

I dont know if this is possible, I'd like to think it is, but I just have no idea how to make this happen. I'm trying to set this up as a long term product for area I'm currently deployed to, and it's definitely well beyond my EXCEL knowledge base.

Thank you very much for any assistance possible!

 

[Toadstool]

Hi NckWnchstr,

A cell can either contain a value or a formula so for Totals cell BA3 it can be a formula or the number 430.

Maybe the VBA guys could offer a solution but using just formulae you'd need a re-design. I'd suggest an extra row on the Totals giving the starting position (which would appear to be the values from 22 July 2020) and then the formulae below would perform the calculation using that as the starting position +- the values from Unit Flow.

My example below doesn't have error checking so would need all HA-n headings and dates present, but you could easily wrap an IFERROR(...,0) around the INDEX so it avoided generating an N/A.

Book1
	A	B	C	D	E	F	G	H
1	Date	PAX	HA-1	HA-2	HA-3	HA-4	HA-5	HA-6
2	23-Jul-20	34	30			4
3	24-Jul-20	25			25
4	25-Jul-20	65		-65
5	26-Jul-20	125			-25	-100
6	27-Jul-20	100					50	50
7	28-Jul-20	56	6	50
8	29-Jul-20	37	37
9	30-Jul-20	89		89
10	31-Jul-20	54				54
Unit Flow

Cell Formulas
Range	Formula
B2:B10	B2	=ABS(SUM(C2:H2))

Book1
	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
1	Date	HA-1			HA-2			HA-3			HA-4			HA-5			HA-6
2	START	400			525			467			511			125			254
3	23-Jul-20	430			525			467			515			125			254
4	24-Jul-20	430			525			492			515			125			254
5	25-Jul-20	430			460			492			515			125			254
6	26-Jul-20	430			460			467			415			125			254
7	27-Jul-20	430			460			467			415			175			304
8	28-Jul-20	436			510			467			415			175			304
9	29-Jul-20	473			510			467			415			175			304
10	30-Jul-20	473			599			467			415			175			304
11	31-Jul-20	473			599			467			469			175			304
Totals

Cell Formulas
Range	Formula
BP3:BP11,BM3:BM11,BJ3:BJ11,BG3:BG11,BD3:BD11,BA3:BA11	BA3	=BA2+INDEX('Unit Flow'!$C$2:$H$999,MATCH($AZ3,'Unit Flow'!$A$2:$A$999,0),MATCH(BA$1,'Unit Flow'!$C$1:$H$1,0))

 

[nckwnchstr]

Will this match a date? or will it match line for line?

I lined up the graph with matching date rows, but we'll have times where we go 2 weeks with no movement, and then we'll get a big push of movement for a few days and then it will slack off.

Would the second formula be =BA2+INDEX('Unit Flow'!$C$2:$H$999,MATCH(TODAY())($AZ3,'Unit Flow'!$A$2:$A$999,0),MATCH(BA$1,'Unit Flow'!$C$1:$H$1,0))?

That way if my date goes from 28-Jul-20 to the next date being 4-Aug-20 it will skip down to that date and continue?

Thanks again!

 

[Toadstool]

It matches by date, if one exists.
If you change to use only TODAY() then the totals won't be correct as you'll miss any previous entries from Unit Flow.

I don't know which table you mean for "we'll have times where we go 2 weeks with no movement" but if it's the Unit Flow then that's OK, you can omit rows and dates which have no values but you need that IFERROR code so the running total is maintained.

NckWnchstr-3.xlsx
	A	B	C	D	E	F	G	H
1	Date	PAX	HA-1	HA-2	HA-3	HA-4	HA-5	HA-6
2	23-Jul-20	34	30			4
3	24-Jul-20	25			25
4	25-Jul-20	65		-65
5	26-Jul-20	125			-25	-100
6	27-Jul-20	100					50	50
7	28-Jul-20	56	6	50
8	29-Jul-20	37	37
9	30-Jul-20	89		89
10	31-Jul-20	54				54
11	4-Aug-20	21	1	2	3	4	5	6
Unit Flow

Cell Formulas
Range	Formula
B2:B11	B2	=ABS(SUM(C2:H2))

NckWnchstr-3.xlsx
	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
1	Date	HA-1			HA-2			HA-3			HA-4			HA-5			HA-6
2	START	400			525			467			511			125			254
3	23-Jul-20	430			525			467			515			125			254
4	24-Jul-20	430			525			492			515			125			254
5	25-Jul-20	430			460			492			515			125			254
6	26-Jul-20	430			460			467			415			125			254
7	27-Jul-20	430			460			467			415			175			304
8	28-Jul-20	436			510			467			415			175			304
9	29-Jul-20	473			510			467			415			175			304
10	30-Jul-20	473			599			467			415			175			304
11	31-Jul-20	473			599			467			469			175			304
12	01-Aug-20	473			599			467			469			175			304
13	02-Aug-20	473			599			467			469			175			304
14	03-Aug-20	473			599			467			469			175			304
15	04-Aug-20	474			601			470			473			180			310
16	05-Aug-20	474			601			470			473			180			310
17	06-Aug-20	474			601			470			473			180			310
Totals

Cell Formulas
Range	Formula
BP3:BP17,BM3:BM17,BJ3:BJ17,BG3:BG17,BD3:BD17,BA3:BA17	BA3	=BA2+IFERROR(INDEX('Unit Flow'!$C$2:$H$999,MATCH($AZ3,'Unit Flow'!$A$2:$A$999,0),MATCH(BA$1,'Unit Flow'!$C$1:$H$1,0)),0)

 

[nckwnchstr]

I attempted to use the formula you gave me, and my EXCEL (2016) is throwing a fit and wont let it actually work. I changed the cell values to match what I have in my spreadsheet (both tables are copied, in the first spreadsheet the tab is labeled Totals. I attempted to change the BA-1 number under the 23-Jul-20 date. I also linked the second sheet which is labeled Unit Flow. I'm not exactly sure where I went wrong in this. I've looked at it a few times, but it just gives me the C1 total. Did i fat finger something in the formula? I also attempted to re-create it by selecting everything, and it will highlight cells on the main Totals page, but when im in the Unit Flow table, nothing shows up as highlighted.

Housing Graph V2.0 (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	STARTING NUMBERS		453			431			433			419			503
2	Building Area		BA-1	BA-1 Max	BA-1 Daily %	BA-2	BA-2 Max	BA-2 Daily %	BA-3	BA-3 Max	BA-3 Daily %	BA-4	BA-4 Max	BA-4 Daily %	BA-5
3	Date	7-Jul-20	429	542	79.15	493	570	86.49	498	542	91.88	411	446	92.15	503
4		8-Jul-20	428	542	78.97	489	570	85.79	498	542	91.88	413	446	92.60	510
5		9-Jul-20	425	542	78.41	489	570	85.79	497	542	91.70	412	446	92.38	511
6		10-Jul-20	425	542	78.41	489	570	85.79	495	542	91.33	412	446	92.38	520
7		11-Jul-20	425	542	78.41	488	570	85.61	493	542	90.96	411	446	92.15	517
8		12-Jul-20	428	542	78.97	514	570	90.18	498	542	91.88	413	446	92.60	510
9		13-Jul-20	425	542	78.41	482	570	84.56	482	542	88.93	410	446	91.93	458
10		14-Jul-20	425	542	78.41	482	570	84.56	482	542	88.93	410	446	91.93	455
11		15-Jul-20	425	542	78.41	482	570	84.56	474	542	87.45	413	446	92.60	472
12		16-Jul-20	425	542	78.41	482	570	84.56	470	542	86.72	413	446	92.60	472
13		17-Jul-20	425	542	78.41	481	570	84.39	457	542	84.32	413	446	92.60	472
14		18-Jul-20	425	542	78.41	481	570	84.39	457	542	84.32	413	446	92.60	472
15		19-Jul-20	425	542	78.41	481	570	84.39	457	542	84.32	413	446	92.60	472
16		20-Jul-20	425	542	78.41	481	570	84.39	457	542	84.32	413	446	92.60	472
17		21-Jul-20	425	542	78.41	456	570	80.00	441	542	81.37	414	446	92.83	497
18		22-Jul-20	425	542	78.41	455	570	79.82	441	542	81.37	415	446	93.05	497
19		23-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
20		24-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
21		25-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
22		26-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
23		27-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
24		28-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
25		29-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
26		30-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
27		31-Jul-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
28		1-Aug-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
29		2-Aug-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
30		3-Aug-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
31		4-Aug-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
32		5-Aug-20	453	542	83.58	431	570	75.61	433	542	79.89	419	446	93.95	503
Totals

Cell Formulas
Range	Formula
J3:J32,D3:D32	D3	=(ABS(542))
N3:N32,K3:K32,H3:H32,E3:E32	E3	=(C3/D3)*100
G3:G32	G3	=(ABS(570))
M3:M32	M3	=(ABS(446))
C19	C19	=C1+IFERROR(INDEX('Unit Flow'!$E$6:$AC$999,MATCH($B19,'Unit Flow'!$B$6:$B$9999,0),MATCH(C$2,'Unit Flow'!$E$2:$AC$2,0)),0)

Housing Graph V2.0 (1).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	ARRIVAL	PAX	Housing Loction / PAX	TP1	TP2	TP3	BA1	BA2	BA3	BA4	BA5	BA6	BA7	BA8	BA9	PAD 16	PAD17	PAD17C	PAD 19	PAD 20	PAD21	PAD25	PAD26	PAD27	PAD28	PAD29	PAD31	PAD32
3	8-Jul-20	142	TP-1	-42						-100
4	10-Jul-20	25	BA 2					25
5	12-Jul-20	138	TP-1	-138
6	23-Jul-20	300	PAD-16				300
7	12-Jul-20	0	BA 9
8	15-Jul-20	0	BA 9
9	21-Aug-20	150	BA 7, 8								-50		-100
10	21-Aug-20	150	PAD 16													150
11	16-Jul-20	150	BA 6													-150
12	19-Jul-20	0	PAD 16
13	20-Jul-20	0	BA 8
14	26-Jul-20	0	BA 4,5,6,7,
15	28-Jul-20	0	BA 8
16	30-Jul-20	0	BA 9
17	5-Aug-20	0	PAD 16/19
18	8-Aug-20	0
19	28-Aug-20	154	PAD 21						100				54
20		0	PAD 19
21	30-Jul-20	138	PAD 16													-138
22	22-Aug-20	0	BA5, 6, 7
Unit Flow

Cell Formulas
Range	Formula
C3:C22	C3	=ABS(SUM(E3:AC3))

 

[Toadstool]

I can't see why you've removed all formulae except for 23 July?

INDEX works by taking a range then retrieving a specified row and column content. My formula is trying to retrieve any Unit Flow value for a specified Date and Building Area.
Your INDEX goes into 'Unit Flow'!$E$6:$AC$999 but the possible numbers start at E3 across to AC and whatever row you want so this should be 'Unit Flow'!$E$3:$AC$999.
The first MATCH finds the row so once the INDEX array is fixed this should work.
The second MATCH finds which column contains a matching Building Area but your search range was changed to 'Unit Flow'!$E$2:$AC$2 so this will error as MATCH will only search one row or one column.

Unit Flow
I see Unit Flow has missing dates and dates out of sequence, but that's OK as we can work with that so I've made no changes to your sheet.

Totals
You show BA-1 starting number as 453 but on 7 July it's reduced by -24 to 429. There is a missing Unit Flow entry for 7 July so this will not work. NOTE: If you remove Unit Flow entries then you must first reset the Starting Numbers on the Totals as at the last date removed (as subsequent dates will be accounted for). I've assumed your ending values for 7 July were correct and used them as the current Starting Numbers and deleted the 7 July from Totals.

A MATCH on Building Area is used to figure which column in Unit Flow to search, so the format must be the same. I've changed Building Area "BA-1" to "BA1" so it matches, and the same for other BAs.

I don't understand the use of ABSolute in the Max columns so I've changed it to use the MAX formula.

Here's the new Totals tab:

NckWnchstr-4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Building Area		BA1	BA-1 Max	BA-1 Daily %	BA2	BA-2 Max	BA-2 Daily %	BA3	BA-3 Max	BA-3 Daily %	BA4	BA-4 Max	BA-4 Daily %	BA5
2	STARTING NUMBERS		429			493			498			411			503
3	Date	08-Jul-20	429	729	58.85	493	518	95.17	498	498	100.00	311	411	75.67	503
4		09-Jul-20	429	729	58.85	493	518	95.17	498	498	100.00	311	411	75.67	503
5		10-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
6		11-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
7		12-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
8		13-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
9		14-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
10		15-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
11		16-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
12		17-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
13		18-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
14		19-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
15		20-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
16		21-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
17		22-Jul-20	429	729	58.85	518	518	100.00	498	498	100.00	311	411	75.67	503
18		23-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
19		24-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
20		25-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
21		26-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
22		27-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
23		28-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
24		29-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
25		30-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
26		31-Jul-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
27		01-Aug-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
28		02-Aug-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
29		03-Aug-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
30		04-Aug-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
31		05-Aug-20	729	729	100.00	518	518	100.00	498	498	100.00	311	411	75.67	503
Totals

Cell Formulas
Range	Formula
C3:C31,O3:O31,L3:L31,I3:I31,F3:F31	C3	=C2+IFERROR(INDEX('Unit Flow'!$E$3:$AC$999,MATCH($B3,'Unit Flow'!$B$3:$B$999,0),MATCH(C$1,'Unit Flow'!$E$2:$AC$2,0)),0)
D3:D31,M3:M31,J3:J31,G3:G31	D3	=MAX(C$2:C$999)
E3:E31,N3:N31,K3:K31,H3:H31	E3	=(C3/D3)*100

 

[nckwnchstr]

Thanks!!

You're an absolute life saver!!

the ABS(###) callout was to prevent any issues with the drag & drop or double click function to continue the numbers in that column since thats a maximum capacity callout.

I had tried the formula in one cell rather than copy it into every cell right away, this formula (as well as the previous one you helped me with) are way beyond anything i understand about EXCEL. I wanted to try one cell and try and make it work before i altered the whole workbook and attempt it. Took a little tweaking and massaging and adjusting to my original file, but i was able to finally get it to work correctly.

I cant thank you enough for the help!!

 

[nckwnchstr]

Hello,

Sorry for the issue with the message, what i'm trying to work out is if i have 2 dates that are the same in the second sheet, how can i get them to read on the same date and update accordingly? Right now, the Totals spreadsheet is reading 2 dates and erroring out and will not input data. It is causing issues and will not give us an accurate picture on that specific date, We will have several movements on each day in the next few months, and i'm hoping to be able to avoid having to do the math or having to show an inaccurate picture by showing a move a day early or a day late.

Is this possible?

Housing Graph Official.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Building Area		BA-1	BA-1 Max	BA-1 Daily %	BA-2	BA-2 Max	BA-2 Daily %	BA-3	BA-3 Max	BA-3 Daily %	BA-4	BA-4 Max	BA-4 Daily %	BA-5	BA-5 Max	BA-5 Daily %
2	STARTING NUMBERS		494			28			0			355			522
3	Date	3-Aug-20	494	542	91.14	28	570	4.91	0	542	0.00	355	446	79.60	522	632	82.59
4		4-Aug-20	1494	542	275.65	1028	570	180.35	1000	542	184.50	1355	446	303.81	522	632	82.59
5		5-Aug-20	494	542	91.14	28	570	4.91	0	542	0.00	355	446	79.60	-478	632	-75.63
6		6-Aug-20	293	542	54.06	-72	570	-12.63	100	542	18.45	355	446	79.60	-478	632	-75.63
7		7-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
8		8-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
9		9-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
10		10-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
11		11-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
12		12-Aug-20	416	542	76.75	-18	570	-3.16	188	542	34.69	1011	446	226.68	-466	632	-73.73
13		13-Aug-20	661	542	121.96	82	570	14.39	233	542	42.99	1076	446	241.26	-441	632	-69.78
14		14-Aug-20	666	542	122.88	86	570	15.09	254	542	46.86	1165	446	261.21	-439	632	-69.46
15		15-Aug-20	691	542	127.49	86	570	15.09	254	542	46.86	1165	446	261.21	-439	632	-69.46
16		16-Aug-20	667	542	123.06	86	570	15.09	254	542	46.86	1165	446	261.21	-439	632	-69.46
17		17-Aug-20	667	542	123.06	86	570	15.09	254	542	46.86	1165	446	261.21	-439	632	-69.46
18		18-Aug-20	722	542	133.21	86	570	15.09	254	542	46.86	1165	446	261.21	-439	632	-69.46
19		19-Aug-20	937	542	172.88	86	570	15.09	277	542	51.11	1165	446	261.21	-439	632	-69.46
20		20-Aug-20	1191	542	219.74	86	570	15.09	277	542	51.11	1165	446	261.21	-439	632	-69.46
21		21-Aug-20	1091	542	201.29	86	570	15.09	277	542	51.11	1165	446	261.21	109	632	17.25
22		22-Aug-20	1091	542	201.29	86	570	15.09	277	542	51.11	1165	446	261.21	109	632	17.25
23		23-Aug-20	1203	542	221.96	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
24		24-Aug-20	1248	542	230.26	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
25		25-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
26		26-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
27		27-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
28		28-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
29		29-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
30		30-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
31		31-Aug-20	1493	542	275.46	-39	570	-6.84	331	542	61.07	1222	446	273.99	109	632	17.25
Totals

Cell Formulas
Range	Formula
C3:C31,O3:O31,L3:L31,I3:I31,F3:F31	C3	=C2+IFERROR(INDEX('Unit Flow'!$E$3:$AE$9999,MATCH($B3,'Unit Flow'!$B$3:$B$9880,0),MATCH(C$1,'Unit Flow'!$E$2:$AE$2,0)),0)
D3:D31,J3:J31	D3	=(ABS(542))
E3:E31,Q3:Q31,N3:N31,K3:K31,H3:H31	E3	=(C3/D3)*100
G3:G31	G3	=(ABS(570))
M3:M31	M3	=(ABS(446))
P3:P31	P3	=(ABS(632))

Housing Graph Official.xlsx
	A	B	C	D	E	F	G	H	I
1
2	UNIT	DATE	PAX	Housing Loction / PAX	BA-1	BA-2	BA-3	BA-4	BA-5
3		4-Aug-20	4000		1000	1000	1000	1000
4		5-Aug-20	5000		-1000	-1000	-1000	-1000	-1000
5		5-Aug-20	3000				1000	1000	1000
6		6-Aug-20	201		-201	-100	100
7		7-Aug-20	933		123	54	88	656	12
8		8-Aug-20	0
9		9-Aug-20	0
10		10-Aug-20	0
11		11-Aug-20	0
12		13-Aug-20	480		245	100	45	65	25
13		13-Aug-20	345		-200	-40	-20	-55	-30
14		14-Aug-20	121		5	4	21	89	2
15		15-Aug-20	25		25
16		16-Aug-20	24		-24
17		16-Aug-20	602		548			54
18		18-Aug-20	55		55
19		19-Aug-20	238		215		23
20		20-Aug-20	254		254
21		21-Aug-20	448		-100				548
22		21-Aug-20	205		-220		15
23		23-Aug-20	98		112	-125	54	57
24		24-Aug-20	45		45
25		25-Aug-20	245		245
26		25-Aug-20	0
27		27-Aug-20	0
28		28-Aug-20	0
29		29-Aug-20	0
30		29-Aug-20	0
31		31-Aug-20	0
Unit Flow

Cell Formulas
Range	Formula
C3:C31	C3	=ABS(SUM(E3:AC3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B34	Expression	=IFBLANK(B:B)	text	NO
B1:B2,B35:B1048576	Expression	=IFBLANK(B:B)	text	NO

Thanks!

Nick

 

[Toadstool]

Hi Nick,

I've not changed the Unit Flow tab at all. I've only changed the Totals tab columns C, F, I, L and O.

NckWnchstr-6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Building Area		BA-1	BA-1 Max	BA-1 Daily %	BA-2	BA-2 Max	BA-2 Daily %	BA-3	BA-3 Max	BA-3 Daily %	BA-4	BA-4 Max	BA-4 Daily %	BA-5	BA-5 Max	BA-5 Daily %
2	STARTING NUMBERS		494			28			0			355			522
3	Date	8/3/2020	494	542	91.14391	28	570	4.912281	0	542	0	355	446	79.59641	522	632	82.59494
4		8/4/2020	1494	542	275.6458	1028	570	180.3509	1000	542	184.5018	1355	446	303.8117	522	632	82.59494
5		8/5/2020	494	542	91.14391	28	570	4.912281	1000	542	184.5018	1355	446	303.8117	522	632	82.59494
6		8/6/2020	293	542	54.05904	-72	570	-12.6316	1100	542	202.952	1355	446	303.8117	522	632	82.59494
7		8/7/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
8		8/8/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
9		8/9/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
10		8/10/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
11		8/11/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
12		8/12/2020	416	542	76.75277	-18	570	-3.15789	1188	542	219.1882	2011	446	450.8969	534	632	84.49367
13		8/13/2020	461	542	85.05535	42	570	7.368421	1213	542	223.8007	2021	446	453.139	529	632	83.70253
14		8/14/2020	466	542	85.97786	46	570	8.070175	1234	542	227.6753	2110	446	473.0942	531	632	84.01899
15		8/15/2020	491	542	90.59041	46	570	8.070175	1234	542	227.6753	2110	446	473.0942	531	632	84.01899
Totals

Cell Formulas
Range	Formula
C3:C15,O3:O15,L3:L15,I3:I15,F3:F15	C3	=C2+SUMPRODUCT(('Unit Flow'!$B$3:$B$9999=$B3)*('Unit Flow'!$E$2:$Z$2=C$1),'Unit Flow'!$E$3:$Z$9999)
D3:D15,J3:J15	D3	=(ABS(542))
E3:E15,Q3:Q15,N3:N15,K3:K15,H3:H15	E3	=(C3/D3)*100
G3:G15	G3	=(ABS(570))
M3:M15	M3	=(ABS(446))
P3:P15	P3	=(ABS(632))

 

[nckwnchstr]

Will that still match it by date and add or subtract it accordingly? For example if it skips from Aug 6 to Aug 15th with no other moves happening between those dates? If im reading it correctly it should, but just want to make sure.

Thanks again for the help, definitely couldn't have gotten this far without your help