Forum Widgets
Latest Discussions
Search 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, MarcusSequence Formula for Deadline Calculations
Good afternoon. I have been tasked with calculating the deadline dates for start dates on rolling courses. I am currently working to develop the best system. Currently, multi people have been manually type in/copy/paste bulk data. The deadline spreadsheet for example has 15 different tabs on it. There are a few quirks here. For example, our December start date is always longer, due to the holiday/break schedule. I have included a brief data set, including the sequence formula I used to calculate the deadline. Some of the program courses last 3 weeks, some 6 weeks, others can be 9 - 11 weeks. I am intending to try the sequence formula in excel to see if I can develop a formula for this process to be more automated and easily calculate, without error. I am looking for input/thoughts/reference to understand the sequence function to aide with my query or any other formula suggestions. Is there a better way to semi-automate this process? Where I can complete "shells' or "templates" and pull (either from an excel formula or simple copy/paste). I am thinking since there are 15 tabs, I can set it up to pull from other tabs. There is a blank line in between the different class starts intentionally. This is formatted specifically to read easier. This is a rolling list, so new start dates will be added at the bottom, and then we need to calculate and publish the deadlines as the new start dates are added at the bottom. So on the Full-Time data set, you can see where new Start Dates have been added. When new start dates are added, I need to find an easy way to go in and add the deadlines. The deadline is based on the last day of the calls. So English 1 had its last week on December 30, 2024. This means January 3, 2025 is the last day of that class. The deadline will always be the last day of class, unless the issue occurs during the last week of class. Then, students will get an extra 7 days. The last week will always have a different deadline. Typically, we publish the class start dates for 1 - 3 years in advance. As this gets updated, the deadline database must also get updated to add the new class start dates and then determine and publish the deadlines associated. I appreciate any thoughts/suggestions when it comes to formulas or different patterns that may develop better formulas than I have. I hope the information I provided was helpful and clear. I tried to provide informational knowledge as needed. Please let me know if there is anything I can clarify to assist better. Thank you all in advance: Sample Data Set with Class Start Week Of Date, Class & Deadline & Formula used to calculate. Warm regards, Kristina
How can I count unique clients in a each year
Hello all. I am trying to obtain the number of clients that shoped in 2018, 2019, 2020, 2021 and so on. I tryed to use Count(Unique) but it gives me the total number of clients that have shoped in our history. When I try to add a formula that restricts the formula to each year, i obtain an error or a wrong number. Each customer has a customer ID. So I have two columns in column A is the clients ID and in column B the year he shopped in. Thank you in advance.
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.
Multiple replace vba
There is a quick way via a macro or function to replace many misspelled words. I have attached an example file. In column A there is the database with the correct entries, in B the incorrect entries that refer to C separated by ";" and in C the entries that should then be replaced. If an entry is not present in the database like Pink in my file, once replaced it will remain in column B I will add it to the database later but this is a very rare evntEmail to be sent when a content is entered in a cell in Excel
Is it possible to set up an automated email notification when a specific cell in an Excel spreadsheet is updated? I'm responsible for tracking reimbursements for expense reports for a number of employees, some will have reimbursements for out-of-pocket expenses. Can I set up an automated email notification that will tell me when they enter data in "a cell" and also tell me in the body of the email. The name of the person on the report (this info is in a particular cell in the report). The date of the report (this info is in a particular cell in the report). Note: Each employee completes their expense report and will have their own separate Excel Expense Report. If I can do this then I will not have to look through every report to find the few with Out-Of-Pocket expenses to be reimbursed.
Selecting MultipleCells on Microsoft Excel using an External Touch Screen Monitor
I recently attached an external touch screen monitor to my laptop and initially I was struggling to select and highlight multiple cells on Excel Randomly the cells started appearing with a circle on each edge making it easier to highlight more cells up, down, left and right Now again randomly its changed again without me changing any settings at all and I can no longer highlight multiple cells using the touch screen monitor The only workaround is to use the select objects function but this does not always work nicely to highlight the cells Any ideas as to how to get the circles back again on a selected cell to make it easier to highlight multiple cells? This is how it looks now but havent got a picture of how it used to be when it worked well
If formula problem
Hi all! I am new to using excel so I appreciate your help in advance :) I created a spreadsheet to track multiple choice answers for a quiz. This is the formula to track if people got the answer right: =IF(F3="a",1,IF(F3="b",0,IF(F3="c",0,IF(F3="d",0,IF(F3="-",0,NA))))) However, someone answered "bd" instead of only selecting one and the formula resulted in an error. How do I correct the formula so that the answer would be "0" in that case? Thank you?
