Forum Widgets
Latest Discussions
XLOOKUP 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. Thanks
Excel Documents printing an extra border around the outside of all of my documents
I have been printing and previewing my excel documents recently. I am not sure when this issue started. Any time I preview and/or print an Excel spreadsheet this extra border shows up AROUND the entire document. It is not the cells within the document. Also, it shrinks my entire document to fit it within this border. I have done all the things the website has recommended: turning off "print gridlines", gridlines are off when in print preview, checking margins, turning on/off borderless printing, set the correct print area, etc. Keep in mind, this has nothing to do with anything I have done within the spreadsheet. It is outside of the spreadsheet. It is almost like I took a picture of the spreadsheet, saved it, and now a border image shows up. I have gone back and looked at all of my OLD documents that I have saved for years and those documents, like Invoices, now have this extra border around them that were never there before. I have even changed the printers in the pull down box. I tried changing it to my old printer which I never uninstalled the software and I get the same thing regardless. Does anyone have any ideas?
Circular 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 =(ASAP utilities in power automate cloud flow
I have one server machine that installed ASAP Utilities for Excel. I want to eliminate individual server logins and allow users to run only 5 specific ASAP Excel plugin functions automatically, just by dropping an Excel file into a common server folder, so the backend processes it and returns the result—saving time. Will Office 365 excel with power automate cloud flow help? Thanks for you answers for my clarifications.
Resizing Excel Window
I cannot adjust the size of my Excel window. It is either full screen or the tiny rectangle with 3 boxes. I am able to change the screen size in Word just fine. But Excel will not change to the double arrow when I move the cursor to the edge of the window, nor will it collapse to half-size when I use the buttons on the top right of the window. I have logged in/out of my Office account, restarted multiple times, etc. Any advice, please? Thanks!
Need 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 AlStock 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?
