I get an export from a program where the report for several entities and the worksheet looks like it's a conversation from a Word doc or something.

The worksheet contains header information, filter information, meta data, the actual table itself for the entity, footer info, and then the header info for the new entity's table on repeat on one worksheet.

The table itself is what I'm interested in and luckily the all the tables I need start in column A and are the same format for every entity.

So I need a way to search the worksheet in Column A for the list of entity ID numbers I have and everywhere that number is listed in column A copy X rows over into a new sheet so that I can compile all the disparate tables into one table.

There's a bunch of ways to accomplish that depending on a few things. If the data for each entity is the same, such as the same number of row after their ID number and same number of non-important cells in between, you can just count and assign unique IDs to a helper column and then just sort.

ENTITY ID
ROW 1
ROW 2
ROW 3
ENTITY ID
ROW 1
ROW 2
ROW 3

In Column B, setup counting for 1, 1, 1, 1, 2, 2, 2, 2, etc. If only Entity ID is Column A and the data is Column B, just repeat the same unique ID for however many rows of data each entity has and sort. And then VBA or manual (depending on size) can copy to new sheets.

I have a large data set where I combine multiple criteria for very granular averages for every row. Many instances have 6-7 criteria matching for AVERAGEIFS, but not all. Instead of N/A, I want to return the next closest matching instance, e.g., if there's 0 instances of my first 7 criteria, check for the next 6.

My current solution is IF(COUNTIFS(7 criteria)>0,AVERAGEIFS(7 criteria),IF(COUNTIFS(6 criteria)>0, and so forth. This works but is bulky and annoying and I'd prefer an easier way if possible.

Is there a known workaround for this or do I need to code up some custom VBA? My initial instinct is thinking some loop for each cell in the range to check COUNTIFS, starting from 7 on down. So basically IF COUNTIFS(7 criteria)>0, assign 7, if not, check for 6, etc. With each cell uniquely ID'd with their maximum level of, I can do whatever, I guess. I feel it might actually be clunkier or more time consuming with VBA where I'm just doing the same thing in the backend, but more difficult.

But maybe just defining the big formula as worksheet function in VBA would speed **** up and be easier to edit later?

I'm trying to create some new info on my current sheet I use to track MTT results. I would like to have ability to track hourly rate via entering start time of session and end time of session.

Currently my sheet only accounts for start/end time of each individual MTT. Any idea how I can aggregate that data and implement this?

Also if anyone has a MTT tracking sheet that is already made please shoot me a link to DL the template. Would love to try it out!

Sent from my SM-G965U using Tapatalk

Need help with 2 formulas

1.)
For example, I can enter :
=TODAY()-C7 & " Days" into Cell O7 and everything is fine. It will then show the correct number found from using the formula followed by the word Days. Such as "23 Days".

The issue I am having is I'd like O7 to remain blank unless there is the word "Yes" entered in cell N7.

What formula would I use in O7 that would use the above formula, ONLY if the word "Yes" is entered into cell N7?


2.)
I'd like to see how many days it took for something to happen, or how many days are pending it is still open
For this example, I can enter into J4:
=I4-C4 & " Days" and everything will work fine if I have a date entered in I4. C4 is when something first occurred that requires an action. If there is a date entered into I4 it means that is the date the action took place so using that date is good as it will show me how many days passed before the action took place.

If there is no date entered in I4, I'd like to use today's date in the formula. That way I can see how many days have passed so far with NO action.

How can I use the date entered in I4 and subtract the date in C4 only if there is a date entered into I4? If no date is entered into I4, I'd like to subtract today's date from the date entered into C4.

I'd also like for the cell in J4 to remain blank if no date is entered into C4.



Thank you in advance!!!

Both of these should be solved by wrapping the “if” formula.

So +if(n7=“yes”,TODAY()-C7 & " Days",””)

Same concept for number two.

+if(i4=“”,today()- C4 & “ days”, I4-C4 & " Days")


Sent from my iPhone using Tapatalk

Thanks Miamicheats, but I'm still not getting it. My fault I'm sure!

Do I add your suggested formula to the end of the formula I have now? Or is your formula the complete formula I should use instead of the one I was using?

I guess I need a little hand holding to get what I'm looking for. What exactly should I enter in cell O7 (from example 1). And exactly what should I enter in cell J4 (from example 2)?

Let me make sure I explained example 2 well (I moved a couple columns from my original question):
* When a specific event takes place, the date of this event will be entered into cell C4.

* The words "Yes" or "No" will then be entered into cell D4 (only two choices to be entered into this cell)

* If Yes is entered, I'd like the formula entered into cell P4 to activate. (if "No" is entered, I'd like the formula to be ignored and the cell P4 to remain blank.

* If "Yes" is entered and the formula is active, I'd like to subtract the date of the event from today's date to see how many days have passes since the event (this number should then be followed by the word "Days"..so the result would be something like "28 Days").

* Once everything has been completed for this event, a completion date will then be entered into cell O4. If a completion date has been entered in cell O4, I'd like the formula in P4 to subtract the event date (C4) to be subtracted from the completion date (O4) to give me the total number of days between the event and the completion. This number should also be followed by the word "Days".

Man that sounds complicated. I hope I explained this okay. If I did, what exactly would I enter into cell P4 to either find the days it took to complete the event, or the days that are still pending without completion?

Thanks so much in advance for your help!!!!

The first one is

+if(d7=“yes”,today()-c7 & “ days”,””)

The formula is saying

If d7 is yes then give me the difference of today and the date in c7.

If the input to d7 is anything or than “yes”, leave the cell blank.


Sent from my iPhone using Tapatalk

The second one:

+if(o4=“”,””,O4-C4 &” days”)

It’s saying if O4 is left blank leave p blank. If there is a date entered, calculate the number of days by subtracting O-C adding the word “days”.


Sent from my iPhone using Tapatalk

I'd like to use conditional formatting to format a cell based on another cell.

I can do the first part of what I need. If Yes is entered into E4, I'd like K4 to have a Red background to show the user that cell needs to be filled in.

In K4, I have the following formula for conditional formatting
=E4="YES"

I would then like K4 to go back to no Red background as soon as the user enters information into K4.

How can I format K4 to have a Red background if Yes is entered into E4 and also remove the red background as soon as K4 is no longer blank?


Thanks in advance!!!

I'm on a train so can't check this but have you tried =AND(E4="YES",K4="")

I tried that, but I get the word "TRUE" in K4 as soon as I enter that formula.

You're adding that formula as a conditional formatting rule, not in the cell itself right? I just tried the above and it worked.

As you suspected, I must have added that directly into the cell. I just tried again as a conditional formatting rule and it works perfectly.

THANK YOU!!!

Is there an easy way to automatically import the date from this table into excel? I have tried, web scrapers, table capture, excel web import.....nothing seems to copy the table? Any help would be appreciated.

https://www.sharpfootballstats.com/t...ics--off-.html

I can't find the date. Did you mean data?

sorry typo meant data

Running into a bit of a problem someone might be able to help with.

Attempting to Sum a group of data for multiple columns based on criteria in column A. So If I have the word BLUE appearing on rows 1,7, 18, 27, and 265, and values for each of those appearing in columns B-G, I want the total sum for all instances of BLUE across all columns. I was able to find that this is doable using the sumproduct function within the same worksheet, however, if I attempt to use the same formula on a separate worksheet, I am getting a VALUE error. I am able to use sumproduct and return a result on a separate worksheet using only a single column, but when I attempt to extend this to multiple columns from the original sheet, I get the VALUE error.

Any ideas on if this is doable? I have been searching for a while now, and have found options for single columns and multiple columns as long as the formula appears on the same worksheet, but not for multiple columns with the formula on a separate worksheet.

Thanks

Do the separate worksheets use the same ranges? If so, is this what you're looking for?

Is there a way to snake long columns without using the, "paste in word, create 2 columns" method?

Looking for an equation to spit out number of lines of bold text over a row -

I have about half dozen workbooks each with multiple worksheets. I'd like a way to organize them all so I can easily navigate to find info. From reading about this it looks like my options are:

-Open all the workbooks and then View>Arrange All

I tried this with cascade but the issue I have is once I select one of the workbooks then I have to cascade again in order for all the headers to be visible again for easy reference

-Create a table of content with Hyperlinks

I did this tonight but each time I select a link I get a security popup that forces me to select yes/no. I did some googling to try and disable it but I could only find fix for older versions of excel(I'm using Microsoft Office Home and Student 2019)

Any possible solutions to the above problems? Any suggestions for a better way to go about organizing them? Any help would be greatly appreciated.

Thank you

Hello,

I am struggling with adding legends, I've tried online vids but none have explained exactly what I need to accomplish.

I have a bar chart with 10 different bars. Each bar is represented on the chart by a different three or four-letter ticker. I need to create a legend that shows the name that represents EACH ticker.

Any help massively appreciated!

Thanks

Basically for any random flop (22,100 flops), and any random hand (1326 hands) ... is it possible to create something in excel that tells you what your hand strength is. Made this picture to show what I am trying to do (automate red column). Maybe its been done before? Thanks.

not sure if i can help or not but if you uploaded an image to see what you're describing i may be able to assist

there's a number of ways you may have fouled it up and it's just easier to share images than guess all the possibilities

you could run a bunch of if functions but preferably with VBA

but more importanly, it won't scale. I imagine you're doing this for a large database, once you get beyond a few thousand lines it's going to start grinding to a halt - we're talking several minutes to open the file, several minutes to add some new inputs, several minutes to save the update

I'm thinking R may be better for this, maybe python?