Advise Required - Save Multiple Copies of Spreadsheet in WorkBook, Access with a Unique ID

[W. Kym Wittal]

Hello, I am looking for any advise on how best to do something.

I have a workbook for a club, keeps all member information on 1 spreadsheet and there are other spreadsheets for Tools, Lookups, etc., etc., all in 1 workbook.

The member information spreadsheet is identified by year. What I would like to do is keep multiple copies of the 'year' spreadsheet, but only 1 of each of the supporting spreadsheets, that is be able to select the year of interest and then access that year of data. Of course, select a different year, see that data.

An easy way is to just have 1 workbook per year, but I was hoping to have only 1 workbook, with multiple years included.

I thought of having a year identifier on the 1 member spreadsheet, not sure if that would work. Thought of many year spreadsheets, again not sure how to access the year I want.

Any advise?

[Pete_UK]

If all your "year" sheets are named in a consistent way (e.g. "2015", "2016", "2017" etc.) then if you use cell A1 to choose the year then you can use the INDIRECT function to access the sheet you have chosen. You would use a formula like this:

=INDIRECT("'"&$A$1&"'!A3")

to get data from cell A3 of the chosen sheet. You may want to make the A (in red) variable dependent on the column and the 3 variable dependent on the row, so that you can copy the formula across and down.

Hope this helps.

Pete

[W. Kym Wittal]

Thanks. I will play with this bit to ensure it will work for what I am thinking.

[W. Kym Wittal]

The previous advise has helped me in other areas of what I am trying to do, however, I have been trying to get the following formula to work, but I am clearly missing something. Specifically I am trying to get the Named Array below (in Red) to be the variable I can change and search on, ie RWFHCMemberDataBase2018, RWFHCMemberDataBase2019, RWFHCMemberDataBase2020, etc., but I keep running into errors. I can hardcode the Named Array, but I have yet to find a way to 'build' or 'populate' the Named Array into the INDEX formula. Any thoughts?

Please Login or Register  to view this content.

[Pete_UK]

Untested, but maybe you can try this:

=IF(RWFHC_DB_Row=1,"",IF(INDEX(INDIRECT("RWFHCMemberDataBase"&$Z$1),RWFHC_DB_Row,O4)="","",INDEX(INDIRECT("RWFHCMemberDataBase"&$Z$1),RWFHC_DB_Row,O4)))

where I have assumed that cell Z1 contains 2018.

Hope this helps.

Pete

[W. Kym Wittal]

Unfortunately, that did not work. I have been trying to step through the error messages and it appears that when the INDEX function tries to resolve the ARRAY name, it can only do it if it is a Defined Name, not a name that has been "built" or "created".

I found this in a previous thread:

Re: Return range name from concatenated strings
As I mentioned, INDIRECT will only work with a named range (your example 1 is a named constant - a string - not an actual range). It will also only work with static named ranges, not dynamic, which is why example 2 fails. If you need to work with a dynamic range, you can use EVALUATE but that can only be used in a defined name, not in a worksheet cell directly.

I believe the above clarifies why INDEX will not work for me (I believe it will function in the same way, that is, I am supplying a string, not a Defined Name). I am able to use IF THEN decisions to get to the proper Named Range, I was hoping for something more elegant. Oh well?

[JeteMc]

Could you upload a small, six to nine names (two or three per year), desensitized sample of your workbook that manually displays the results you would like the formula to produce?
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.