How does Excel calculate named formulas and materialized named ranges?
I have been creating utility formulas with AFE to make my lambdas more readable and maintainable, for example: ManualAllocRng =OFFSET(ManualAllocCorner,1,0,URows,EntityCount); How does Excel calculate these results? Are they calculated every time I use them, or is there an internal type of materialization that happens once and then is re-referenced on each usage? In particular, I am wondering: (1) If I have a lambda that references my ManualAllocRng twice, does it matter if I write it as thing = LAMBDA(x,LET( rng, ManualAllocRng, a, something(rng), b, otherthing(rng), res, combine(a,b,x), res )); or as thing = LAMBDA(x,LET( a, something(ManualAllocRng), b, otherthing(ManualAllocRng), res, combine(a,b,x), res )); Does version 2 calculate the result twice? Does version 1? (2) Should I instead be materializing the value in a Calcs sheet, naming the corner ManualAllocResMaterialized, and using ManualAllocResMaterialized# throughout the workbook instead of referencing ManualAllocRes? Does every reference to the named formula calculate its result again? Does every reference to a spill recalculate the result, or does it "look" at the spilled range that was already calculated? On a related note, I have found a circumstance where sheet-based spilled result =Ledger.FilterMatch produces a different result from VBA Dim B as Variant B = Evaluate("=Ledger.FilterMatch") This seems like a serious bug. So I am considering materializing all my AFE-based named formulas onto a Calcs sheet just to avoid this bug, even if there is no performance issue to consider in the questions I asked earlier.If, Then formula from multiple columns
Hi there! I'm doing a mail merge for a scholarship award letter. The data report that we're running doesn't specify where the funds will be deposited in words. it only has a header of locations a, b, c, etc. and underneath it says "yes" or is left blank. Right now I have this on a sheet labeled "data" The merge is pulling from sheet 2 labeled "merge" in the award letter we need to insert "your award will be applied to <<location>>" so what i've done so far is made additional columns with this formula: =IF(BG3="Yes","LOCATION A",0) where I'm stuck is: how do I take multiple columns of information (one column containing a word and the rest 0) and tell a cell on another sheet to pull the word out and leave the zeros. If there's a better way to take the initial data and narrow down this information into Sheet 2 "merge"?
Vlookup using short and full name with case insensitive
Hi Experts, I have a sheet named Source, with these columns: Column A: ID Column B: Full Name Column C: Department (data starts from Row 3.) I have a sheet named Sheet2, with these columns: Column A: dept with short name Column B: Full Name Column C: ID Column D: Department I would like to populate the data [full name, ID and department] in sheet Source into sheet2 column B, C and D respectively by matching the short name with the full name. Upper/lower case mismatch and name not match exactly, only can match 2 words. What formula should i use to insert into Sheet2, column B so that I have the information to be automatically filled up in column B, C and D ?
How to count duplicates ... sort of ...
Greetings! Here is part of a table that I am working on: RepairID ModelID Serial Number Date Started Date Tested ReportedSymptom SPEA-1010 PT206 8FA552 10/22/2025 10/28/2025 No Boot - White Screen SPEA-1024 PT206 8FA552 10/22/2025 11/4/2025 No Boot - White Screen SPEA-1037 PT206 7C99BD 10/22/2025 11/10/2025 EGM No Comms SPEA-1038 PT206 715473 8/21/2025 EGM No Comms SPEA-1039 PT206 715473 11/10/2025 11/11/2025 Failed Battery Test SPEA-1056 PT206 7142AE 10/20/2025 11/12/2025 Black Screen SPEA-1057 PT206 71584F 10/20/2025 11/12/2025 Black Screen SPEA-1144 PT206 7142AE 11/24/2025 11/24/2025 No Boot - Black Screen I am trying to count the number of repairs based on serial number in this table. But there are some duplicates. However, if you look at the rows with red font, you will see that the serial numbers, though they are the same, were not being repaired for the same reason. So, these need to be counted as 2 separate repairs. If the repairs to the same serial number are for the SAME symptom, we only count it once. What would be a good approach to creating a function that will count this into a cell on a separate table? Any assistance would be greatly appreciated.
How to sum chain values from N-number cells?
Hello, I have data of people by age, but 100 different values on a line chart would look... bad. Hence the need to group ages in sets of 5 or 10 on a separe line in chain, but what is the function to do this? It certainly isn't the humble SUM function, since it moves the range by one, producing "1 the problem". Sure, I could do the SUM chain and delete four cells between keepers, yielding "2 the problem", but that's just awful. Or I could do "WHAT I SEEK" manually, if I had an enernity... Thanks in advance, and have a wonderful day!
i need the data from all the sheets in the workbook to link to one data sheet.
A colleague of mine made a data spreadsheet (lets call it SHEET 1), he no longer works for the company and over time the worksheet has been amended, so doesn't work like it should. There are several sheets in the workbook and all data that is copied into these needs to go into SHEET 1 (doing it itself, not manually). My question is when I download my data and paste into SHEET 2, how do I get it to automatically go on to the SHEET 1, taking only certain parts of the data, in this case B and F. The attached is just a small example of the data I need from SHEET 2. On SHEET 1 There is a list of numbers on the left and more, how can I get this data to filter itself onto SHEET 1 in the right column then adding the numbers on the left together. So, on SHEET 1, 160 -T will show 7 because there is 7 1's below for that Org. Sorry if i have made this sound long winded, I am awful at explaining. I have basic knowledge of excel but I cannot get my head around formulas. It should look something like this. Any advice would be great. Thanks
Practical use of row_delimiter with TEXTSPLIT
Let me be clear, I am not asking HOW to use the row_delimiter part of the TEXTSPLIT argument, I am asking WHY. Any use case I have come up with so far would be better addressed by Power Query. Even if there is a reasonable example, it still seems like it would have to be looped using VBA. Is material out there on what the developers had in mind for using it? If anyone knows, please share or share any example you have. (I'm in pretty much the same place with the ignore_empty and pad_with parts of the argument as well.) Thanks!Formula not staying
Hello community! I am having trouble with my worksheet. It is not keeping my formula for adding number from different worksheets. The formula that I am trying is: ='1'!C12+'2'!C12+'3'!C12+'4'!C12+'5'!C12+'6'!C12+'7'!C12+'8'!C12. When I save, close and reopen the file, the formula disappears and the resulting number is kept. Please advise on trouble shooting with this concern. Thanks!Excel Security Warning:
Hello Everyone, I am having one strange issue. I am working on a excel which has few Power Queries and around 10 sheets. I have not created any macros. But in VBA Editor there are two VBA Projects. But they do not have any macros in it. Now the issue is, every time I open this excel file, I have been greeted with the "Security Warning: External Data connections have been disabled". and I need to press the "Enable Content" button every time. I have tried saving the file as .xlxs (from .xlam) but it didn't solve the issue completely, it does not throw error in some machines still and I have also tried to remove these two projects which has but no luck. Can someone let me know if this is a known issue? What are possible ways I can remove this warning (only for this file) as technically I am not using any macros. Any help would be highly appreciated. Let me know if you need more details. Thanking you.
