You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Formula: Depending on Which Sheet a Cell is Selected from Using Another Formula

Advertisement

QUESTION: Hi, I'm not really sure how to word this, but I'll give it a shot. I've used Excel a while, but have had very little to do with formulas. I have a workbook with multiple sheets containing identical fields, but for different time periods. I've set up formulas to select a minimum and maximum value from ALL the sheets and add them to the summary worksheet. What I would like to do is make a separate formula next to each of these min or max results, that displays which sheet the min or max result is from. I'm not looking to use the sheet name -- I can use the cell on each sheet with the title. I've googled this, and have yet to find anything. :-/ Here's the formula I used to select the min/max cell values: =MIN(INDEX(Hours17,MATCH(MIN('2014-02-17'!B4:B27),'2014-02-17'!B4:B27,0)),INDEX(Hours18,MATCH(MIN('2014-02-18'!B4:B27),'2014-02-18'!B4:B27,0)),INDEX(Hours19,MATCH(MIN('2014-02-19'!B4:B27),'2014-02-19'!B4:B27,0))) This is much more complex than I'm used to, and may not even be the right way to do it, but it's worked. So, the lowest # from the selected ranges on the three sheets is selected. Now how can I get a formula that will select a matching text cell on each sheet depending on which sheet the min answer was retrieved from? Hope that makes sense? Thanks in advance!!

ANSWER: Evelyn,

Since I don't know what Hours17, Hours19 or Hours19 refer to so it would be helpful to see your workbook and I believe I can work out a solution for you.

You can send it to twogilvy@msn.com

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Hello,

Thanks for responding so quickly, and sorry I didn't get back to you sooner.

The data isn't owned by me, so I'd rather not share it, if you can work without it?

The named ranges (Hours17, Hours18, Hours19) refer to rows A4:A27 on each of three pages. The other ranges in the formula use the same rows as the named ranges, for each of the three pages where I'm looking for data. The formula looks in the B column of all three pages for the lowest value, and then supplies the one lowest value found on all 3, from the corresponding row in A1 of the page with that lowest value (this is on a fourth worksheet - a data summary page). My next step is for the name of the page (of the three pages the formula searches) to automatically come up in the cell next to the result of the formula above. Basically, if the lowest value in the B column is from sheet 2, then the title of that page that I've inserted in cell A1 of each page needs to come up next to the result from sheet 2. This is where I'm stuck. :-/

Evelyn,

this worked for me:

=INDEX(CHOOSE({1,2,3},'2014-02-17'!A1,'2014-02-18'!A1,'2014-02-19'!A1),MATCH(G1,CHOOSE({1,2,3},INDEX(hours17,MATCH(MIN('2014-02-17'!B4:B27),'2014-02-17'!B4:B27,0)),INDEX(hours18,MATCH(MIN('2014-02-18'!B4:B27),'2014-02-18'!B4:B27,0)),INDEX(hours19,MATCH(MIN('2014-02-19'!B4:B27),'2014-02-19'!B4:B27,0))),0))

change the G1 in the formula to refer to the cell that contains your original formula.

so if your original formula was in Z25 the formula would be

=INDEX(CHOOSE({1,2,3},'2014-02-17'!A1,'2014-02-18'!A1,'2014-02-19'!A1),MATCH(Z25,CHOOSE({1,2,3},INDEX(hours17,MATCH(MIN('2014-02-17'!B4:B27),'2014-02-17'!B4:B27,0)),INDEX(hours18,MATCH(MIN('2014-02-18'!B4:B27),'2014-02-18'!B4:B27,0)),INDEX(hours19,MATCH(MIN('2014-02-19'!B4:B27),'2014-02-19'!B4:B27,0))),0))

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks! And wow, that is some formula. There is no way I'd have figured that out on my own. I appreciate your super-fast response. I'm going to give it a try right now. |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.