Posts by EnginerdUNH

Correction: =SUMIF(Names,I2,Overall_Appearance) does work unlike what I thought so it appears to only be when I try to use SUMIFS.

Hi,

I am working with a spreadsheet that has a total of 5 named ranges and I need to use those named ranges to a series of SUMIFS calculations using different combinations of the ranges. One of the named ranges contains a series of names which I then use the UNIQUE formula to list each value only once. From there, I am trying to use the Names range with one of my other criteria ranges called Is_Judge which contains "Yes" and "No" values to do a summation of the values in the other named ranges. When I try to do the following sum using SUMIFS, =SUMIFS(Overall_Appearance,Names,I2,Is_Judge,"No"), it throws me a #VALUE! error. I know that the formula is breaking on the Names,I2 part because if I omit it and just do =SUMIFS(Overall_Appearane,Is_Judge,"No"), the formula works just fine. Oddly enough, using the Names Range and cell I2 doesn't throw any errors if I use say =COUNTIF(Names,I2), only when using SUMIF or SUMIFS. Does this have anything to do with me using the UNIQUE function in cell I2 and then trying to use that value in my sum formula? Do I need to modify the SUMIFS formula to change how it treats the value in I2? Should I be using SUMPRODUCT instead of SUMIFS? Any help is greatly appreciated.

Hi,

I am working with a data set that has the same x-axis data for three different sets of outputs. Currently, I am displaying them on the same chart but since the magnitudes of the data on y-axis vary quite a bit, the higher magnitude data overtakes the chart and the lower magnitude data looks relatively flat and/or zero. I know that I could cheat the data by putting them all on their own charts, putting the charts relatively close together and then grouping them but that isn't really something that I'm looking to do unless it's my only option. What I would like to do instead is to take the data and put them all on the same chart sort of like a panel chart which I know how to do but instead of having one x-axis, one y-axis with groups separating the data on the x, I'd like to see if there is a way to create a chart like the sketched-out figure I've attached to this post where there's multiple x and y axes with the three data set charts stacked on top of each other.

Hi Jeff,

Thank you for your response. I'm not quite sure your suggestion will work for what I'm trying to do though. I don't care about the ones that are empty and the ones that have more than one element are an array (i.e. I only care about the scalar elements). If I do something like:

Dim Possible(1 To 9, 1 To 9) As Variant
   
   Dim L As Long
   Dim i As Long, j As Long
   
   For i = LBound(Possible, 1) To UBound(Possible, 1)
      For j = LBound(Possible, 2) To UBound(Possible, 2)
      
         If Not (IsEmpty(Possible(i, j)) Or InStr(TypeName(Possible(i,j)), "(") > 0) Then
              'scalar, do some stuff here
         End If
      Next j
   Next i

will this work the way I expect it to?

Hi,

Within my code, I have an array called Possible which is an array of type Variant with size (1 to 9, 1 to 9). Each element within the array can either be:

1) empty

2) a single element

3) multiple elements

what I am looking to do is loop though each element within Possible and see if the number of sub-elements in each position is 1 or not. I have tried using the following code but it is giving me an error:

For i = 1 to 9
    For j = 1 to 9
        If UBound(Possible(i,j)) = 1 then
            'do some stuff here
        end If
    Next j
Next i

Thank you, Logit, one question though... if the error occurs, it would be occurring within the function that I want to restart. I don't think I'm able to call a function from itself, am I?

Hi,

I am wondering if it is possible to write an error handler such that when my main body of code calls a particular function, if while the function is running, an error occurs, the code restarts the function.

Bump

Hi,

So I have the following scenario I’m trying to test out:

There is a multi-stage contest with 30 participants. Participant 1 has a goal of finishing the competition in the top 10. The first round of the competition is complete and Participant 1 would like to determine the highest number of points they can obtain in the next round in order to achieve 10th place or higher.

The assumptions are:

Participant 1’s place after round 2 is >10

The scores of all participants in each round are x>=1 and x<=30

Scores must be integer values

Ties are allowed and the next highest place would take skip a place (e.g. 1, 1, 3, etc.)

is this something that is possible using the Solver tool or do I need to do something more robust using formulas/VBA?

Is it possible to use a formula to do what I’m looking for without always having to have the other workbook open to do it? Or would that only be possible with a macro?

Thank you Pecoflyer for your reply! Ok I’ve dabbled at trying to revise my formula and I’ve come up with the following:

=“‘’”&INDIRECT(C1)&”[“&INDIRECT(C2)&”]”&”Sheet Name’”&INDIRECT(cell to grab)

And it’s no longer bringing up the file explorer when I hit enter but now I get a #REF! error. Any ideas what I’m doing wrong??

Hi,

I am wondering if it is possible to use a helper cell to update the file path/name for some linked data that I am working with. Right now I have several cells which are formatted as follows:

=‘File Path[File Name]Sheet Name’!Cell Ref

and I would like to know if there is a way that I can modify this to reference a cell that contains the file path instead. The reason for this is that I am looking to use this file for multiple sets of data which have the same sheet name and cell reference that I would be working with but the file path and file names would obviously change. I was trying to do something like the following:

=‘&C1&”[“&C2&”]Sheet Name’!Cell Reference”

but every time I go to enter that, excel immediately brings up the file explorer for me to select the file I want to link to.

That works out extremely well for me! Thank you!! Also, I realize that I meant to put this thread under the formulas section so thank you also for answering it despite the fact that I put it in the wrong area. The only change that I made with what you sent was I removed the drop down box that you added for year selection since I want to be able to put it other options besides just the years in the first row of the table. I tested it out with entering numbers like 6, 9, 13, etc. and it still works the way I want it to. Thank you again for your help

Hi,

I have an array(1 to 5) variant type variable and am trying to create a macro that will check to see if all spots within the variant array are filled or not. Is there a way that I can accomplish this with a simple if statement like:

if there are no empty spots in array(1 to 5) then

'do some code here

End If

without having to loop through the array every time to check it? Is the only way to accomplish this using something like:

If Not IsNull(array(1)) And Not IsNull(array(2)) And Not IsNull(array(3)) And Not IsNull(array(4)) And Not IsNull(array(5)) Then

'do some code here

End If

Roy,

Thank you for your reply. Please see the stripped down workbook with only the pertinent information included for this question.

I was able to get the following formula to work to sum up all rows in the correct column using SUMPRODUCT and HLOOKUP:

=SUMPRODUCT(HLOOKUP(G2,P1:U13,{2,3,4,5,6,7,8,9,10,11,12,13},TRUE)

Where G2 is the value used to pick the correct column and P1:U13 is the lookup table. But when tried to make the stuff in curly brackets dynamic by entering the following into a helper cell

=IF(B2<N2,”N,”,””)&IF(B2<N3,”3,”,””)&IF(B2<N4,”4,”,””)&IF(B2<N5,”5,”,””)&IF(B2<N6,”6,”,””)&IF(B2<N7,”7,”,””)&IF(B2<N8,”8,”,””)&IF(B2<N9,”9,”,””)&IF(B2<N10,”10,”,””)&IF(B2<N11,”11,”,””)&IF(B2<N12,”12,”,””)&IF(B2<N13,”13,”,””)

And then changing the static part of my original formula to “{“&LEFT(M19,LEN(M19)-1)&”}” but now I’m getting #REF! error.

Hi,

Let’s say I have a column like the following:

I need to develop a formula that will sum all rows whose date in column A is greater than today for the column that I specify in a given cell.

Well I think I solved the issue of the INDIRECT formula...turns out I needed to format it like INDIRECT("'"&Ref Cell&"'!Range")

Hi,

I have a spreadsheet with multiple sheets that each contain a table of data which contains a series of dates and other criteria with which I want to do a comparison. The basic structure of the columns is as follows: date, criteria1,criteria2, criteria3,desired value column.

I am trying to create a formula that will pull the value from each table that corresponds to the closest date less than or equal to the date specified that meets the desired value in the columns for criteria1, criteria2, and criteria3. I though about using the SUMPRODUCT formula since it will make all values that don't meet one or more of the criteria equal to zero with the structure =SUMPRODUCT(('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value)*'Sheet Name'!E2:E20)

My concerns with this formula are:

1) What happens if there are multiple date values that meet the criteria values provided for criteria1, criteria2, criteria3? How do I tell the formula to only give me the most recent value for this subset?

2) The above example formula is not dynamic and I attempted to modify this by using the INDIRECT formula since I have the sheet names listed within cells on the sheet I am trying to use the above formula but excel kept throwing a "#REF!" error. It would also not work when I tried to use the INDIRECT formula to reference a single cell rather than a range. Does this have anything to do with whether or not the sheet name has a space in it?

I also thought about using a combination of the INDEX and MATCH formulas as follows {=INDEX('Sheet Name'!E2:E20,MATCH(1,('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value))} again replacing the Sheet Name callout with a dynamic reference to to the cell containing the sheet name using the INDIRECT formula.

I have been working VBA to scrape website HTML code using the Microsoft HTML Object Library and the Microsoft Internet Controls Library. Now that support for Internet Explorer is being phased out, I am trying to switch my code over to scrape Google Chrome using the Selenium Type Library which is an open source download. The website I am trying to pull data from into excel has the following line of HTML code that I am interested in assigning to a variable:

<div jsname="iXWWee" class="enWFYd KDN9Hf" style="left: 39px; display: block; transform: translate3d(85px, 0px, 0px);">Feb 4, 2021</div>

I have been able to successfully assign a value to a variable using the following lines of code

Private ch As Selenium.ChromeDriver
Sub Test()
    Set ch = New Selenium.ChromeDriver
    
    ch.Start , "https://www.google.com"
    ch.Get "https://www.google.com/search?q=us+vaccine+tracker&oq=us+&aqs=edge.0.69i59l2j69i57j69i60l4.2010j0j1&sourceid=chrome&ie=UTF-8"
    
    Dim Element As Selenium.WebElement
    Set Element = ch.FindElementByCss("div[class='enWFYd KDN9Hf']")
    Debug.Print Element.Attribute("innerHTML")
end

But what you will notice is that the the HTML code I provided has innerHTML which changes based on where your cursor is on the screen. The code above works but it only pulls the last instance where the innerHTML value is "Aug 3, 2021". What I want is something that will pull all of the data and not just the last instance. I tried to test it by changing the second to last line of code to Set Element = ch.FindElementByCss("div[class='enWFYd KDN9Hf'] [innerHTML='Feb 4, 2021']") but when I run the subroutine, I get an error saying that this element can't be found. I have also tried changing the last three lines as follows but it still is only pulling the last instance of this div tag.

Dim Elements As Selenium.WebElements
Set Elements = ch.FindElementsByCss("div[class='enWFYd KDN9Hf']")
For Each Element in Elements
    Debug.Print Element.Attribute("innerHTML")
Next Element

Am I not setting up my Css selectors correctly? Anybody that is familiar with how to use Selenium with VBA, I would greatly appreciate your help.