Forum Widgets
Latest Discussions
Sorting numbers
Hi, I have a excel sheet with x number of columns. I have formated the rows so that if there is a specific number it is marked in red. See picture below. The numbers that are marked are Top row 0 - 3 and 9 - 13. So numbers 4-8 shall not be marked Middle row 0 and 6 - 13. So numbers 1-5 shall not be marked Bottom row 0 - 1 and 7 - 13. So numbers 2-6 shall not be marked What I now whant is to count all columns that are not marked red. As of now I do this manual with marking with "1" below and use function Count. Someone that can help me with a function for this? Most thankful.PerJan 24, 2025Occasional Reader110Views0likes13CommentsSearch Formula help!
Help you lovely genius people. Is there a formula to check a text string in a cell and if it contains a sequence of 4 or 5 numbers in a row return the 4 or 5 digit number? For some backstory... I'm looking at a 2 columns of data from Xero and within some of the cells there will be a Job reference, but the text strings are not in any specific order/format. I was using this formula before... =IF(M1824="Expense",IFERROR(UPPER(MID(E1824,SEARCH("JOB",E1824,1),3)&"-"&MID(E1824,SEARCH("JOB",E1824,1)+4,4)),UPPER(MID(D1824,SEARCH("JOB",D1824,1),3)&"-"&MID(D1824,SEARCH("JOB",D1824,1)+4,4))),"") Columns D & E contained the text and this returned a result in a format I could then use for lookups i.e. "JOB-####", or an error if "JOB" wasn't in either column. This meant a lot of manual checking of error cells. To add to the fun, we have just breached JOB-9999 so I'm now also having to look for JOB-10000+ (I don't envision having to worry about 6 digit job reference but if there is a way to future proof then bonus.) Annoyingly, some of the data is system generated when invoices are processed and POs are matched, but some comes from expense claims and is manually entered, so although Xero will always give the reference as "JOB-####" or "JOB-#####" sometimes the reference will appear as "Job ####" or "job no ####" or just "####" or any other random combination the engineers think to put on their expenses, so if i can just reference a cell and pull back any 5 digit number and if there's no 5 digit number then look for a 4 digit number? I hope someone out there can help, I've tried a few things but can't seem to get anywhere close to a useable result. Living in hope, MarcusSolvedEmJayLeppJan 24, 2025Copper Contributor43Views0likes5CommentsY-Axis Not Grouping?
Hi there, I am working on creating a block schedule chart, you can see the table at the top left and what my chart looks like right now. The y-axis represents a different room. I want the block schedule to overlay rooms so that there are multiple blocks in the same row. For example, TUA TOPS OR INTRAOP 2 would have the 4 blocks displayed on the same row. My problem is that I cannot seem to get them to group together. You can see that in the table I have identified duplicates to ensure that the naming conventions are the same. I have also formatted column C as text. I have also assigned the Axis Type to be "Text axis" as you can see on the right of the screen. Any suggestions? I think ChatGPT is sick of trying to help me at this point lol.ErikElisson21Jan 24, 2025Occasional Reader5Views0likes1CommentSkipping cell while pasting data
Hi, I'm trying to figure out if there's a way to skip cell at constant interval while pasting Huge amount of data. Something that will act as a pasting template/guide eg.: I need to copy huge amount of data (around 5000 cell) in a row but I need to let 2 blank cell every 5 Data cell. So lets say column C is where I want to paste the data, but I need it to skip cell C7,C8 and then C14,C15 (and so on) while pasting the data. like this: Thank youCyressvirusJan 24, 2025Occasional Reader16Views0likes1CommentRemoving a line between two points expands the legend
Hi all, I'm wondering how to avoid the following problem: When I remove the solid line between two points, where there's a break in data logging, the legend seems to expand to describe each point's x value instead of the series name. Is it possible to avoid this? Images below. Many thanks, FazFazJan 24, 2025Occasional Reader6Views0likes1Commentget count from multiple rows in one cell by unique identifer
I have a tab "Tab1" with a column named DND that has multiple rows with the same cell data. See my pictures, for example there are three lines for "location 1". I need to count the totals in the "Count" column for that location and put the result in my second tab named "Tab2". So, on "Tab2" for "location 1" the answer should be 3.ssuuzzeeqqJan 24, 2025Occasional Reader7Views0likes1CommentAVERAGEIF On Multiple Columns, Ignoring 0 and N/A
Hello All, I am building a performance review sheet and need to take the average of multiple ratings across cells that are not in the same row or column AND exclude 0 or N/A from the average calculation. Please see the picture for reference.GasDetectJan 24, 2025Copper Contributor23Views0likes3CommentsExcel 365 Focus Cell - "Show Auto-Highlight" feature is disappeared
Hi everyone, I recently update the Excel 365 to and I found the "Show Auto-Highlight" feature is disappeared. From Microsoft website: Increase ease of navigation with Focus Cell in Excel | Microsoft Community Hub, this feature is very handy when using the find fuction. how can I get this function back? Thank you!joeyc380Jan 24, 2025Copper Contributor46Views1like3CommentsHelp on a Formula for checkbook register
I am using excel as a checkbook register. I print my own checks but sometimes hand write a check on the same account so the numbers are not in series. I have a formula to auto increment my check number column only if a reference cell is "Printed Check", and I type in the hand written check numbers. I'm looking for help modifying my formula to ignore the hand written check numbers and find the last (or largest) printed check number. The formula currently is: =IF(B11="Printed Check",(XLOOKUP(TRUE,ISNUMBER($A$1:A10),$A$1:A10,,,-1)+1),"-") All of my hand written checks will be below 15000. Link to data test sheet I linked a test sheet above but data looks like: 15100 Printed Check 15101 Printed Check 15102 Printed Check - E-mail Transfer - E-mail Transfer - Auto Pay - Direct Deposit - Auto Pay 15103 Printed Check 701 Hand Check 702 Printed Check 703 Printed Check For now I can use this formula and brute force it by typing the next printed check number after a hand written check but it would be nice to have the formula do that for me. I've tried using < 15000 logic modifiers but have not been successful yet. I'm only YouTube certified in excel but this has been a fun project so far. I do have a 30 day trial of Copilot but wasn't able to find a solution that way either. I hope somebody is bored on a lunch break or something and willing to help, thanks! 😁SolvedRimRock_RanchesJan 24, 2025Occasional Reader16Views0likes1Comment
Resources
Tags
- excel42,066 Topics
- Formulas and Functions24,389 Topics
- Macros and VBA6,335 Topics
- office 3655,910 Topics
- Excel on Mac2,610 Topics
- BI & Data Analysis2,316 Topics
- Excel for web1,869 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,604 Topics