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]

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

Totals Sheet

1. Your"Starting Numbers" must be as of end of the day before the first date on this sheet.
2. The dates must be in ascending order and with no gaps.

Unit Flow Sheet

1. Any Unit Flow date before the earliest date in Totals will be ignored (because they should be represented in the Totals "Starting Number").
2. Any Unit Flow date after the last date of the Totals sheet will be ignored.
3. All other dates will be accounted for, including duplicate dates (e.g. 4 entries for a single date).
4. You can have gaps between dates.
5. The dates can be in any sequence.

 

[nckwnchstr]

Awesome

Thank you so much for the help!!

 

[Toadstool]

You're welcome!