How to get formulas to pull through info from other sheets?

[Slyon]

Please could someone hopefully advise on the below..

I am wanting to use my first sheet of my workbook as the main part of my spreadsheet, to show the outcome of my formulas from the data provided on several other sheets.

How do I go about this? I'm assuming there will be something I need to add onto the formula to get it to relate to the relevant sheet..

If it helps, this will be the formula I'll be using on each row changing the relevant columns etc - =IF(F3<EDATE(TODAY(),-12),0,F3-MAX(E3,EDATE(TODAY(),-12))+1)+IF(H3<EDATE(TODAY(),-12),0,H3-MAX(G3,EDATE(TODAY(),-12))+1)+IF(J3<EDATE(TODAY(),-12),0,J3-MAX(I3,EDATE(TODAY(),-12))+1) Therefore I just need to get this to relate to other sheets depending on which one it relate to.

Much appreciated, as always!

 

[Toadstool]

Hi Slyon,

To address another sheet you prefix the cell address by the sheet name, wrapped in single quotes in case it contains a space, and then an exclamation mark:

=IF('My First Sheet'!F3<EDATE(TODAY(),-12),0,'My First Sheet'!F3-MAX('My First Sheet'!E3,EDATE(TODAY(),-12))+1)+IF('My First Sheet'!H3<EDATE(TODAY(),-12),0,'My First Sheet'!H3-MAX('My First Sheet'!G3,EDATE(TODAY(),-12))+1)+IF('My First Sheet'!J3<EDATE(TODAY(),-12),0,'My First Sheet'!J3-MAX('My First Sheet'!I3,EDATE(TODAY(),-12))+1)

 

[Slyon]

Hi Slyon,

To address another sheet you prefix the cell address by the sheet name, wrapped in single quotes in case it contains a space, and then an exclamation mark:

=IF('My First Sheet'!F3<EDATE(TODAY(),-12),0,'My First Sheet'!F3-MAX('My First Sheet'!E3,EDATE(TODAY(),-12))+1)+IF('My First Sheet'!H3<EDATE(TODAY(),-12),0,'My First Sheet'!H3-MAX('My First Sheet'!G3,EDATE(TODAY(),-12))+1)+IF('My First Sheet'!J3<EDATE(TODAY(),-12),0,'My First Sheet'!J3-MAX('My First Sheet'!I3,EDATE(TODAY(),-12))+1)

Thanks Toadstool, that seems to have worked!

Except the only problem I have now though, is that I don't know how to get the formula to include all date ranges within two certain columns from that second sheet, if that makes sense?

 

[Slyon]

To clarify..

The formula is only working for one date range on the other sheet, where as I need all date ranges within two columns totalling together then show as a total on the main front sheet. E.g. B4 & C4 = 1 day then add B5 & C5 which = 1 day etc etc... if that makes sense?

 

[Toadstool]

You've not actually stated your aim so I'm guessing you're trying to add together absence days over the last 12 months?

Is this what you want?

Slyon.xlsx
	A	B	C
1
2		From	To
3		19-Sep-21	19-Sep-21
4		20-Sep-21	20-Sep-21
5		21-Sep-21	21-Sep-21
6		22-Sep-21	22-Sep-21
7		23-Sep-21	23-Sep-21
8		24-Sep-21	24-Sep-21
9		25-Sep-21	05-Oct-21
10		26-Sep-21	26-Sep-21
11		27-Sep-21	27-Sep-21
12		28-Sep-21	28-Sep-21
13		29-Sep-21	29-Sep-21
14		30-Sep-21	30-Sep-21
15		01-Oct-21	01-Oct-21
16		02-Oct-21	02-Oct-21
17		03-Oct-21	03-Oct-21
18		04-Oct-21	04-Oct-21
19
My First Sheet

Slyon.xlsx
	A	B	C
1		Absence Days in Last 12 Months
2		26
3
Absence

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--('My First Sheet'!$B$3:$B$9999>EDATE(TODAY(),-12))*(('My First Sheet'!$C$3:$C$9999+1)-'My First Sheet'!$B$3:$B$9999))

 

[Slyon]

You've not actually stated your aim so I'm guessing you're trying to add together absence days over the last 12 months?

Is this what you want?

Slyon.xlsx
	A	B	C
1
2		From	To
3		19-Sep-21	19-Sep-21
4		20-Sep-21	20-Sep-21
5		21-Sep-21	21-Sep-21
6		22-Sep-21	22-Sep-21
7		23-Sep-21	23-Sep-21
8		24-Sep-21	24-Sep-21
9		25-Sep-21	05-Oct-21
10		26-Sep-21	26-Sep-21
11		27-Sep-21	27-Sep-21
12		28-Sep-21	28-Sep-21
13		29-Sep-21	29-Sep-21
14		30-Sep-21	30-Sep-21
15		01-Oct-21	01-Oct-21
16		02-Oct-21	02-Oct-21
17		03-Oct-21	03-Oct-21
18		04-Oct-21	04-Oct-21
19
My First Sheet

Slyon.xlsx
	A	B	C
1		Absence Days in Last 12 Months
2		26
3
Absence

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--('My First Sheet'!$B$3:$B$9999>EDATE(TODAY(),-12))*(('My First Sheet'!$C$3:$C$9999+1)-'My First Sheet'!$B$3:$B$9999))

My apologies, yes that is correct.

I'm hoping to have one sheet as the main sheet then several other sheets with peoples names on. Then under those named sheets will be all that persons absence dates (like the above list of dates photo).

I then need the total of all date ranges entered in those two columns under each sheet sheet to populate onto the main page under the relevant person. The formula also needs to eliminate any days which are from before a year from the current date (As per my previous post on here - Formula needed .

See example below of what my spreadsheet looks like:

 

[Toadstool]

Ah! Therein lies more complexity.

Unless you want to handcraft each formula cell to include the sheet name then you'll need to use INDIRECT.

Let me check something, are you sure it's a rolling 12 month period and not a defined date range for the reporting period? (e.g. instead of dates after 29-Oct-2020 do you use a 1-Jan-2021 to 31-Dec-2021 as the period?)

I've shown the use of INDIRECT below (omitting "Name 3" to test error handling) but you can see the issue in Name 2 dates 26-Oct-2020 to 05-Nov-2020. Because 26-Oct-20 is more than a year ago then it does not include that range, but 30-Oct-2020 to 05-Nov-2020 are within the range and I can't see how to address that without a redesign (maybe having a calculation on each Name sheet in a specific cell and just retrieving that number for the report).

Slyon2.xlsx
	A	B	C	D
1
2		From	To
3		19-Sep-21	19-Sep-21
4		20-Sep-21	20-Sep-21
5		21-Sep-21	21-Sep-21
6		22-Sep-21	22-Sep-21
7		23-Sep-21	23-Sep-21
8		24-Sep-21	24-Sep-21
9		25-Sep-21	05-Oct-21
10		26-Sep-21	26-Sep-21
11		27-Sep-21	27-Sep-21
12		28-Sep-21	28-Sep-21
13		29-Sep-21	29-Sep-21
14		30-Sep-21	30-Sep-21
15		01-Oct-21	01-Oct-21
16		02-Oct-21	02-Oct-21
17		03-Oct-21	03-Oct-21
18		04-Oct-21	04-Oct-21
19
Name 1

Slyon2.xlsx
	A	B	C
1
2		From	To
3		19-Sep-20	22-Sep-20
4		26-Oct-20	05-Nov-20
5		28-Nov-20	01-Dec-20
6		31-Dec-20	03-Jan-21
7		02-Feb-21	05-Feb-21
8		07-Mar-21	10-Mar-21
9		09-Apr-21	12-Apr-21
10		12-May-21	15-May-21
11
Name 2

Slyon2.xlsx
	A	B	C	D
1	Employee	Position	SE	Days Taken
2	Name 1			26
3	Name 2			24
4	Name 3			No such name
Absence Tracker

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(SUMPRODUCT(--(INDIRECT("'"&A2&"'!$B$3:$B$9999")>EDATE(TODAY(),-12))*((INDIRECT("'"&A2&"'!$c$3:$c$9999")+1)-INDIRECT("'"&A2&"'!$B$3:$B$9999"))),"No such name")

 

[Slyon]

Ah! Therein lies more complexity.

Unless you want to handcraft each formula cell to include the sheet name then you'll need to use INDIRECT.

Let me check something, are you sure it's a rolling 12 month period and not a defined date range for the reporting period? (e.g. instead of dates after 29-Oct-2020 do you use a 1-Jan-2021 to 31-Dec-2021 as the period?)

I've shown the use of INDIRECT below (omitting "Name 3" to test error handling) but you can see the issue in Name 2 dates 26-Oct-2020 to 05-Nov-2020. Because 26-Oct-20 is more than a year ago then it does not include that range, but 30-Oct-2020 to 05-Nov-2020 are within the range and I can't see how to address that without a redesign (maybe having a calculation on each Name sheet in a specific cell and just retrieving that number for the report).

Slyon2.xlsx
	A	B	C	D
1
2		From	To
3		19-Sep-21	19-Sep-21
4		20-Sep-21	20-Sep-21
5		21-Sep-21	21-Sep-21
6		22-Sep-21	22-Sep-21
7		23-Sep-21	23-Sep-21
8		24-Sep-21	24-Sep-21
9		25-Sep-21	05-Oct-21
10		26-Sep-21	26-Sep-21
11		27-Sep-21	27-Sep-21
12		28-Sep-21	28-Sep-21
13		29-Sep-21	29-Sep-21
14		30-Sep-21	30-Sep-21
15		01-Oct-21	01-Oct-21
16		02-Oct-21	02-Oct-21
17		03-Oct-21	03-Oct-21
18		04-Oct-21	04-Oct-21
19
Name 1

Slyon2.xlsx
	A	B	C
1
2		From	To
3		19-Sep-20	22-Sep-20
4		26-Oct-20	05-Nov-20
5		28-Nov-20	01-Dec-20
6		31-Dec-20	03-Jan-21
7		02-Feb-21	05-Feb-21
8		07-Mar-21	10-Mar-21
9		09-Apr-21	12-Apr-21
10		12-May-21	15-May-21
11
Name 2

Slyon2.xlsx
	A	B	C	D
1	Employee	Position	SE	Days Taken
2	Name 1			26
3	Name 2			24
4	Name 3			No such name
Absence Tracker

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(SUMPRODUCT(--(INDIRECT("'"&A2&"'!$B$3:$B$9999")>EDATE(TODAY(),-12))*((INDIRECT("'"&A2&"'!$c$3:$c$9999")+1)-INDIRECT("'"&A2&"'!$B$3:$B$9999"))),"No such name")

Unfortunately it would need to be on a rolling basis in the circumstances this is needed for, otherwise it would probably be alot easier sadly.

I don't mind changing the formulas where it states the sheet names in them for each one if needed..

The spreadsheet needs to be used by other people at a glance, so needs to always be up to date for them, if you get what I mean?

 

[Toadstool]

I would redesign the name sheets to include the calculation then just pull the total into the Absence Tracker sheet, like this:

Slyon3.xlsx
	A	B	C	D	E	F
1
2		From	To		Last 12 Months	Total
3		19-Sep-20	19-Sep-20		0	8
4		31-Oct-20	01-Nov-20		1
5		01-Jan-21	05-Jan-21		5
6		22-Sep-21	22-Sep-21		1
7		24-Sep-21	24-Sep-21		1
8		11-Nov-21	02-Nov-22		0
9
Name 1

Cell Formulas
Range	Formula
F3	F3	=SUM($E$3:$E$999)
E3:E9	E3	=IF(OR(B3="",C3=""),"",MAX(MIN(C3,TODAY())-MAX(B3,EDATE(TODAY(),-12))+1,0))

Slyon3.xlsx
	A	B	C	D	E	F
1
2		From	To		Last 12 Months	Total
3		19-Sep-20	22-Sep-20		0	29
4		26-Oct-20	05-Nov-20		5
5		28-Nov-20	01-Dec-20		4
6		31-Dec-20	03-Jan-21		4
7		02-Feb-21	05-Feb-21		4
8		07-Mar-21	10-Mar-21		4
9		09-Apr-21	12-Apr-21		4
10		12-May-21	15-May-21		4
11
Name 2

Cell Formulas
Range	Formula
F3	F3	=SUM($E$3:$E$999)
E3:E11	E3	=IF(OR(B3="",C3=""),"",MAX(MIN(C3,TODAY())-MAX(B3,EDATE(TODAY(),-12))+1,0))

Slyon3.xlsx
	A	B	C	D
1	Employee	Position	SE	Days Taken
2	Name 1			8
3	Name 2			29
4	Name 3			No such name
5
Absence Tracker

Cell Formulas
Range	Formula
D2:D5	D2	=IF(A2="","",IFERROR(INDIRECT("'"&A2&"'!$F$3"),"No such name"))