Forum Widgets
Latest Discussions
Rows being deleted unintentionally
Excel 2021: I have had severl instances when opening a particular workbook, or similar copies, of rows being deleted and the rest in a specific group of rows, being run together. I have 12 worksheets with months names on the workbook and this is happening on 11 of the sheets. I have to copy the good sheet, then select the other 11 and paste. It has been happehning many times this week.
Creating Formulas but not responding as expected.. Due to format of cell?
I have a large moderately complex workbook for forecasting / projecting a financial situation for next 30 years. It references data on multiple sheets. I'm using Accounting format, but I have removed / shifted the decimal places so it shows Custom for the format. It has been working fine, but yesterday when I attempted some changes, all of a sudden when I create a formula, even something as simple as =H36 or as simple IF statement, the cell remains empty or shows 0! I just tried something in some unused cells, where I did a simple =E31, which contains the value 12%. Three of the four cells show 12% the other $ 0. The three that show 12% were probably General to start, but now show percentage. The one that didn't show 12%, was Custom, from me using it previously to test thigs. I then formatted a blank cell Accounting and shifted decimals (sand it reacts the same way, and show $ 0, not 12%. What is going on? LOL E31 is manually entered 12% I entered =E31 in each of these cells below. (It won't let me use a table here...) They started as General. The one that doesn't show 12% was preformatted Accounting but with decimals shifted, so it shows Custom. The last one I formatted as Accounting without removing / shifting the decimals and it pulled in the 12%... I'm lost. What's gone whacky with the Custom format after shifting the decimals? It has been working fine. Thanks 12% 12% $ 0 12% 12% 12%
Look Up FX Rate by Transaction date
Hello Folks, Attached you will find a sample file where I am trying to do the following: https://docs.google.com/spreadsheets/d/1NeCSt8oAcEM86DAxI-8LkqTQK4D6WQvv/edit?usp=sharing&ouid=103354753371375324640&rtpof=true&sd=true Under the Data tab, if B2="C", then C2/xlookup(A2/FX Rate Oct 2025), C2) basically, if the Currency Code is C for Canadian then divide the Canadian $ on that transaction date by the reported FX Rate for that month that is stored in the FX rate tab. Otherwise, just return the contents of that cell. I tried to do a xlookup by the transaction date but since the data file is converted from a flat file template, I think my lookup function is not recognizing the lookup field between the array and the cell reference. I am more than happy to create a helper column for the Transaction date that will match the format of the month in the FX rate tab. Hope you can point out a clean solution for this approach. Thank you. Regards, Shams.Look Up FX Rate by Transaction date
Hello Folks, Attached you will find a link for a sample file where I am trying to do the following: https://docs.google.com/spreadsheets/d/1NeCSt8oAcEM86DAxI-8LkqTQK4D6WQvv/edit?usp=sharing&ouid=103354753371375324640&rtpof=true&sd=true Under the Data tab, if B2="C", then C2/xlookup(A2/FX Rate Oct 2025), C2) basically, if the Currency Code is C for Canadian then divide the Canadian $ on that transaction date by the reported FX Rate for that month that is stored in the FX rate tab. Otherwise, just return the contents of that cell. I tried to do a xlookup by the transaction date but since the data file is converted from a flat file template, I think my lookup function is not recognizing the lookup field between the array and the cell reference. I am more than happy to create a helper column for the Transaction date that will match the format of the month in the FX rate tab. Hope you can point out a clean solution for this approach. Thank you. Regards, Shams.I broke my spreadsheet dropdowns
Somehow, the dropdowns (data validation list) stopped working within my entire workbook. It is the "Weekly Schedule Planner" template that Excel offers. I added 2 additional picks to the list. Then I updated the conditional formatting to assign them specific colors. Somewhere in this process or perhaps accessing this shared file through M365 Copilot on my phone, the dropdown arrow on the cells is gone! The options are still there when using the formula box at the top, albeit only when I backspace first. I have tried everything I can think of to get the dropdown arrow to show, and it doesn't. The first column in the To do items as well as each day is set up with the list. I used Ctrl-G to make sure there wasn't an extra cell hiding somewhere, too. We will probably start fresh with a new file in 2026, so it's not the end of the world if I have to struggle through the next six weeks, but it might be. TIA for any advice that might fix this.
Power Query - Large Data Set Question
Hello - I combined a handful of .xlsx files and there's about 5 million rows total. I'm trying to identify duplicate "project IDs" (the long strings below in rows 22-29), basically what this filter is showing is all of the project IDs that have 3, 4, 5, 6, etc. duplicates all the way through 14. That's exactly what I'm looking for, there's about 180k project IDs I was able to get. The problem is the project IDs that have 1 duplicate, which is the number 2 in this filter because I did a group by in power query by the project ID and it counts the number of matching rows it has, so 2 means it has 1 duplicate. When I filter on this it runs over the 1 million excel row limit, I was wondering if anybody had an idea of how I could get around this problem?
show a due date using data from multiple columns
I have a spreadsheet in excel, such as the Table 1 below. For the 5 Due Date columns, I have conditional formatting set up to format the cells. See Image 1 for a list of rules applied to all Due Date columns. I'd like for another table to populate, showing only columns, Laboratory ID, Description, and any column that is highlighted per the conditional formatting rules. For example, I'd like the return table to look something like Table 2. Table 1. populated in cells G9:N12 ID Description Due Date 1 Due Date 2 Due Date 3 Due Date 4 Due Date 5 Out of Service? 1234 Instrument 1 11/17/2025 N/A 1/31/2027 3/11/2026 11/17/2027 FALSE 1235 Instrument 2 1/14/2026 2/17/2026 3/31/2027 1/23/2026 12/29/2027 TRUE Table 2. Conditional formatting rules applied to cells I9:M12 This Week = White font color, Red fill This Month = Red font color, Light Red fill Next Month = Dark Yellow font color, yellow fill Table 2. If today is 11/10/2025, the table will display columns ID, Description, and only column show for due dates within This Week: ID Description Due Date 1 1234 Instrument 1 11/17/2025Excel interprets dates 12 and lowers as months
Hi If I in any cell, without any adjustment of cell formatting, excel will interpret 15/8 as 15-aug, as it should but 4/8 is interpreted as 8-apr... Is there any way to get excel to interpret this consistently correct, that is dd/mm = dd-mmm and not "IF dd=≤12 then dd is mmm and mm=dd" which is insane?
