counting and percentages in multiple sheets

**kmfdm515** · 01-22-2008, 07:43 PM

i apologize in advance for starting 3 threads in 1 day, but i thought each one was different enough from the last top warrant it's own thread.

here's an example of what i am trying to do. i have 4 worksheets with various names. i have an 5th worksheet with a named list (TabNames) of the 4 worksheet names

each of the 4 worksheets have cells with percentages in them.

i would like to check a cell from each sheet and see if the percentage is equal to or above a certain %, for example anything equal to or above 90%. then return the number of cells that meets or exceeds that 90% criteria.

so for example

sheet1 sheet2 sheet3 sheet4
A
1 80% 91% 76% 82%
2 80% 95% 92% 43%

so checking cell A1 in list TabNames would return 1, and checking cellA2 would return 2.

thanks...last topic today i promise

just starting to learn about excel formulas...

**Leith Ross** · 01-22-2008, 09:54 PM

Hello kmfdm515,

The easiest approach is to use a UDF (User Defined Function) to do this across multiple sheets. Here is the code and instructions on how to install and use the UDF.

UDF Macro Code

Please Login or Register  to view this content.

Installing the UDF Code
1. Copy the code above using CTRL+C
2. Open your Workbook in Excel.
3. Right Click on the Name Tab of any Worksheet.
4. Click View Code in the pop up menu.
5. Press ALT+I to activate the Insert menu.
6. Press the letter m.
7. Paste the code into the module using CTRL+V
8. Save the UDF code using CTRL+S
9. Press ALT+Q to return to Excel.

Using the UDF
This function takes 3 arguments:
RefCell_Addx - A string indicating the cell you want to check
Compare_Flag - A string indicating the type of compare to perform. There are five strings "<=", "<", "=", ">", ">="
Percent_Value - Number expressed as a valid percentage.

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**kmfdm515** · 01-22-2008, 11:26 PM

That is fantastic! Thank you so much!

One more question...how do I edit that formula to make it conditional on some text in a certain cell on each sheets? like if i only want it to count sheets that have text "x" in cell C9, etc.

**Leith Ross** · 01-23-2008, 12:00 AM

Hello kmfdm515,

To test for a string value the UDF would be...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**kmfdm515** · 01-23-2008, 12:15 AM

thanks so much!

what language is this code? VB?

**Leith Ross** · 01-23-2008, 01:52 AM

Hello kmfdm515,

The language is VBA (Visual Basic for Applications). This is used by all the Office suite programs: Excel, Word, PowerPoint, Access, and Outlook.

Sincerely,
Leith Ross

**kmfdm515** · 01-23-2008, 02:15 PM

expanding on this a bit, would it be possible to then list the sheet names in some cell and specify whether they met the criteria or not?

**Leith Ross** · 01-23-2008, 05:27 PM

Hello kmfdm515,

You should start a new thread to get an answer to that question.

Sincerely,
Leith Ross

**kmfdm515** · 01-24-2008, 10:53 AM

Thanks Leith, I'll do that.

With these formulas you gave me, though...they aren't re-calculating after any changes are made to the numbers in the sheets, even after saving and re-opening my file. i have to manually go back to my cells with these formulas and press enter for every one to get them to re-calculate.

am i missing something here?

**Leith Ross** · 01-25-2008, 04:03 PM

Hello kmfdm515,

I forgot to add an important line of code to the macro. The line in blue makes sure the function gets recalculated whenever Excel recalculates the sheet.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**kmfdm515** · 01-30-2008, 01:30 PM

Hi,

That seems to work...when i make a change in another sheet, the formulas recalculate.

but then...if i leave the sheet open for ~ 10 minutes...all the cells with these formulas go back to zero??? what is causing that?

**vane0326** · 01-30-2008, 09:48 PM

May I see a sample of your workbook?

**kmfdm515** · 01-31-2008, 11:19 AM

my workbook has like 100 worksheets so it's really huge. is posting it here necessary to determine what's causing that issue?

**Leith Ross** · 01-31-2008, 01:23 PM

Hello kmfdm515,

The 10 minute time interval sounds like the default setting for the Auto Save. What I don't understand, and can't reproduce is the data loss after saving the workbook. You mention you have over 100 sheets in the workbook. Perhaps the problem is related to memory management. What version of Excel are you using?

Sincerely,
Leith Ross