formulas and functions
25363 TopicsLine chart dynamic scaling of Y-axis
Micro$oft, please, please, pretty please with sugar on top, END the ensh*ttification, and get the Y-axis of a line chart to dyanmically scale with the data range that is being plotted. The range defaults to 0 as the lowest Y-value. The only way to change it, is to manually set the ax range, or use a work-around involving VBA. So... NO! This is crazy! It should be easy as apple pie for you lot to add an option to dynamically scale the Y-range in the chart, but I guess you are far too busy pushing people onto your crappy AI, which you can really stick some place the sun doesn't shine. Thank you.3Views0likes0CommentsCircular Reference Issues - A desperate woman needs help ASAP =(
I have an incredibly in-depth spreadsheet, with about 15 pages, and in-depth tables and calculations on most of those pages. Point being, I have a HUGE freaking file with tons of data collected over the years. Now, all of a sudden, I cannot input anything without the Microsoft Excel Warning popping up, stating: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells." I understand what this pop up is saying, but my file is so huge, and there is so much information, I cannot find the error anywhere!!!!! And I don't have the option to search for it. So I cannot correct this error on my own, and I cannot input anything into the spreadsheet without having to exit out of that **bleep** pop up!!! I am defeated, INCREDIBLY FRUSTRATED, and I cannot see a clear path to recovery. I have been working on this document for years now, and I cannot start fresh. HELP, HELP, HELP!!!!!!!!!!!!! SOS!! Anyone! I am desperate and beyond frustrated =(17Views0likes1CommentXLOOKUP using 3 unique criteria
I have almost completed my pet project, but I have one final piece that I cannot figure out. First off, I work in insurance where I audit incoming (uploaded) spreadsheets from insurance reps. The auditing I do mainly is to be sure that all the data in the uploaded spreadsheet imports into our db accurately and completely. These uploaded spreadsheets list any new, cancelled or adjusted policy information. The policy could be one carrier or it could be multiple carriers. I built a dashboard (sorta) that sorts the rows of data in a way that makes it a TON easier to audit. My main focus here is to list out all the carriers of a policy and to show their cost related columns for each carrier's part. I am using a FILTER formula to list the carriers based off a policy # that is a Data Validation list in cell (Dashboard!A4). The carriers are then listed in cells (Dashboard!AN4 - AN25). Then, in columns (Dashboard!AO - AS) are the cost related columns. The Data Validation list, Carriers and cost related columns are all pulled from the Transactions worksheet. So far, this is working MOSTLY as intended, but because some policies use the same carriers, the cost related columns are not filtering by policy and will then therefor show the first available entry for each carrier (kinda like what VLOOKUP does). I am using the following formulas so far. List of carriers: =FILTER(Transactions!H:H, Transactions!A:A=A4, "No matches found"). This works as intended. Cost related columns: =XLOOKUP(AN5,Transactions!$H$3:$H$1000,XLOOKUP(Transactions!$O$2,Transactions!$A$2:$BA$2,Transactions!$A$3:$BA$1000)). This too works as (mostly) intended, but as you can see, I need an additional filter to sort by policy #. This should be the last step to finishing this pet project. I would appreciate it if anyone can help me add the additional formula criteria that would now filter out by specific policy. I tried adding another XLOOKUP to the formula to add the additional criteria based on policy # with no luck. For additional reference, the Transaction worksheet will list the same policy for each row that contains a unique carrier. So if a policy has 5 unique carriers, there will be 5 rows to list each unique carrier and their cost related amounts associated with each carrier (row). What I need is to add the additional filter based off the policy # to be sure that the cost related columns are specific to the policy # and individual carriers? Any help would be GREATLY appreciated. Thanks73Views0likes4CommentsNeed quick help counting values from a list for presentation.
Hi All I could usually work this out by searching google and the forum if I had time but its run out so looking for some quick community assistance to get me through the day. I need to count how many times we went to specific locations that are "In area". there is a list of 47 locations and 13 of them are considered "In area" I have a list of jobs in sperate sheets named based on years (2024, 2025, 2026) in those sheets I have a column that lists the location the job was in, which is pulled from a list of locations as a list in the sheet "DataLists". Each year there are approximately 250 jobs and of that 150 are probably in area. In the DataList sheet where the list of locations are pulled from, next to each location listed in column E I have a 1 or 0, in column D, 1 for "in area" and 0 for "out of area". I also have a "Report" sheet that generates a report based on the selected year as a drop down in cell C2 I want to count how many times we went to jobs the were "in area". I tried a quick solution to get me through today and generated a new list in column Q in the DataList sheet with: =IF(D2=1,E2,"") this made a list of only "in area" locations in column Q. Then in one of the year sheets I tried the following formula =SUMPRODUCT(COUNTIF(H3:H200, DataLists!Q2:Q47)) where H3:H200 is the locations of the jobs and DataList!Q2:Q7 is the list of "in Area" and got 3335 which is very wrong as there are only 97 jobs so far in 2026 and maybe 50-60 of those are "In Area" I was going to then expand it to the following in the "Report" sheet. =SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2&"'!h:h"),DataLists!Q2:Q50)) so it pulls the data from the selected year entered into cell c2 What am I doing wrong here, am I using the wrong function. Sorry only have a few hours before presentation. Ideally I would like the formula in the "Report" sheet, to sum the total number of occurrences from the "year" sheet column H:H for any match from "Datalist" sheet column E:E but only if D:D = 1 I'll take any quick fix at this stage. Cheers Al46Views0likes1CommentStock Price Function
I've used the "Stocks" Data Feature in the past. This is missing from Excel on my new Surface. It still works on an old desktop I have but not on my new Surface. I would expect more features, not less! If I need a different version of Excel, how do I manage that from O365? Does anyone know how to fix this?2.1KViews0likes6CommentsPython integrado con excel
Tengo una suscripción de Microsoft 365 Empresa Estándar, ya estoy dentro del grupo de Microsoft Insider 365, tengo habilitado el Canal Beta pero aún así no me esta funcionando Python integrado con excel ya que escribo el código pero no me muestra el resultado, en su lugar me muestra el mensaje "BLOQUEADO" indicando que no tengo la licencia requerida. He hecho de todo lo que me ha salido de consejos en la web, incluso cerré sesión y volví a ingresar pero el resultado es el mismo:9Views0likes0CommentsAdding measures in a data model at a summary level - how ?
I'm probably not going to describe this very well - but here goes 🙂 I am new to data modeling and am trying to convert old files / pivot tables to new table design/data model structures and at the same time develop some new reports for key stakeholders. The attached file has a table (I know its not optimal in terms of structure - but way better than it was). This table (called Hours_New) collects employee time daily. The time is regular, ot or unpaid. It is also chargeable or non-chargeable (as determined by the column in the table titled SC & CD Combo. There are productivity targets (50% for labourers, 75% for other trades). What I want to be able to do is report the productivity % at various levels (Foreman, Trade, Time Period etc etc etc). When I build pivot tables to try and do this I'm not able to get the results presented as I want them - I've taken a couple of stabs at it, but the results are way too busy & I want to be able to highlight when targets are being missed (on both upper and lower ends). I'm sure its not all that tough to do but I'm spinning my wheels - grrr. The attached file has multiple tabs and I've trimmed the dataset down - its actually close to 500,000 rows and removed most of the other pivot tables not related to what I'm trying to do - any help would be most appreciated !!780Views0likes2CommentsData validation dropdown list isn't working
Just recently, the data validation I have set up for my worksheets no longer shows a dropdown list or arrow in the cell for my users. You can start typing something in, and it will give options close to what you've typed in if it's on the list but it no longer gives a dropdown list to choose from. I do have the appropriate boxes checked to allow for that and it's still not working. It's incredibly frustrating to find more and more things no longer work properly since the most recent update. Has anyone else had an issue with this?Solved125KViews0likes9Comments