Posts by SerenityNetwork

Thanks Rory.

In a pivot chart, I am using two values on the horizontal axis (axis categories). The Month/Year is sorted by Class Count (ascending), but when I expand both axis categories (to show both the Class Session and Month/Year) then the formatting of the Month/Year changes to horizontal and will not fit in the space allotted. I have not been able to figure out how to have both the Class Session and Month/Year to rotate 270 degrees. Only the first of the two values will rotate. The other will be horizontal.

How do I show both values on the horizontal axis and get both to format rotated 270 degrees?

Thanks in advance,
Andrew

Never mind. I figured it out.

The cross post is located at: http://www.msofficeforums.com/…ce-number.html#post120884

At this point in time there have been no responses.

I can use the BINOMDIST to find the sample size needed when I provide the required accuracy, confidence level, and number of errors. But I'd like to go the other way and find the number of allowed errors when I know the required accuracy, required confidence level, and number of trials. Is there a similar statistical function or formula I can use to do this? I've searched, but I'm not familiar enough with statistical functions to know if I'm just missing it or not.

Thanks in advance,
Andrew

PS. Cross-posted. No reply in earlier MS Office forum.

Perfect Skywriter. It didn't even cross my mind to use INDEX. Thank you very much.

As shown in the two tables below, I need to return information based upon both Location and Time. I can use the MATCH function in an array formula, but the criteria (including time) must match exactly. That won't do. A vlookup (TRUE) handles inexact times, but I can't think how to add the second criteria. I tried a couple times using a cheater column that concatenates Location and Time in different forms, but couldn't get that to work. Perhaps I could use match to find the row of the Location and then use indirect and VLOOKUP to handle the time within the range found by the match, but that seems really convoluted. I have the sneaking suspicion that I'm just having a brain cramp and this should be easy, but for the life of me I'm not figuring it out.

Any help will be greatly appreciated.

Thanks in advance,
Andrew

Update 1: Fixed typo with Data Table times.
Update 2: It looks like I can do it using MATCH to find the Location range and then VLOOKUP (TRUE) to find the Information by the Time (see attachment). But it still seems to me like there should be something easier.

Results Table [TABLE="align: left, border: 1, cellpadding: 1, width: 500"]

Location

Time

Information (from Data Table)

Location 1

06:05:32

Needs to return "Info A"

Location 1

07:10:15

Needs to return "Info B"

Location 1

09:05:05

Needs to return "Info C"

Location 1

15:15:15

Needs to return "Info A"

Location 2

06:05:32

Needs to return "Info E"

Location 2

15:15:15

Needs to return "Info F"

Location 2

16:00:00

Needs to return "Info G"

[/TABLE]

Data Table [TABLE="align: left, border: 1, cellpadding: 1, width: 500"]

Location

Time

Information

Location 1

00:00:00

Info A

Location 1

07:00:00

Info B

Location 1

08:00:00

Info C

Location 1

00:00:00

Info A (Perhaps this row isn't needed.)

Location 2

00:00:00

Info E

Location 2

15:00:00

Info F

Location 2

16:00:00

Info G

Location 2

00:00:00

Info E(Perhaps this row isn't needed.)

Location 3

00:00:00

Info I

[/TABLE]

Re: Set all option buttons in worksheet to off

Well, I tried the code below that I found on one of the suggested threads. But it didn't work. However, I realized that just clearing or setting to zero the value in the linked cell will deselect the radio buttons. That works just as well for me and I know how to do that task.
Thanks,
Andrew

For i = 1 To ActiveSheet.OLEObjects.Count 
    If TypeName(ActiveSheet.OLEObjects(i).Object) = "OptionButton" Then 
        ActiveSheet.OLEObjects(i).Object = True 
    End If 
Next i

I have a worksheet with multiple group boxes (form controls) and within each I have multiple option/radio buttons (form controls). I know I can deselect each in a macro by naming each option. But that is both a pain and problematic, as I will delete and add options from time to time. I would prefer not needing to change my macro every time I add a new option.

What would be a macro that would simply deselect all options in a worksheet?

Thanks in advance,
Andrew

I'm using the BETAINV function to tell me the minimum sample size I need and the number of errors I can have for a given sample size, based upon variables of confidence level and required accuracy. But the way I'm calculating the minimum sample size is pretty much brute force. (Please see attached workbook.) I'm thinking there must be an easier way to calculate my minimum sample size. I don't know if there is a unique function that will do this, or a method to manipulate or nest the BETAINV function so that it only returns the Minimum Sample Size (for a given confidence level, required accuracy, and possibly number of errors). Any assistance will be appreciated.

Thanks in advance,
Andrewforum.ozgrid.com/index.php?attachment/63838/

Re: Return cell references to multiple matches

Perfect. Thank you yet again.

Also, the simpler string is making it a little easier for me to follow. Although I can't say I've fully digested it yet.

Regards,
Andrew

Re: Return cell references to multiple matches

Well, working from what you provided I thought I could finish up my last need but I'm stuck.

Say for instance when looking for a match on "fed oatmeal" the UDF returns in cell D3:
A2
A7
A8
A12

I was wanting to also append the text from the cell where the match was found. For example D3 would have:
A2: Tom must be fed oatmeal.
A7: Oatmeal must be fed to Tom.
A8: Babies must not be fed oatmeal.
A12: I fed oatmeal to the dog.

I'm not getting how to accomplish this within the same cell. Even doing it in the next column, my method is a kludge.

My apologies for not putting this in my initial request. I was thinking I could figure it out.

Thanks,
Andrew

Re: Return cell references to multiple matches

That is perfect. Thank you very much.

Re: Return cell references to multiple matches

That's elegant. Thank you!

I just tweaked it a bit to put in a line break, instead of a comma, and to trap for errors.

=IF(ISBLANK(B2),"",IFERROR(SUBSTITUTE(TRIM(aconcat("A"&SMALL(IF(ISNUMBER(SEARCH(B2,$A$2:$A$11)),ROW($A$2:$A$11)-ROW($A$2)+2,""),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,"*"&B2&"*"))))," "))," ",CHAR(10)),""))

Just in case I can't convince others to allow a UDF, I've tried working on just using an array formula. But I've run into a stumbling block and can't see where I'm going wrong. If you don't mind, would you please take a look? The objective is the same as you have already created, but since I can't return the results in one cell then I'm trying to return the results in multiple cells. For example Result 1 appears in G5, Result 2 appears in H5, Result 3 appears in I5, and so on. Then it repeats on the next row for the next criteria. I'm trying to use the MATCH function. When I use it alone, it identifies the correct row. But when I plug it in the larger formula then it's not working. I'm stumped.

THIS WORKS:  =SMALL(MATCH("*"&$D5&"*",$A$5:$A$16,0),1)

THIS DOES NOT WORK:  =IFERROR(INDEX($A$5:$B$16,SMALL(IF(INDIRECT("A"&SMALL(MATCH("*"&$D$5&"*",$A$5:$A$16,0),1)+4)=$D$5,ROW($A$5:$A$16)-4),COLUMN(A1)),2),"")

Thanks again,
Andrew
EXAMPLE ATTACHED: forum.ozgrid.com/index.php?attachment/61824/

I can use the following to return the cell reference in Column-AA that has a match to the content in E2.
=SUBSTITUTE(ADDRESS(1,COLUMN(AA:AA),4),"1","")&MATCH("*"&E2&"*",AA:AA,0)

If E2 contained "fed oatmeal" and AA20 contained "Bob should be fed oatmeal twice daily" then AA20 would be returned.

But what if I have multiple occurrences of "fed daily"? Say I have: "Bob should be fed oatmeal twice daily" in AA20, "Babies should be fed oatmeal" in AA32, and "Tommy should never be fed oatmeal" in AA40. Is there a clean way to return AA20, AA32, and AA40 in a single cell?

I know I could do it by essentially concatenating the above formula to itself a bunch of times (starting the next concatenation at the row found in the previous), but it would only return a limited number of occurrences. It seems that an array formula could do it, but I'm not sure how to write it.

Any help would be appreciated. (Note: For this task I would greatly prefer not to use macros, if it is at all possible.)

Thanks,
Andrew

Update: I did examine the links to existing threads and found a reference to this solution. It comes close to getting what I need, but it returns the results in different cells. I would really prefer to have the multiple results returned in one cell (if at all possible).

Re: Gathering Values on Workbook Open for use later in subroutines

Whoohoo! Thank you so very much. That is exactly what I needed. Best of all, I can see what you've done. It makes total sense, but I would not have gotten there on my own.

Thanks again,
Andrew

PS. I love your shortcut for newline. I'll be adopting that technique for other lengthy and repetitive code.

Re: Gathering Values on Workbook Open for use later in subroutines

I suspect I'm not giving you all that's needed to guide me, so I've attached a workbook that's an example of what I'm trying to accomplish. (Of course it's not working.)

Basically I need to:

• Load values into memory from a worksheet when the workbook opens.
  
  
  • I'll be loading a maximum of about 40 different values.
  • Most of the time it will just be less than 10 values.
  • Most are strings.
  • All will be less than 15 characters in length and about 1/2 of them will be cell addresses.

  
  

• Be able to use those values in different macros sometime later (not during the initial opening macro run).

Thanks,
Drew

Here is the example: forum.ozgrid.com/index.php?attachment/60783/

Re: Gathering Values on Workbook Open for use later in subroutines

No, I did it within the subroutine.

I have moved them to as you have shown. When I open the workbook, all load properly. I can validate by having the following within my Workbook_Open subroutine.

MsgBox ("Address = " & varWorkbooksAddress & vbNewLine & _
        "Row = " & varWorkbooksRow & vbNewLine & "Col = " & varWorkbooksCol)

But when I go to use them later, they don't see to be available. Here is how I tried.

Sub varCheck()
varWorkbooksAddress As String, varWorkbooksRow As String, varWorkbooksCol As String
MsgBox ("Address = " & varWorkbooksAddress & vbNewLine & _
        "Row = " & varWorkbooksRow & vbNewLine & "Col = " & varWorkbooksCol)
End Sub

I receive the Compile error: Statement invalid outside Type block.

Sorry, I'm still lost.

Thanks,
Andrew

Re: Gathering Values on Workbook Open for use later in subroutines

Never mind. I just tried the above. It doesn't work. I'm missing something.

Thanks,
Andrew

Re: Gathering Values on Workbook Open for use later in subroutines

So...

Dim varWorkbooksAddress As String 
Dim varWorkbooksRow As String 
Dim varWorkbooksCol As String

...simply becomes...

Public varWorkbooksAddress As String 
Public varWorkbooksRow As String 
Public varWorkbooksCol As String

...and the values are accessible from other subroutines?

Is that correct?

Thanks,
Andrew