Reading Different Tabs

[Jar888]

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!

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))

 

[GlennUK]

Have you thought about using Power Query to combine all the tabs into one list? Then you can analyse to your hearts content with ease.

 

[Jar888]

I can't say for sure weather that would work as I have no knowledge on power query. I'll explain a bit more in depth below.

Each worksheet is divided up into a nightshift and dayshift (DS or NS) followed by the day of the month (1, 2, 3, 4). I only really care about the previous days shifts. For instance, today is the 15th, so I am looking at DS (14) and NS (14) worksheets to get the information for reporting to management.

I was hoping you could get excel to read a particular worksheet based off an input into a cell, but I haven't seen if anything like that online, so I wasn't sure if it was possible or not. If power query can get me the same results, I'll look into it though.

 

[GlennUK]

You could use INDIRECT to point to a specific tab in a formula .. off an input by cell. Have you used that before?

 

[Jar888]

I'll look into INDIRECT. I'm a complete novice, sort of learning everything on the fly while I work.

 

[Jar888]

I've looked into INDIRECT, and I think it'll work perfectly. I'm just a little bit stuck on the syntax on how I'm supposed to lay it out.

I currently have the below, which brings up a #VALUE error.

=IF(SUM(INDIRECT("'"&R1&"'!$S$23:$T$25")=0),"",SUM(INDIRECT("'"&R1&"'!$S$23:$T$25")))

Cell R1 contians = DS (1)

Any help would be appreciated.

 

[GlennUK]

I'd say that one of the cells in the range 'DS (1)'!S23:T25 has a content of #VALUE - did you check?