Grabbing Offset (Excel 97 SR1 NT4)

[johncon]

Good day all.

I am having some difficulties with a formula. I have several tabs containing maintenace data for company vehicles. On the first Tab, I have a summation area.
I have several variables I would like to pull, like most/least mileage, highest/lowest repair bills etc. I have used MAX/Min no problem to get the data. My question is that in the next field I want to pull in the Van # beside the entry. Ie. Most mileage - Results - Van #. Is there any way to grab the offset of the MAX/MIN results to determine the appropriate VAN #? Is there some function/way to grab the variable?
FYI Van name for each sheet is at A1 and as the Tab name (ie Van 1, Van 2, etc).

Your thoughts please.

Thanks.

[j.peter.orourke]

Assuming the named range 'MyData' contains the range in which you are looking for the MIN or MAX values -

=MATCH(MIN(MyData),MyData,0)

Will tell you the FIRST location in the list where the MIN value can be found. i.e. If there were three values the same, this will only find the first occurence.

Using this, you could feed it into ADDRESS or OFFSET to calculate your lookup. Hope this helps.

Regards
Peter

[johncon]

I was initially thinking along those lines but my data is spread across 9 tabs. Every option I have to try and create a named range over those tabs results in only 1 area in one tab being selected. I am not sure a named range can exist across multiple tabs. I can not even use DMAX due to the multiple ranges.
Any ideas on how to get arount this so I can try your formula?

[sdckapr]

I make this suggestion over and over again, COMBINE the data. Add an extra column in it as the identifier for the different sheets. Autofilter, and other functions are much easier to use. Why have multiple copies of essentially the same sheet : combine them and add differentiating columns.

Steve

[JohnBF]

I agree with Steve that combining the data into one sheet is the better way to organize your data, but sometimes the boss wants a specific inefficient layout. See if <!post=this,190939>this<!/post> helps with the 3D named range issue and also investigate Data, Consolidate.

[sdckapr]

I think that if you show a boss the ease of use (make sure it is REALLY user-friendly) of one sheet with autofilters, subtotals to calculate info and other "bells and whistles" (most that he had no idea could be done!) he will go along with whatever you design.

I find giving the BOSS what they NEED and can use (and is easier for ME to update) rather than what they ASK for (they never really know what they want or need or the "power of excel") is always the better way to go.

Steve

[johncon]

Of course you are both right. I am a big fan of Autofilters and one single layout. This 'exercise' was more an experiment on my behalf to get a 3D feel. The Data, Consolidate option was considered, but I simplified the multi-tab data into one Pivot table with Page fields for each VAN/Tab. This allows a uniform design down and the user can pull up the Page/Tab of the individual units and get more thorough data. Which goes to show, with Excel, there are countless ways to do one thing. I will probably resort to a single sheet if I can keep it from displaying too much data.

I thank you both for your opinions. They are appreciated. I knew I came to WOPR for a reason.

[j.peter.orourke]

Well, I can't find a way to make MATCH work across worksheets. (Perversely the MAX/MIN functions do.) The following, somewhat laboured example, works:

=MATCH(MAX(Sheet1!F15,Sheet1!G15,Sheet1!H15,Sheet1 !I15,Sheet1!J15,Sheet1!K15,Sheet1!L15),Sheet1!F15: Sheet1!G15:Sheet1!H15:Sheet1!I15:Sheet1!J15:Sheet1 !K15:Sheet1!L15,0)

Note all references are still on Sheet1. Any attempt to change this to refer to multiple sheets results in a #VALUE! error. As others more experienced than I have suggested, best approach is to combine the data from your various worksheets and work on the combined data. Good luck!