I am certainly not an Excel superuser but I can work my way around ok when I
know what I am looking for. Here lies the problem. Which is the best way to
set up a register for archiving tracking.

I basically need it to act like a form and sub form in Access where there
are standard fields to be entered for each box (box number to auto number,
date of archive, date to be destroyed, archived by) and then a different
number of fields for each box for the actual files that are contained within
it.

I don't know that best way to begin setting this up but if you could offer
some ideas on which way is best, it would be great to know what to look for!

Appreciate all ideas,
Cheers Nic

P.S. To use Access is not an option unfortunately!

I can think of a couple of ways to deal with this.

BEST WAY: Use Access! (yes, I saw your P.S.)

One Option: use a couple of User Forms - one to get the general information
(like the main form in Access) and then a second form to gather the specifics
for the 'related' records.
What happens after either form is used depends on how you want things set up
in the workbook. The first form could add entries to a kind of Table of
Contents sheet that might provide a hyperlink to the beginning of those
related records entries on a second sheet. You could also set up the entries
created on the second sheet with the second form to link back to the other
entry on the TOC sheet.

Modified Option 1: Have the entries from first form entered starting in
column A on a sheet, then all entries from second form get put under it in
tree-fashion starting in column B. Then maybe a blank row to separate from
the next group on the sheet.

Another Option: pretty much like option 1, but without the user forms.

Third Option: you make entries as in the main form and record those on a
sheet (either manually or through a userform) and then entries for the
specifics are entered through a userform and instead of being saved in the
workbook, they would be written out to a .txt file which could be read back
in when you do the equivalent of a search for all files associated with that
unique number in the primary record.

Remember: some things that Excel simply does not have...
#1 - an auto-number feature. That has to be coded up.
#2 - no SQL type function to retrieve all records on a sheet that are
related to a given "key field value" - although Auto Filter can simulate that
to a degree.
#3 - Forms within forms to present data in an Access like fashion. Although
you could probably set up separate windows to display 2 sheets at the same
time, but that doesn't do you a lot of good unless you manage to link between
the two in some fashion.

Hopefully others will drop in and come up with some other possible options
for you to consider.

"Nic" wrote:

I am certainly not an Excel superuser but I can work my way around ok when I
know what I am looking for. Here lies the problem. Which is the best way to
set up a register for archiving tracking.

I basically need it to act like a form and sub form in Access where there
are standard fields to be entered for each box (box number to auto number,
date of archive, date to be destroyed, archived by) and then a different
number of fields for each box for the actual files that are contained within
it.

I don't know that best way to begin setting this up but if you could offer
some ideas on which way is best, it would be great to know what to look for!

Appreciate all ideas,
Cheers Nic

P.S. To use Access is not an option unfortunately!