User Profile
Riny_van_Eekelen
Platinum Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Vlook up issue related to format of cells between two different files
I suspect that your 'tiger codes' in file B are texts whereas the 'name codes' in file A are numbers. Simply formatting both columns as Number does not change a text to a number or vice versa. VLOOKUP will thus not find a match and always return #N/A. Probably easiest to transform column B in file A to text with Text-To-Columns. You find the icon on the Data ribbon. Save you file before doing this so that you can always revert back it in case it doesn't work as expected! Select B2:B616, Text to columns, step1 change nothing, next, step 2 change nothing, next, step 3 set the column data format to Text, Finish. Now the name codes in file A are all texts and VLOOKUP should be able to find matching codes from file B.Re: Power Query - SKU Generator
Your screenshots don't show the query you have built and it's not clear what should 'happen' upon a refresh. Can you share a link giving access to your file on OneDrive or similar. And kindly ignore Kidd_Ip 's comments on Loading options. PowerQuery on a Mac loads back to Excel by default. Each query into its own sheet. Very annoying and you can't choose the loading destination. There are work-arounds but they aren't user friendly.Re: Adding to a formula
You need to add a COUNTIFS( ) for each of code. So, in your first formula the first COUNTIFS [if_true] would become three COUNTIFS like this: COUNTIFS( $A$13:$A$377, ">=" & $A$1 + 1, $A$13:$A$377, "<=" & $A$377, Q13:Q377, "H" ) - COUNTIFS( $A$13:$A$377, ">=" & $A$1 + 1, $A$13:$A$377, "<=" & $A$377, Q13:Q377, "L" ) - COUNTIFS( $A$13:$A$377, ">=" & $A$1 + 1, $A$13:$A$377, "<=" & $A$377, Q13:Q377, "PH" ) * 0.5 You need to do that also for the [if_false] argument. And similar for the 2nd formula.Re: Formula in Excel Spreadsheet not work
The use of an approximate match in VLOOKUP requires the lookup range to be sorted in ascending order. Yours is not and the CF formula will never give the correct answer. And when you think it does, that's just a coincidence. What is the logic for the conditional format? Can you describe it in words? The value of 50 seems to your breaking point and you want to return the next lowest value. Correct? That would be 22, while your VLOOKUP returns 20. This is due to the fact that VLOOKUP performs a so-called binary search.Re: Titre axe secondaire
Forgive me for responding in English, but I don't have French Excel and I trust you can translate my message in your browser or some other service. Select the chart. Then, on the Chart Design ribbon select the Add Chart Element icon on the far left. Select Axis Titles. Oddly enough it indeed does not let you select the Secondary Vertical axis, but when you press 'More Axis Title Options...' a title box will appear with the secondary axis as well.Re: How to filter visible cells by formula after applying filter to a dataset.
I would add a helper column that check if the row is visible or not and then a regular FILTER formula as demonstrated in the picture below. In your real model, move the FILTER formula away from the data table as it will otherwise be partially hidden when you apply a filter to the data table.
