Fetch results from entire workbook according to cell value

[Lux Aeterna]

Hello again

We've got an excel file which at the moment has two sheets. The first one has some demographics and some test results for year 2022. The second one is the sheet in which we write the test results for each person. These results are transferred through a macro the Demographics 2022 sheet. Next year we'll have a new sheet called Demographics 2023, in two years a sheet called Demographics 2024 and so on.

I was wondering if we could somehow fetch past results of a person to the Result sheet, whenever this person is tested again.

This would mean to search Demographics 2022, 2023 etc. according to the cell value in the "Results sheet" cell F6 and have those past results presented somewhere in the Results sheet, so that the supervisor can compare at a glance. Some of the participants may be tested more than once, so multiple results need to be presented in chronological order.

If that's possible, I can provide more details about the cells and ranges of the sheets.

 

[My Aswer Is This]

This is possible using Vba
Please provide specific details
Like
1. Both sheet names
2.The sheet columns like column B or G
Do not say column "Total"

 

[Lux Aeterna]

This is possible using Vba
Please provide specific details
Like
1. Both sheet names
2.The sheet columns like column B or G
Do not say column "Total"

Thank you for your reply and sorry for getting back late!

At the moment we only have one sheet for the demographics, called 2022. Next year we'll have a new sheet called 2023, In two years 2024 and so on. Is it possible for the VBA to include future sheets that haven't been created yet?

At the moment we only have one results sheet, but I am planning to create a second one. So it will be "Results normal" and "Results abnormal". Depending on the result of each sample, we will be using one of those sheets. So I guess the VBA should be able to run on the active Results sheet only.

Fetched results should appear on the active Results sheet, columns AA, AB, AC and AD, rows 11, 12 ... to 35. Row 11 will have to show the most recent and row 35 the oldest result.

Results sheet column AA should draw data from sheet 2022, 2023 etc. column B (that's the examiner name column)
Results sheet column AB should draw data from sheet 2022, 2023 etc. column F (that's the test date column)
Results sheet column AC should draw data from sheet 2022, 2023 etc. column AM (that's the pass/fail column)
Results sheet column AD should draw data from sheet 2022, 2023 etc. column AN (that's the comment column)

Now, the unique code according to which data should be fetched to the above columns is located in Results sheet F6. I don't know if it plays any role, but that cell gets its value through a VLOOKUP function.

Tell me if you need any other info!

 

[Lux Aeterna]

@My Aswer Is This I think I am able to make a single sheet both for normal and abnormal results. Does this make it easier for you?