Search results

Hello. Consider these questions solved! The simpler expression for MultiComboBox FILTER Formula is done with IsNumber edit, as is consistent/reliable when compared with Sequence, at least on my i3 Laptop with Excel 2021. 3 current/final file variations available here @ cross post...

Hello, having just said how easy Pivot Tables & Slicers (Once familiar with where TABULAR LAYOUT option is hiding!) are, and without any coding in Formula.... Well back to my original question, I wanted a simpler expression Formula edit for the 4Multi-dependent COMBO BOX, well.... In another...

Hello Thanks for suggesting PIVOT TABLE SLICERS. My final file attached, no formulas or vb, hence no ridicoulous over complicated feat with offset indirect lookup index match etc, no messing about with UNIQUE or SORT to display AZ, just SOURCE DATA, PIVOT TABLE & SLICERS, hooray! These...

CROSS POSTS: https://www.excelforum.com/excel-formulas-and-functions/1402036-filter-function-nested-if-multi-better-simpler-expression.html#post5804759 Lots of options...

HI. SEQUENCE formula: Inconsistent results, save 20kb file it then 22mb? Perhaps it works fine on XL365? XL21 on laptop gets confused with it. SLICERS: Absolutely why mess about with formulas when this feature can do it all with you. Only reason is I did formula approach is can never get...

Hello! This sort of works.... and sort of doesn't... for correct Combo Box selection. HOWEVER on COL B selection does NOT select correct data just the top most entries. For example select MAKE: BMW, MODEL: X4, you'll notice 3 SERIES is wrongly displayed in results data. So...... how to pair...

Hello After some thought since FILTER FUNCTION is either AND or OR, it is likely it best expressed as I've edited it! It is easy to follow Filter Formula Dynamic Boxes A-D + Blank (P1, X1,AF1,AN1, AV1) as seperates like this, but perhaps there is an even simpler solution!: DYN 1BOX (A) DATA...

Hello UPDATE: Merged all 4 Dynamic range results from multi dependent Combo Boxes using UNIQUE IF: =UNIQUE(IF(L2<>"",BT1:BY25,IF(K2<>"",BL1:BQ25,IF(J2<>"",BD1:BI25,IF(I2<>"",AV1:BA25,""))))) But now the question is... a way to: MERGE 4 Filter Function Sorts into 1 cell & hence 1 dynamic...

Hello Worksheet is searchable 1st column az with dynamic multi dependents, in other words data has 4 COMBO BOXES: I2, J2, K2 & L2 Valid data presented using FILTER FUNCTION in I22. However the question, is correct FILTER FUNCTION to accomodate either 1 COMBO in I2, and/or 2 COMBO in J2...

Hi, well TABS may include more characters then just year, and instead of lots of equals, the indirect formula directly refers to tab cell even though includes letters other just date numbers. ALPHANUMBERIC TAB TYPE: B2...

Hello 3D Indirect Spreadsheet (Auto sums of all relevant tabs) totals from several tabs in 1 summary sheet simply by tab name in column A of tab EV YEAR TOTAL. Further to recent previous post: https://chandoo.org/forum/threads/tab-name-indirect-automate-instead-of-year.51737/#post-294657 I...

Hello Year? I was referring to TAB NAME as just YYYY, as formula compares date =IF($A5<=$Z$2,....... Index Match? But if data can be from Various/Numerous Multiple TABS (ie many many many YEARS) how would Index Match work? Isn't Index Match specific to TAB? Maybe it just longer Formula, but...

Hello 1st reply, yes use of YEAR as is (YYYY) is best solution. 2nd reply, wow that is detailed/complicated, thank you for explaining! I'm hoping for simpler solution that would work in Excel2003. Perhaps something similar to Photos Index Match formulas in photo below? or 3D SumIfs files...

Hello Excel File version 2021 attached, presumably easier to construct compatible Formula format from broader range available in XLSX.

Hello My question is INDIRECT FORMULA that works alphanumeric TAB NAMES (Not just YEAR, but contains Year needed for auto date chk) to automate from TAB NAME in COLUMN A of 2ND TAB (EV YEAR TOTAL), which I have done successfully per Tab Name per YEAR columns B to P, but I want more creative tab...

?? These all have errors in Excel 2003!! ?? I'll leave it in MANUAL EDIT for 1st DATE of YEAR, 01-01-YY, totally bullet proof. No thanks to the Millennium Bug (ie a technological issue of outdated tech with 2 character/digit year going into another/New Century.... Aka Buck Rogers), the...

Right I'll try those..... & also hard to dodge the velocity of this the previous Formula did actually work, the Worksheet Tab name just needed formatting like a date: 01-01 etc However I found the impact of this making the purpose of each tab more vague on 1st impression as all start the same...

Maybe I'm asking the wrong question, how about: WORKSHEET TAB NAME as FORMULA DATE (YEAR)? Your Formula suggested did do as suggested however it was unusable because: = MID(CELL("filename"), 1+SEARCH("]", CELL("filename")), 128) 1. Not recognised as Date & can't be custom formatted as date. 2...

Or to make it much more obvious of the deliberate intention: =IF(A6<=$BG$2,INDIRECT(TEXT($A6,"yyyy")&"!$Y$34"),"") In tab TOTAL, cell C6. A6 indirectly refers to year then cell. The obvious next thing to celebrate all going well is to do same with refering to tab name as year "without" cell...

2020 tab refers to year 2020 2021 tab refers to year 2021 Total tab is summary of these seperate years. Formula in Year tabs calcs used/spent average apart from mot costs (£45+). Tab: TOTAL Cell: BF2 =DATE(YEAR(BG2),1,1) Hence how change ref of BG2 to Tab of Year such as 2020. Currently I...

1st Date of Year using Tab Ref, not Cell Ref? For example in Tab TOTAL Cell BF2: =DATE(YEAR(BG2),1,1) Hence how to substitute BG2 with TAB YEAR: 2020? The cause reason is to automate Years Tabs Cell X39, which currently is manually edited date. Cheers

D6 =INDIRECT(TEXT(A6,"yyyy")&"!$V$32") This worked for Columns B-L, few things: 1. If no data, how to change FALSE to nothing? 2. Columns AL-BC unable to construct working edit for A6 Ref instead of Direct Tab ref. A lso these are ARRAY FORMULAS that require CTRL, SHIFT & ENTER: Example in...

Hi Thanks but its the Diesel spreadsheet "TOTAL" tab that needs an edit. For example on Total what to edit '2020' with A6, INDIRECT FORMULA suggested only worked for COLUMN B: B6 =IF(A6<=$BG$2,'2020'!$Y$29,"") C6 =IF(A6<=$BG$2,'2020'!$Y$34

Here's a thought, perhaps somebody can suggest viable alternative of using ref to tab for further columns with an edit of: SUMPRODUCT SUMIF INDIRECT with Defined Name list of: Year TABS Example file attached: 3D SUMIFS Which I'm unsure how to edit formula, but I feel the answer to this riddle...

Hello thank you for rushing to my aid! This works for Column B only & my prowess in Excel doesn't know how to edit INDIRECT for Column C onwards: C6 =IF(A6<=$BG$2,'2020'!$Y$34,"") CHEERS