Excel functions

[tkaz]

I have a workbook with multiple worksheets. I have a summary sheet that finds the minimum value in those 25 worksheets by cell. How would I be able to identify which sheet holds the minimum value for each row on the summary sheet?

I'm not very familiar with Excel functions so any help would be appreciated.

Here is an example of the data.

Sheet1!B2 = 25
Sheet2!B2 = 30
Sheet3!B2 = 27
...
...
...
Sheet22!B2 = 18
Sheet23!B2 = 26
...
...
...

The summary sheet shows cell B2 minimum value as "25". I want to know that value is coming from Sheet1!B2.

Does that make sense and can it be done?
Thanks

 

[randy700]

I'd start by correcting the formula that finds the minimum values. Isn't 18 (on sheet22) less than 25?


Randy

 

[SkipVought]

I have a workbook with multiple worksheets.

THAT is your biggest problem, very common with novice spreadsheet users.

I would consolidate your data into one sheet, adding a column identifying the group or sheet. With that kind of structure, your answer would be relatively simple.

What is the structure of each sheet? What is the purpose of each sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tkaz]

The formula to find the MIN is actually correct. I simply gave the wrong sheet number in my example.

This project is purposed to accept bid amounts from multiple contractors for similar services, collect them all, and find the lowest bidders.

The structure on each sheet includes bidder name, address, product, price.

I was hoping to not have to consolidate all the bid responses into one spreadsheet...therefore the summary sheet that finds the MIN value for each product down the price column. And, that does work just fine.

The issue came in when I then needed to see which company had that low price without going back through each sheet.

I hate that I messed up my bad example and thank you both for the responses.

 

[kjv1611]

For what you are wanting to do, you need to put them all in one sheet, as suggested. Sure it can be done in multiple sheets, but the effort to do so is worthless, when doing it the correct way will be LOTS quicker and simpler.

If you've got the same fields in all sheets, it'd be really easy to combine them all into one with a sheet identifier type column.

 

[kjv1611]

Also, is this something you do on a regular basis? How does the data change?

If it's something you do as a routine, and the data comes in as separate sheets, then building a Macro or two for it in Excel would probably be helpful, or else converting the process to Access might be better. I suppose it depends upon you, and what you need/want to do with it.

For instance, in Access, you could import all the sheets into their own tables and use a union query to "combine" them all, and compare from that. Or you could just import them into the same table... so you'd once again only have one data source to deal with.

When you're comparing data, you really need it in one "source", so you can make sure you don't have any "stupid" errors.

 

[tkaz]

Yes, this is done on a regular basis. Yes, the data comes in on separate sheets. I like the idea of using Access; then I could use the data for other purposes and build some reporting.

Thanks for the suggestions.

 

[Gruuuu]

import all the sheets into their own tables

eek! just as bad as leaving the data in separate sheets! Writing the SQL to handle such a poor design would be magnitudes more difficult than writing Excel functions to do the same.

 

[kjv1611]

Gruuuu,

It was one option of the few. And a Union Query to combine them all would not be as difficult as writing the VBA to handle it... at least not in my opinion.

It can be done like this:

1. Create a Query in Access
2. In Query view, select the tables you want to combine with the "Show Tables" button... or when you first create the query, when it asks you to select the source(s).
3. Change from Design View to SQL View.

Now, you'll have something like
SELECT FROM Table1, Table2, Table3, Table4, Table5

So then you change it to:

SELECT *
FROM Table1
UNION SELECT *
FROM Table2
UNION SELECT *
FROM Table3
UNION SELECT *
FROM Table4
UNION SELECT *
FROM Table5

Of course, the one table method would be best, but if for some reason they were more comfortable with keeping multiple tables, it's an option.

I've actually been using this as a method of combining data from multiple databases of late for a couple of processes. Before they were manually copied/pasted together which just doesn't seem like a good idea to me. Now, I link to the tables, then create a UNION query to combine the data, and then I can do what I want with it.

Of course, in my scenario, I'm working on a different database design - a true "split" database with multiple front-ends rather than a bunch of totally separate databases. Just haven't put it into production in the biggest area yet.

But I'm heading off-topic, so I'll stop here.

 

[kjv1611]

tkaz,

Espeically considering this comes in on a regular basis, I'd DEFINITELY say to build one "master" table, and always import to that table.... or, depending upon your situation and how you want to run it, you could do something like this:

1. Have an "import" table
2. Have a "main" table

Steps:
1. Always clear the "import" table of any data
2. Import the new sheet each time to the "import" table
3. Have a query built which will take the import data and append it to the main table, and in that query, you can add the field to specify what source/name/sheet/whatever..

So, the SQL for that query would look like this (though you wouldn't have to do it in SQL code):

INSERT INTO tblMain
SELECT  i.* ,'MyWorksheet'
FROM tblImport i

Well, I can't remmber for sure whether you can use INSERT in Access, or whether you have to use another method... but it's an Append query from the query type menu/ribbon.