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 Contributor18Views0likes2CommentsLambda 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 18, 2025Copper Contributor72Views0likes8CommentsAbility to reference cell while looking up a value
I am completely stumped on how I can go about my situation... My spreadsheet requires a way that I can lookup the value in a cell, which is a formula containing text and math, however that cell is additionally looking up a value from another cell. The issue I have is that the values being referenced in the lookup aren't unique, and are across multiple ranges. Essentially I need to be able to reference the cell which is looking up the initial value and offset the returned referenced cell. To best explain how it work... I want to enter a number into a cell, Sheet1!A1 Sheet1!A1 = 40 I want a lookup function in Sheet1!C1 that returns the value in Sheet2!B1 Sheet1!C1 = INDEX(Table2[@COLUMN2],MATCH(Table1[@COLUMN1],Table2[@COLUMN1],0)) I want the value from Sheet1!A1 to be in the formula on the cell at Sheet2!B1, however it must be referenced using an offset Sheet2!B1 = "SOME TEXT"&Sheet1!A1*Sheet2!C1 = "SOME TEXT"&INDIRECT(ADDRESS(ROW(),COLUMN()-2))*Sheet2!C1 The trickiest part of this, is that the ADDRESS function returns a cell reference as a string. As I want Sheet2!B1 to lookup the value of Sheet1!A1, I must enter the formula of Sheet2!B1 as text and evaluate the formula in Sheet1!C1, otherwise ADDRESS(ROW(),COLUMN()) will return the value of Sheet2!A1 and not Sheet1!A1. As mentioned above, the first value, 40→Sheet1!A1 is a range of values in multiple tables and so is the returned cell of Sheet1!C1. As I need to reevaluate the cell of Sheet1!C1, the best method I can find for this is using an Excel macro of EVALUATE in the name manager, which is reevaluated in Sheet1!D1 Sheet1!D1 = EVALUATE(GET.CELL(5,INDIRECT("RC[-1]",FALSE))) = EVALUATE(Sheet1!C1) This method works as I need, the final issue that I have is because I'm using INDIRECT in the name manager formula, I am unable to use INDIRECT again in Sheet2!B1, I can't work out how to reapply it to the formula, I end up with a string of the cell's address instead of the cell's value (A1 & RC[-2] should be 40, not the address) Sheet1!C1 = "SOME TEXT"&ADDRESS(ROW(),COLUMN()-2,4) Sheet1!D1 = SOME TEXT A1 To put it into some actual data, that can imported into excel and is a direct reference to what I want achieved. I'm 100% open to alternatives, suggestions or methods of how to get my spreadsheet to work this way. The below results in #VALUE, as example it's trying to multiply John*1.1 and not 160*1.1. If I use it as a text formula and wrap the ADDRESS in INDIRECT, I get #REF as it's result, however if I omit the INDIRECT it will result in C2*1.1 and again, not 160*1.1 as the address is a string not a cell referenceAshley_JamesFeb 18, 2025Copper Contributor27Views0likes2CommentsFormula calcular años desde 1800
Hola a todos...trabajo en un lugar donde tenemos datos de pernas que pertenecieron a este grupo desde del 1800...cuando uso la formula para calcular la edad con dos fechas (la de nacimiento, y la de muerte) me da eeror en aquellas fechas que son antes del 1900...pero las que son del signo 20 no hay problema que puedo hacer, hay alg{un modo de aumentar el rando de la fechaMaria12Feb 18, 2025Occasional Reader21Views0likes1CommentExcel will not let me click into cells or incorrect cell is selected randomly
Hi, This is an ongoing issue that I've been having for years across platforms, computers, versions of excel and workbooks. Basicially, one of two things happen: 1-I click on a cell but nothing happens, the cell is not selected 2-I click on a cell but the wrong cell is selected (Example: Click on cell B2 but excel will select C2) This will happen as frequently as a few times a day (across multiple workbooks) to every month (again, across multiple workbooks) In ALL cases cells are NOT protected Saving the workbook, closing then restarting excel fixes the problem. As I mentioned before, this has been ongoing for years now across multiple computers, workbooks, operating systems. All systems and versions of excel were fully up to date at the time of the error. Because this is happening over several different workbooks, I do not belive it is a workbook issue (especially when it doesn't happen 100% of the time to ANY workbook) Systems used: Macbook Pro (circa 2015), OS up to date - No longer have this computer Windows 8 (again, old computer) Windows 10 (old computer Windows 11 (2 different computers, currently owned) I have always kept excel and the OS fully up to date and I'm currently using the most recent version of Windows and Excel. In all cases, I have used built in trackpad and a 3 different wireless mice (with full batteries). Still randomly occurs. It's really just an annoyance and like I mentioned before, saving the workbook and restarting excel solves the issue... until next time (which could be later that day or a few weeks...It's very random.)CPeterson710Feb 18, 2025Copper Contributor197KViews5likes103CommentsTaskbar Covering Tabs
Good afternoon, I'm working on an excel sheet but the taskbar is blocking the view of the tabs for some reason. I've tried file > options > advanced > display > 'show sheet tabs' and the box is already ticked. Does anyone know what might be causing this? Thanks.Callumc123Feb 18, 2025Copper Contributor12Views0likes1CommentCountifs 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 Contributor57Views0likes4CommentsConcatenate 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 18, 2025Occasional Reader22Views0likes1Comment
Resources
Tags
- excel42,220 Topics
- Formulas and Functions24,473 Topics
- Macros and VBA6,358 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