Forum Widgets
Latest Discussions
Shortcut to jump to formula bar in Excel 365
I would like to know if there is a keyboard shortcut to jump to the formula bar in MS Excel 365. The shortcut used to be F2 + Ctrl A in previous versions of Excel, but it is no longer working in Excel 365. I know that I could turn off "Allow editing directly in cells", but that is not what I'm looking for. I want to edit formulas directly in the cell, but be able to switch to the formula bar if needed. Would really appreciate it if someone could help me out with this one. Thanks!
How can I display negative values for time in calculation results
I'm trying to create an Excel spreadsheet with a column that shows the time relative to a predetermined point in time in 15-minute intervals with negative values for time in the cells above 0:00 and positive values below. I have the correct formulas entered in, but I can't seem to figure out how to get Excel to display negative values for time. Is there a way to do this?
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 ?Excel sharing, New Outlook and Outlook 2016
Hello, when I try to share an Excel file (File => Share => Send as attachment), Excel opens Outlook 2016 (Office 365). But I only use New Outlook (the native Windows application). I've changed the default application, but it doesn't make any difference. Do you know how to force Excel to share files through New Outlook? I'm French and I used a translator. I hope you understand.Excel bug Data sorting largest to smallest
I have some problems by sorting data in excel. First sorting by column M Largest to Smallest, than by column L also Largest to Smallest. In one case sorting is not successfully (Marked font color RED). Can someone explain this to me? https://drive.google.com/file/d/1n_IWsw9YH6FDxXnFm_wWwJEVQIN9AxRu/view?usp=sharing
