Forum Widgets
Latest Discussions
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!Formula help
I have the following formula that partially works. It processes this formula and returns the 1st match based on the formula criteria. =IFERROR( LET( KeyTypes,FILTER('Key Log'!$F$4:$F$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), KeyNums,FILTER('Key Log'!$K$4:$K$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), IF(INDEX(KeyTypes,ROW(A1))="Hard Key","V"&INDEX(KeyNums,ROW(A1)),INDEX(KeyNums,ROW(A1))) ),"") This formula resides in cells A8:A18 which is designed to return a key number, whether the key be a swipe key or a hard key. Cells B8:B18 are for descriptions of the key identified in cells A8:A18. This formula is supposed to find the 1st match on the key log and then find any other matches and place them in cells A8:A18. There is another formula for defining the description based on criteria. I need this formula to search out all transactions on the key log that match the ID number on the Key Issue Form in cell D6 with the cells in column A on the ID number and to also match the room number from K15 on the Key Issue Form with the room number in column C on the key log. There is something missing from this formula that is not allowing the other transactions to be found and listed on the Key Issue Form. Can anyone help me complete this formula? This is the formula for B8:B18 =IF(A8="","", LET( FilteredRows,FILTER(SEQUENCE(ROWS('Key Log'!$A$4:$A$309)),('Key Log'!$A$4:$A$309=$D$6)*('Key Log'!$C$4:$C$309=$K$15)), RowNum,INDEX(FilteredRows,ROW(A1)), Room,INDEX('Key Log'!$C$4:$C$309,RowNum), Status,INDEX('Key Log'!$E$4:$E$309,RowNum), Type,INDEX('Key Log'!$F$4:$F$309,RowNum), DateVal,INDEX('Key Log'!$D$4:$D$309,RowNum), IsAB,OR(RIGHT(Room,1)="A",RIGHT(Room,1)="B"), DoorDesc,IF(IsAB,"Combined Main & Room Door","Main Door"), KeyDesc,IF(Type="Swipe Key","Swipe Key","Hard Key"), DoorDesc & " " Can someone help me figure this out? Carl
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.
