Help with excel formula

Copper Contributor

Hello,

 I need some help linking multiple sheets.

 I need to pull data into ‘T3 Data’ D4, from (‘Titanium Data’ D5,E5)&(‘Dryer Data’ D5,E5,F5)&(‘B1 Data’ D5,E5,F5)&(‘Retail Blend Data’ D5,E5,F5)

 My other formulas on my “Display” sheets pull the info from singular data sets based on the date and/or shift information. T1 Display pulls previous shift data, T2 will be past 24 hours (multiple shifts from that same day. T3 will add all data sets for the previous 24 hours

 

Can somebody help me throw this together? Way above my knowledge level.

5 Replies

@MattP817 

You could write a formula that uses the + operator between each of those fields, but that becomes lengthy.  A better approach is to take advantage of Excel's SUM function, which can add literal numbers and/or values from individual cells and/or values in ranges of cells.  Try this:

=SUM('Titanium Data'!D5:E5, 'Dryer Data'!D5:F5, 'B1 Data'!D5:F5, 'Retail Blend Data'!D5:F5)

 

I think the following works and is 'dynamic' based on the header in that column:

 

=SUM(FILTER(TOCOL('Titanium Data:Retail Blend Data'!5:5),D$3=LEFT(TOCOL('Titanium Data:Retail Blend Data'!$3:$3),LEN(D$3)),0))

 

 

How would I "lock" that range of cells when I drag it over? In this case, I need it to add the three cells from each day (three shifts), but in future sheets, I will be adding areas that only work two shifts.
This looked like it would work, but I have info that changes in the column. For instance, In T3 Data D15, I need to add cells ('B1 Data'D16:F16,'Retail Blend Data'D16:F16, 'Retail Blend Data'D19:F19,'Retail Blend Data'D22:F22)
If you need different formula for different rows it will need to change. I don't know what you need for each of those values. The formula I gave you will automatically sum up ACROSS that range of tabs/sheets the values 1 row 'lower' where the column header starts with the same header on this page. NOTE: I notice the formula had 3:3 and that should be $3:$3 so it always looks at the header on those pages.
That said, for row 15 telling me it needs to add up those somewhat random ranges isn't that helpful. but you can use the same 'format' to automatically find and filter the appropriate columns for those rows you need.