File Archive Spreadsheet

[fishezuk]

Hi Everyone!

I am creating a spreadsheet to record and track physical files that are being archived. Whilst I can get multiple 'IF' statements to work in principle there are too many nests for Excel to allow it. I have no idea whether I need to use COUNTIF(S), VLOOKUP, INDEX/MATCH or another formula to achieve the desired outcome.

Here is a basic idea of what I am trying to achieve:

SHEET 1 (INFORMATION)

File Type	Date of Birth	Date of Leaving	Retention Expiry
Letter	01/10/2015		01/10/2025
Application Form		01/03/2021	01/03/2028

SHEET 2 (DATA)

Keep for 10 years (from DOB)	Keep for 7 Years (from DOL)
Letter	Application Form

So I am looking for a formula to achieve the following:

1) Sheet 2 (Data) has a column for each retention period with every file type listed in the appropriate column.

2) I need to calculate the retention expiry (on Sheet 1) based on which column the file type is in.

For example, in Sheet 1 (line 1) the file type 'Letter' is in the column 'Keep for 10 years (from DOB)' on Sheet 2. Therefore, the retention expiry on Sheet 1 is calculated as Date of Birth + 10 Years.

In Sheet 1 the file type (line 2) is 'Application form' which is in the column 'Keep for 7 Years (from DOL)' in Sheet 2. Therefore, the retention expiry is calculated as Date of Leaving + 7 Years.

I am happy for the years to be calculated using EDATE for months.

There are a lot of different retention periods but I don't believe it will exceed to 64(?) maximum allowed by Excel. Fingers crossed I am correct.

I hope this makes some sense. I appreciate that what I am attempting to achieve is pretty complex but I am hoping you wonderful people on here can help me out.

Thank you so much in advance for your efforts and support.

John

 

[Eric W]

Welcome to the MrExcel forum!

I'd recommend you set up your Data sheet like this:

Book2
	A	B	C
1	Type	Start Date	# of years
2	Letter	Date of Birth	10
3	Application Form	Date of Leaving	7
4
Data

If you do, you can set up your Information sheet like this:

Book2
	A	B	C	D
1	File Type	Date of Birth	Date of Leaving	Retention Expiry
2	Letter	1/10/2015		1/10/2025
3	Application Form		1/3/2021	1/3/2028
4
Information

Cell Formulas
Range	Formula
D2:D3	D2	=EDATE(INDEX(B2:C2,MATCH(VLOOKUP(A2,Data!$A$2:$C$10,2,0),B$1:C$1,0)),VLOOKUP(A2,Data!$A$2:$C$10,3,0)*12)

The description (column B) on the Data sheet must match the heading on the Information sheet. But if you do it like this, then you can extend the table as far as you want, and you don't have to worry about nesting IFs. I can shorten the formula a bit if you don't use Excel 2019.

Hope this helps!

 

[fishezuk]

Welcome to the MrExcel forum!

I'd recommend you set up your Data sheet like this:

Book2
	A	B	C
1	Type	Start Date	# of years
2	Letter	Date of Birth	10
3	Application Form	Date of Leaving	7
4
Data

If you do, you can set up your Information sheet like this:

Book2
	A	B	C	D
1	File Type	Date of Birth	Date of Leaving	Retention Expiry
2	Letter	1/10/2015		1/10/2025
3	Application Form		1/3/2021	1/3/2028
4
Information

Cell Formulas
Range	Formula
D2:D3	D2	=EDATE(INDEX(B2:C2,MATCH(VLOOKUP(A2,Data!$A$2:$C$10,2,0),B$1:C$1,0)),VLOOKUP(A2,Data!$A$2:$C$10,3,0)*12)

The description (column B) on the Data sheet must match the heading on the Information sheet. But if you do it like this, then you can extend the table as far as you want, and you don't have to worry about nesting IFs. I can shorten the formula a bit if you don't use Excel 2019.

Hope this helps!

Thank you SO much for your help with this query and thank you for answering so quickly as well.

In the actual spreadsheet, the cells 'Date of Birth', 'Date of Leaving' etc. are not next to each other.

I even managed to add a 3rd reference cell 'Date of file' (i.e. the date of the document/file itself) as a reference cell and get it working thanks to you!

Do I need to reorganise the spreadsheet so that they are next to each other or is there a way for the formula to still work?

Currently, Date of Birth is in C2, Date on File is in L2 and I need to add Date of Leaving into the spreadsheet.

Thank you so much again!

John

 

[fishezuk]

Hi Eric

I have now got the formula working. Please ignore my first reply. I cannot thank you enough for your support!

I hope you don't mind if I ask you one more question. Fingers crossed you don't! Here goes:

Following your example above, on the 'Information' Sheet there is an 'Action upon Expiry' (it's actually in cell O2..)

My 'Data' Sheet is very similar to your suggestion:

File Type	Start Date	# of Years	Action at End of Life
Letter	Date of Birth	10	Archive
Application Form	Date of Leaving	7	Secure Disposal

I would like a formula that will check the 'Information' Sheet 'File Type' and fill in the 'Action at End of Life' on the 'Information' Sheet based upon the 'Data' Sheet contents.

For example, if the File Type on the 'Information' sheet is 'Letter' then the 'Action at End of Life' cell on the 'Information' sheet is 'Archive'. This is based upon matching the 'File Type' and 'Action at End of Life' from the 'Data' sheet.

If the File Type on the 'Information' sheet is 'Application Form' then the 'Action at the End of Life' on the 'Information' sheet is Secure Disposal etc.

For reference, on my actual 'Data' sheet File Type is in cell I3 and the Action at End of Life is in cell O3. However, feel free to use the cell references above and I can try and tweak it accordingly.

Thank you so much!

John

 

[Eric W]

You should just be able to use a VLOOKUP:

Book2
	A	B	C	D
1	Type	Start Date	# of years	Action at End of Life
2	Letter	Date of Birth	10	Archive
3	Application Form	Date of Leaving	7	Secure Disposal
Data

Book2
	A	B	C	D	E
1	File Type	Date of Birth	Date of Leaving	Retention Expiry	Action at End of Life
2	Letter	1/10/2015		1/10/2025	Archive
3	Application Form		1/3/2021	1/3/2028	Secure Disposal
Information

Cell Formulas
Range	Formula
D2:D3	D2	=EDATE(INDEX(B2:C2,MATCH(VLOOKUP(A2,Data!$A$2:$C$10,2,0),B$1:C$1,0)),VLOOKUP(A2,Data!$A$2:$C$10,3,0)*12)
E2:E3	E2	=VLOOKUP(A2,Data!$A$2:$D$10,4,0)

Change the references to match your sheet, as before. If the "Action" column isn't adjacent to the other columns, you can use INDEX/MATCH or XLOOKUP instead.

 

[fishezuk]

Thank you so much for all your support and patience with my requests. It is VERY much appreciated!

John