Hi there.
Is there an easier way to set up to read data from separate tabs than the below formulas? There is a DS and NS for every day of the month, but the values I want to pull are in identical columns/rows. I don't particularly want to set up a summary for each day if I can avoid it, I'd rather set up a single summary and be able to change the tab data (DS (1) to say DS (2)) but not sure if you can. Any help would be appreciated or a point in the right direction. Thanks!
Is there an easier way to set up to read data from separate tabs than the below formulas? There is a DS and NS for every day of the month, but the values I want to pull are in identical columns/rows. I don't particularly want to set up a summary for each day if I can avoid it, I'd rather set up a single summary and be able to change the tab data (DS (1) to say DS (2)) but not sure if you can. Any help would be appreciated or a point in the right direction. Thanks!
2022-2 Feb - 54 Workshop Handover.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
Q | R | S | T | U | V | W | |||
3 | Vehicles | (1) Summary | (2) Summary | ||||||
4 | TRU002 | 10 | GTG | GTG | |||||
5 | TRU013 | 12 | 2 | OUT | OUT | ||||
6 | TRU039 | 12 | 12 | GTG | 12 | 12 | GTG | ||
7 | TRU046 | GTG | 3 | GTG | |||||
8 | TRU047 | GTG | GTG | ||||||
9 | TRU054 | 2 | GTG | 3 | 7 | GTG | |||
10 | TRU055 | 2 | GTG | GTG | |||||
11 | 8156 | 4 | GTG | 1.5 | 4 | GTG | |||
12 | 8158 | 3 | GTG | 11 | 12 | GTG | |||
13 | 8159 | 12 | 12 | OUT | 6.5 | 7 | OUT | ||
14 | DRI004 | 1 | GTG | 8 | GTG | ||||
Availability |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R4 | R4 | =IF(SUM('DS (1)'!$S$23:$T$25)=0,"",SUM('DS (1)'!$S$23:$T$25)) |
S4 | S4 | =IF(SUM('NS (1)'!$S$23:$T$25)=0,"",SUM('NS (1)'!$S$23:$T$25)) |
T4,W4 | T4 | ='NS (1)'!$U$11 |
U4 | U4 | =IF(SUM('DS (2)'!$S$23:$T$25)=0,"",SUM('DS (2)'!$S$23:$T$25)) |
V4 | V4 | =IF(SUM('NS (2)'!$S$23:$T$25)=0,"",SUM('NS (2)'!$S$23:$T$25)) |
R5 | R5 | =IF(SUM('DS (1)'!$S$26:$T$28)=0,"",SUM('DS (1)'!$S$26:$T$28)) |
S5 | S5 | =IF(SUM('NS (1)'!$S$26:$T$28)=0,"",SUM('NS (1)'!$S$26:$T$28)) |
T5,W5 | T5 | ='NS (1)'!$U$14 |
U5 | U5 | =IF(SUM('DS (2)'!$S$26:$T$28)=0,"",SUM('DS (2)'!$S$26:$T$28)) |
V5 | V5 | =IF(SUM('NS (2)'!$S$26:$T$28)=0,"",SUM('NS (2)'!$S$26:$T$28)) |
R6 | R6 | =IF(SUM('DS (1)'!$S$29:$T$31)=0,"",SUM('DS (1)'!$S$29:$T$31)) |
S6 | S6 | =IF(SUM('NS (1)'!$S$29:$T$31)=0,"",SUM('NS (1)'!$S$29:$T$31)) |
T6,W6 | T6 | ='NS (1)'!$U$17 |
U6 | U6 | =IF(SUM('DS (2)'!$S$29:$T$31)=0,"",SUM('DS (2)'!$S$29:$T$31)) |
V6 | V6 | =IF(SUM('NS (2)'!$S$29:$T$31)=0,"",SUM('NS (2)'!$S$29:$T$31)) |
R7 | R7 | =IF(SUM('DS (1)'!$S$32:$T$34)=0,"",SUM('DS (1)'!$S$32:$T$34)) |
S7 | S7 | =IF(SUM('NS (1)'!$S$32:$T$34)=0,"",SUM('NS (1)'!$S$32:$T$34)) |
T7,W7 | T7 | ='NS (1)'!$U$20 |
U7 | U7 | =IF(SUM('DS (2)'!$S$32:$T$34)=0,"",SUM('DS (2)'!$S$32:$T$34)) |
V7 | V7 | =IF(SUM('NS (2)'!$S$32:$T$34)=0,"",SUM('NS (2)'!$S$32:$T$34)) |
R8 | R8 | =IF(SUM('DS (1)'!$S$35:$T$37)=0,"",SUM('DS (1)'!$S$35:$T$37)) |
S8 | S8 | =IF(SUM('NS (1)'!$S$35:$T$37)=0,"",SUM('NS (1)'!$S$35:$T$37)) |
T8,W8 | T8 | ='NS (1)'!$U$23 |
U8 | U8 | =IF(SUM('DS (2)'!$S$35:$T$37)=0,"",SUM('DS (2)'!$S$35:$T$37)) |
V8 | V8 | =IF(SUM('NS (2)'!$S$35:$T$37)=0,"",SUM('NS (2)'!$S$35:$T$37)) |
R9 | R9 | =IF(SUM('DS (1)'!$S$38:$T$40)=0,"",SUM('DS (1)'!$S$38:$T$40)) |
S9 | S9 | =IF(SUM('NS (1)'!$S$38:$T$40)=0,"",SUM('NS (1)'!$S$38:$T$40)) |
T9,W9 | T9 | ='NS (1)'!$U$26 |
U9 | U9 | =IF(SUM('DS (2)'!$S$38:$T$40)=0,"",SUM('DS (2)'!$S$38:$T$40)) |
V9 | V9 | =IF(SUM('NS (2)'!$S$38:$T$40)=0,"",SUM('NS (2)'!$S$38:$T$40)) |
R10 | R10 | =IF(SUM('DS (1)'!$S$41:$T$43)=0,"",SUM('DS (1)'!$S$41:$T$43)) |
S10 | S10 | =IF(SUM('NS (1)'!$S$41:$T$43)=0,"",SUM('NS (1)'!$S$41:$T$43)) |
T10,W10 | T10 | ='NS (1)'!$U$29 |
U10 | U10 | =IF(SUM('DS (2)'!$S$41:$T$43)=0,"",SUM('DS (2)'!$S$41:$T$43)) |
V10 | V10 | =IF(SUM('NS (2)'!$S$41:$T$43)=0,"",SUM('NS (2)'!$S$41:$T$43)) |
R11 | R11 | =IF(SUM('DS (1)'!$S$11:$T$13)=0,"",SUM('DS (1)'!$S$11:$T$13)) |
S11 | S11 | =IF(SUM('NS (1)'!$S$11:$T$13)=0,"",SUM('NS (1)'!$S$11:$T$13)) |
T11,W11 | T11 | ='NS (1)'!$U$32 |
U11 | U11 | =IF(SUM('DS (2)'!$S$11:$T$13)=0,"",SUM('DS (2)'!$S$11:$T$13)) |
V11 | V11 | =IF(SUM('NS (2)'!$S$11:$T$13)=0,"",SUM('NS (2)'!$S$11:$T$13)) |
R12 | R12 | =IF(SUM('DS (1)'!$S$14:$T$16)=0,"",SUM('DS (1)'!$S$14:$T$16)) |
S12 | S12 | =IF(SUM('NS (1)'!$S$14:$T$16)=0,"",SUM('NS (1)'!$S$14:$T$16)) |
T12,W12 | T12 | ='NS (1)'!$U$35 |
U12 | U12 | =IF(SUM('DS (2)'!$S$14:$T$16)=0,"",SUM('DS (2)'!$S$14:$T$16)) |
V12 | V12 | =IF(SUM('NS (2)'!$S$14:$T$16)=0,"",SUM('NS (2)'!$S$14:$T$16)) |
R13 | R13 | =IF(SUM('DS (1)'!$S$17:$T$19)=0,"",SUM('DS (1)'!$S$17:$T$19)) |
S13 | S13 | =IF(SUM('NS (1)'!$S$17:$T$19)=0,"",SUM('NS (1)'!$S$17:$T$19)) |
T13,W13 | T13 | ='NS (1)'!$U$38 |
U13 | U13 | =IF(SUM('DS (2)'!$S$17:$T$19)=0,"",SUM('DS (2)'!$S$17:$T$19)) |
V13 | V13 | =IF(SUM('NS (2)'!$S$17:$T$19)=0,"",SUM('NS (2)'!$S$17:$T$19)) |
R14 | R14 | =IF(SUM('DS (1)'!$S$20:$T$22)=0,"",SUM('DS (1)'!$S$20:$T$22)) |
S14 | S14 | =IF(SUM('NS (1)'!$S$20:$T$22)=0,"",SUM('NS (1)'!$S$20:$T$22)) |
T14,W14 | T14 | ='NS (1)'!$U$41 |
U14 | U14 | =IF(SUM('DS (2)'!$S$20:$T$22)=0,"",SUM('DS (2)'!$S$20:$T$22)) |
V14 | V14 | =IF(SUM('NS (2)'!$S$20:$T$22)=0,"",SUM('NS (2)'!$S$20:$T$22)) |