Forum Widgets
Latest Discussions
Stringing together formula in Excel to create one formula that looks at multiple factors
Hi, I am trying to write a formula that returns a YES or NO in one cell, based on several different sets of criteria for values in 2 other cells. I know how to write the formula for each different set of criteria, but i cannot work out how to string them all together so that all are considered for the YES/NO result. I have tried multiple ways of putting them together and it's just going way over my head and past my formula knowledge so would really appreciate some help in how to do it. (I've never managed to get my head around more complex OR functions which is what i think is needed here?) I have listed the separate formulas below: =AND(D10>=1, D10<=31, E10="Days NET") – YES or =AND(D10<=30, E10="Days after receipt") – YES or =AND(D10<=30, E10="Days after EOM") – YES or =AND(D10>=29, E10="Days after EOM") - NO or =AND(D$0>=29, E10="Days after receipt") - NO or =IF(D10>=30) - no In explanation: If the value in D10=30 AND the value in E10='Days NET', OR the value in D10 is less than 30 (regardless of value in E10), result is YES. Or If the value in D10=30 AND the value in E10 is 'Days after EOM' or 'DAYS after receipt', result is NO If the value in D10 is over 30, result is always NO (regardless of value in E10). D10 is an empty cell that can have any 2 digit number entered. E10 is a drop down list of 3 options only (Days NET, Days after EOM or Days after Receipt) Result formula is going in cell H10. I would really appreciate any guidance on how to create this formula. Thanks in advance!
How when using INDEX MATCH do I use OR when setting search array?
Hi I have a dashboard setup across three tabs on excel to assign people their work tasks, tab one is the easy read dashboard, second is the data sheet with all the tasks and where I can assign each person to each task, and tab three is where I keep all my formulaes safe 😖. I'm using Index match to show different things each time but the one I struggle with is when I want to put the two or more people on one task, at the moment I just copy the task onto the next row down as a work around but it’s clunky and messes with figures for task numbers etc. I use people’s initials and the task priority number to determine which order they should be done (sound like micro-managing but honestly it’s not). If I put them in the same cell it doesn’t work as it gets confused so I thought I could add an extra column but can’t figure out how to do “or” without it being confused if it sees someone else’s initials. My current one is =INDEX(‘Data Sheet’!A:J,MATCH(“JB1”,’Data Sheet’!J:J,0)2) JB1 is the variable for the initials. I would either like to make it so the Cell i have “JB1” in can have multiple initials in and the search would be able to look them up and pull the same results, or I can just add in Column K and L and it will search those also and pull up the same result and not be confused if I have different initials in them. hope this all makes sense. Would appreciate anyone’s help.Macro Formula Relative Referencing
Hi there I am trying to create a macro to use at the end of every month to convert a report into a format the client requires. The amount of rows in the report will differ every month so using relative referencing. I cannot figure out how to get the formulas in Cells G2 & H2 (which I have added while creating the macro) to reproduce regardless of the amount of rows in the report? They always stop at row 14. Thanks for any help. Sheet attached. Quit new to this. Microsoft® Excel® for Microsoft 365 MSO (Version 2505 Build 16.0.18827.20102) 64-bit PC Windows 11 BusinessExcel
Help, I have a spreadsheet with various data added and want to filter this to only show certain data. However the sheet is not filtering out all blank cells. I have removed the filter, resaved the document and ensured all cells are blank and tried again - and failed again. I have the same document saved this different data that is allowing the filter to work correctly. Any ideas on what I am doing wrong? thank you in advance MandyTable Merge from Web
Dear Experts, I need to export the below in a Single Table from below link :- https://portal.3gpp.org/desktopmodules/Specifications/SpecificationDetails.aspx?specificationId=3283 But when I use PQ, it gives me into different Tables, and I have to combine them Manually as below:- How shall , I merge them all into a Single Output? Thanks in Advance, Br, Anupam
REF Broken Formula Link Issue
Hello! I have created 6x files which contain formulas that link back to a specific cell from multiple external files all stored in the same drive. However, when opening each of these files, either half or all linking to external files break and become #REF erasing the formula that links to the external sheet. Strangely, this doesn't happen consistently as sometimes only half of the links break across some or all files, sometimes all of them, and a mix of working and broken links in the same files. I'm not sure why this is happening and am finding it difficult to troubleshoot. Has anyone experienced this before or know why this might be occurring? Any insight or help would be greatly appreciated! Thank you!Making Python-in-Excel worth it
I feel like Python-in-Excel has huge promise. Embedding a Seaborn correlation matrix in an analysis sheet with a couple lines of readable code? Keeping multiple dimensions of data in one cell efficiently? Making models that involve recursion and iteration without all the workarounds of LET/LAMBDA? Yes please!! I've messed around with it a lot but my experience has mostly been negative and from reading comments it sounds like a lot of others have had bad experiences too. A local version that was unlimited for free would be ideal of course. But I'm going to assume for a minute that isn't possible for business reasons. Most of my frustrations relate to these three things. So maybe if we could get these it would be worth it to use? A way to track usage and buy more credits so we don't get stuck with #BLOCKED! with no recourse Improve the xl() function so it works with variables inside it instead of just literals. e.g. foo = "B4" ; bar = xl(foo) currently doesn't work but it should. The error messages need to always tell you what line number the error is in I'm curious if other folks have tried it and agree or disagree with this wish list.
