Posts by EnginerdUNH

    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

    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:


    date135101520
    1/1/21111313151517
    2/5/21121214141618
    3/5/21111313151517
    4/2/21111313151517
    5/7/21121214141618
    6/4/21111313151517
    7/2/21111313151517
    8/6/21121214141618
    9/3/21111313151517
    10/1/21111313151517
    11/5/21121214141618
    12/3/21111313151517



    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.

    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:

    Code
    1. <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

    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.

    Code
    1. Dim Elements As Selenium.WebElements
    2. Set Elements = ch.FindElementsByCss("div[class='enWFYd KDN9Hf']")
    3. For Each Element in Elements
    4.     Debug.Print Element.Attribute("innerHTML")
    5. 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.

    I am working with the following formula that calculates an average value, excluding the first difference: =SUMPRODUCT((A$2:A$10=A2)*(A$1:A$9=A$2:A$10)*N(+C$2:C$10)/(COUNTIF(A$2:A$10,A2)-1)) which works really well when I have a static range that I'm not adding to. What I want to do now is create a dynamic formula that works with a table so that the formula will work regardless of how much data I decide to work with. The problem that I am having is that I am having is I can change A$2:A$10 to Table1[Date] and C$2:C$10 to Table1[Difference] but I can't figure out how to address the range A$1:A$9. Is there a way that I can set up a named range that automatically updates? or use the just incorporate the OFFSET formula into the formula that I'm modifying?

    Hi,


    I am hybrid Gantt chart of sorts. I'll try to explain what I mean by that . Let's say I have the following test data that I'm working with:

    Test Name Start Date Duration
    Test 1 1/1/2021 10
    Test 2 1/5/2021 17
    Test 3 1/10/2021 12
    Test 4 1/17/2021
    Test 5 1/24/2021 19
    Test 6 2/3/2021 14
    Test 7 2/17/2021


    What I want to do is create a hybrid Gantt chart where the tests which have a start date and duration are represented by bars and the tests which only have start dates (meaning they have not completed) are represented by a symbol. I have created the basic Gantt chart structure by creating a stacked bar chart with the test name on the vertical axis and the start date and duration on the horizontal axis with the date set to no fill. I then created a new data series where the tests with durations are represented by N/A and the dates without durations are the start dates. But this is where it stopped working for me...When I first added the new data series, it displayed the data as bars going from the start data and extending all the way to the max value of the date axis. When I tried to change the chart type for the data series to a line chart with data markers, it added a second vertical axis where that axis is all dates. Can anyone help me???

    Try this


    Thank you Carim and Roy! Roy, quick question for you...The piece of code that you provided about changing the automation security settings, should that be placed at the very beginning and very end of my code (i.e. before I've initiated the loop to go through each file and just before I'm about to end the sub routine) or should it be placed within the loop?

    I am working on a macro that will loop through a series of workbooks, open them, compare their contents to the reference cell in the workbook that the macro works off of, and then do stuff in the main workbook if condition is met. The thing that I'm running into is that workbooks that I'm trying to open using my macro are macro-enabled workbooks and every time a new one is opened by the macro, a prompt comes up to either enable or disable macros. This is ultimately time consuming because I'm having to press either enable or disable every time a new workbook is opened. Is there a way that I can make it so that excel doesn't keep asking me this every time there's a new workbook being opened? either in the settings for excel itself, my macro code, or something else?

    Thanks Carim! I'm not sure why my formula wasn't working even though I was pressing CTRL + SHIFT + ENTER after constructing the formula. Nevertheless, I tried your formula and for some reason excel liked your way better. Now I actually get what I'm trying to find. Thanks for the help!

    ,Hi,


    I'm working with data where I've been given a table of dates and times corresponding to different events. What I am trying to do now is group those times together to find both the percentage of events that occur during specific time range and then to also find the median event time for that range. For example, I need to find both the number of events occurring between 12:00AM - 5:59AM, 6:00AM to 11:59AM, 12:00PM - 5:59PM and 5:59PM - 11:59 PM. Finding the number of events was easy, I just used a simple COUNTIFS statement to count all times between the bounds of each bin. As for the median time calculation, I'm getting stuck. I tried using the following formula to no avail: MEDIAN(IF(AND(Table1[Time]<=X2,Table[Time]),Table1[Time])) where Table1[Time] represents the entire set of time data in Table1 and X2 and Y2 represent the first bin of time range (12:00AM - 5:59PM) but it kept throwing back what appears to be the median of the entire data set. I was thinking I'm better off trying to come up with a macro that will loop through each of the data points and using and if statement, add them to the correct array to then take the median of. Any insight and help on structure/syntax is greatly appreciated.

    If the values are numbers you can use COUNT rather than COUNTA, if the values are text use

    =LOOKUP(2,1/(E2:E10<>""),E2:E10)

    COUNT is exactly what I needed!! I realized after I posted this that I posted under the VBA section of the forum rather than the excel formulas section but thank you for providing me the answer that I needed, Fluff13.

    I'm sure I'm not the first person to have this question but I haven't been able to find anything online to help me solve my problem. I have a table of data and I need to pull the data from the last non-blank row in one of the columns in my table. Sounds simple enough but that's where you would be wrong. Normally I would be fine using =INDEX(Table1[Column X],COUNTA(Table1[Column X])) to find the last non-blank row of data. The problem with is that the column that I need to pull data from in the table that I'm working with isn't truly blank. Instead, there's a formula which is telling it to check for a certain condition, if that condition is met then spit out a value else the value in that cell is "". Even though the value in that cell may be "", since there is a formula which tells excel how to populate that cell, when using INDEX and COUNTA, excel treats the cell as if it's not blank and returns the wrong answer. What I need to figure out is how to find the last row which contains something other than "". For example, let's say I have the following table:

    DateData 1Data 2Data 3Sum
    January 20201236
    February 202054615
    March 202028313
    April 2020

    In the example table, let's say I need to pull data from the "Sum" column. In the example, the sum column would be populated with =IF(SUM(Data 1:Data 3)>0,SUM(Data 1:Data 3),""). If I were to use =INDEX(SUM,COUNTA(SUM)) then excel would return "" because April 2020 shows "" and is not truly blank because there is a formula inside the cell. What excel SHOULD be returning is March 2020's value of 13.