Solved: Update records without looping?

[Ago]

I thought i was done with the hard part of my summary workbook, but i dont know how to find the values i need the easiest way.

The workbook is linked to other workbooks, hopefully you will just get a errormessage that the cells could not get updated.
Sheet 1 (Summary) contains a list of files i have in the same folder and the best numbers of those files.
The list is updated by a workbook_open() macro (which i disabled so it wont run on your computer) and works.

Now i need to find a way to update the sheet Records, which will sort out the best of the best. So this sheet will give me my personal records (high and low).
But heres the problem, how do i do that?
I know the numbers are in sheet Summary but for example to find the highest horizontal speed i need to compare the numbers in cell E3, E7, E11 and E15, for now but if i add one more set of data it will be E19 too.

I thought of a loop but that would mean i had to loop 14 times to fill the Records sheet.

The plan is to place the values in C column in Records sheet and a hyperlink filename in column D to the file.

Any suggestions?
I dont expect anyone to solve everything, just give me a hint or a start.

[mbarron]

I've attached a formula driven solution for the min's and max's but didn't know what values would drive the average for the Records

[Eds]

Hello Ago.

Are you familiar with Naming Ranges? One simple solution for you to get your Max function (or Min or Average) to run only over selected cells is to name all cells with the same name.

Suppose you want to find Max H-Speed for all the files (unknown number). Then you simply name the cells for which you want the values to participate in the max computation with the same name, lets say, HSpdMax, and in Records sheet C9 you enter formula

=Max(HSpdMax)

This could be the solution if you have just a few files. Like no more than 20. Otherwise it could mean much work for you to prepare your worksheet.

For the Link in column D, I think the best it would be using a User Defined Function. I can give you a hint later about this.

But, before I continue, what values should the algorithm return in case it finds two files with equal Max (or Min or Average) values? Which file name should it return? The first it finds? Both of them?

Good Luck
Eduardo.

[Ago]

First of all, can anyone see the VBA code that is (should be) in the file?
Before i uploaded the file here i marked all code and pressed comment block so that it would be disabled.
Saved it and uploaded it.

Now when i open the file its empty, so is the one here when i download it.
Can anyone see it? How can it just disapear?


mbarron, that looks really nice but there seems to be a problem with the maxnumbers. All of them seem to show the same number.
For example, max glide is supposed to be 1.514 but shows 2822.

I like the idea of making it a exelformula. But the problem will be to get the hyperlinks because you dont know where the data is pulled from (?).
Lol forgot about the average, its supposed to be max average value.


Eds.
I have no idea what naming ranges is, do you mean you mark a cell with a name?
Could that be done in VBA? The code i lost was placing all the numbers and links in the summary sheet, i might be able to just add that feature to the code?

[mbarron]

I forgot to adjust the formulas for the max results.

I've updated the sheet with the correct Max values added Max Averages as well. I also added hyperlink formulas.

[Ago]

WOW!
Im really impressed.
I had no idea all that could be done with normal Excel formulas.
Thank you so much!

Just so that i know, how does it work if there is two numbers that is the same.
Its very unlikely with "real" numbers (this is as you can see two identical files in the samplefile)?
It seems as it creates the link to the top one, correct?

Now its going to be so much easier to compare the data. Now i just need to work on the skill to get better :-)

Thank you !!!

[mbarron]

You are correct. If there are two numbers that match, it will return the first one (starting at the top of the sheet) it finds.

[Eds]

Hi Ago, mbarron...

No, I did not see any VBA code inside your file either.

Still, happy you`ve got your problem solved!

Bye,
Eduardo