User Profile
Riny_van_Eekelen
Platinum Contributor
Joined Sep 04, 2019
User Widgets
Recent Discussions
Re: Formula help - why doesn't this work for January dates please?
Please clarify what this odd formula is trying to achieve. Both MONTH functions return an array of TRUE or FALSE if the dates in the ranges are in December (true) or not (false). Then adding them together results in an array of 0's, 1's or 2's. For instance, if the fist date in E falls in December but the first one in H does not it returns 1 + 0 = 1 for that instance. No need for the double "--" and what is SUMPRODYCT supposed to do? Don't you just want to SUM the resulting array? And what 'random total' do you get for January? Share your file or at least add a screenshot so that we can see what you are working on.Re: How can I display negative values for time in calculation results
As mathetes mentioned, there's no such thing as negative time. BUT..... switching Excel to use the 1904 date system will allow you to calculate with and display negative time values. Though, changing the data system will shift all dates in your file by 4 years. If you don't have real dates in this particular workbooks that may not be a problem then.Re: PowerPivot not returning correct data
Kindly ignore Kidd_Ip 's AI generated answer. Just use the Month and Invoice number fields from the 1-side of the relationship and then it will work as you expect. https://onecom5409298.sharepoint.com/:x:/g/EUjiPZibwUBBgmIRbXMUIRABXdytx63J1VbZ1WTQhvEFSw?e=6uc8QbRe: 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.
