Forum Widgets
Latest Discussions
Relative cell reference in formula is not updating when dragging down rows
I have this formula: =SUM(COUNTIF(INDIRECT({"j16","n16","r16","v16","z16","ad16","ah16","al16","ap16","at16","ax16","bb16","bf16","bj16","bn16","br16","bv16","bz16","cd16","ch16","cl16","cp16","ct16","cx16","db16","df16","dj16","dn16","dr16","dv16","dz16","ed16","eh16","el16","ep16","et16","ex16"})|"=1")) When I drag down to populate more rows with the formula, the cell references are not updating. It just copies the same formula. Have other formulas that update the cell references fine in the same sheet. I have tried adding $ in front of each cell, in between the row & cell #, removing quotes (which breaks the formula) nothing works. Any help is much appreciated.KKincaidFeb 18, 2025Copper Contributor4Views0likes0CommentsHow to return counts of text cells across multiple worksheets in same workbook?
I'm working in Excel 365. Windows environment. Working in the Excel app but will be posting to a shared drive for multi-user data entry. 38 worksheets, one for each office. What I need is to be able to return the total # of records in the data set that are marked as 'Complete', 'Pending' and 'Urgent' (from a drop-down is one cell of each record), by office and the Rep's name that made the entry (also from drop-down). I would also like to be able to return the total entries across all worksheets by Rep Name, so I can see which offices/Reps are making the most/least entries. I'm not very savvy with using Power Query. I've tried to do it, but I think it requires that there are no blank cells in the records. It keeps making tables in the query that don't actually exist. Tried it using 6 worksheets, just to test it, and the appended data set contains way more tables that I added. I'm not bad with pivot tables. If anyone has an idea of how I could accomplish this, I sure would appreciate it! Thanks in advance for any assistance. :)Marcus_BoothFeb 18, 2025Brass Contributor54Views0likes2Comments"Sorry , something went wrong"
Can anyone help me with this error? In one of the Excel online workbook, which has a lot of rows and links. since around 2 weeks ago, everytime anyone double clicks any cells with hyperlink, it will show this error message afterwards and if you press OK the workbook will reload. Thank you.ricomulyadiFeb 18, 2025Copper Contributor4Views0likes0CommentsFormula is too complex to be assigned to object
Hello, I can't run neither assign a macro to the object. I've figured it's because of the long file path. However, the files are on Sharepoint and folders need have long file paths. Is there a workaround to this? Thanks.SolvedkheldarFeb 18, 2025Iron Contributor22KViews0likes4CommentsCountifs a selected month is between date range
Hi all I may be getting confused with this one, but essentially I have the following table: I am trying to use COUNTIFS to count how many projects from the table are between the date range (start month and end month) I know how to do this the other way round, e.g. provide a date range and lookup the table. However not sure how to do it in reverse. Can anyone advise? Thanks Mattmatt0020190Feb 18, 2025Brass Contributor33Views0likes3CommentsWordle with Excel Solver
Amid the plenty of solutions and techniques that are coming for the newly viral word-puzzle game Wordle. I have also tried to make an excel-based solution for the puzzle. Because, being an excel enthusiast, I believe that Excel provides more flexibility and customization power than other analytical and problem-solving tools. The primary aim of sharing the solution with the respected members of this excellent community is to have different logic and alternative solving approaches that would surely provide some learning opportunities. The element of interest for the members is to model an approach that would produce more close and accurate guesses. For this solver, I have got inspiration from Mr. Robert Gascon, who occasionally demonstrates excellent features of traditional excel. Therefore the solver is a non-VBA / non-CSE model and does not require the 365 version of Excel. Thoughtful comments, especially from Mr. Robert Gascon Twifoo and Mr. Sergei Baklan SergeiBaklan about alternate solving approaches will be valuable. The solution of two real games along with the solution strategy is also attached for reference purposes. Thanks TauqeertauqeeracmaFeb 17, 2025Steel Contributor8.8KViews4likes11CommentsLambda for Getting Sums Based on Dynamic Filters
A while back, someone helped me by drafting a Lambda formula that counted the number of rows in a dynamic table based on multiple dynamic filter values. I now have a need to modify it so it calculates the sums of a column in a table based on the same premise. This is the lambda formula for if it were to be applied to the same spreadsheet: =LAMBDA(tblall,BeginDate,EndDate,Dept,PRAM,LET(tbl,DROP(tblall,1),hdrs,TAKE(tblall,1),nofltr,SEQUENCE(ROWS(tbl)),result,FILTER(nofltr,(INDEX(tbl,,MATCH("Date",hdrs,0))>=Begin)*(INDEX(tbl,,MATCH("Date",hdrs,0))<=EndDate)*(IF(Dept="",nofltr,INDEX(tbl,,MATCH("Staff & Agencies Involved",hdrs,0))=Dept))*(IF(PRAM="",nofltr,INDEX(tbl,,MATCH("PRAM #",hdrs,0))=PRAM)),"None Found"),COUNT(result))) My initial thought is that the change would take place in part where it defines "nofltr" as the number of rows [.....nofltr,SEQUENCE(ROWS(tbl)),.....] and where it filters the defined "result" [...COUNT(result)....]. I can't quite figure out what needs to be modified. I've used some very convoluted IF functions in the past to do the same thing, but this has proven to be a lot neater and more scalable for large numbers of dynamic filters.lovea70Feb 17, 2025Copper Contributor62Views0likes7CommentsDrop down lists from separate workbook
I'm trying to create a drop down list where the source data is in a separate workbook but can't seem to find anything in data validation that allows me to use a different workbook as a source. Any ideas how to do this? ThanksCJ_C42Feb 17, 2025Copper Contributor68KViews0likes8CommentsConcatenate or vlookup or something else?
Hello! My first post and I'm not even really sure what formula I am looking for, but I'll try to explain what I need... Along the top of my spreadsheet are the dates of the month. The first column is a list of driver violations. I'm using TRANSPOSE and XLOOKUP to grab the dates of each violation, however, some violations happen on several consecutive days. Instead of lising several consecutive dates in a column, is there a way to return the date of the first violation in a column and the last consecutive date in another column, or return for example October 1 - October 5 in one column? Thank you for any help you can offer! Dawn-Mariedmgiles0528Feb 17, 2025Occasional Reader4Views0likes0CommentsHiding Column and Row Headings for Excel Mobile App
I want to hide the column headers and rows, so that when I focus in using the mobile phone version of excel, it avoids them being enlarged and thus saving screen space, by only focusing in on the cells I want enlarged - can this be done ?Fat8oySl1mFeb 17, 2025Copper Contributor2Views0likes0Comments
Resources
Tags
- excel42,218 Topics
- Formulas and Functions24,472 Topics
- Macros and VBA6,357 Topics
- office 3655,942 Topics
- Excel on Mac2,620 Topics
- BI & Data Analysis2,336 Topics
- Excel for web1,882 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics