User Profile
Lorenzo
Silver Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: Viva Engage - Photos Not Loading in Conversations
Hi I had no idea what Viva Engage was (https://www.microsoft.com/en-gb/microsoft-viva/engage?msockid=09d76e3572d66fa03c657a1073916ee3). Obviously this has nothing to do with Excel - this forum. Suggest you post your issue in the appropriate Viva Engage community: > Select a Discussion Space (at the bottom of the page)Re: Non-Consecutive Cell Referencing
MattKW1 Option from m_tarler is much better - from an efficiency perspective as well. If you want to stick to INDIRECT the following addresses his point re. locked column A on the Orig sheet, the sheet name remains locked though *: =INDIRECT( ADDRESS( QUOTIENT( ROW(B3)-ROW(B$3) +1, 5 ) +ROW(Orig!A$3), COLUMN(Orig!A3),,, "Orig" ) ) * A workaround exists with Excel >/= 2013Re: Calculate overlapping hours
Hi To help people who search for existing solutions it would be smart of you to revise the title of this thread with Calculate non-overlapping hours ;-) - Thanks With Power Query > Pivot Report: Added dummy records to those you provided. Looks good to me... #1 Please double-check and confirm (or not). Sample (to download) avail. at: https://1drv.ms/x/c/1cd824d35610aacb/IQCkJLQ4qI2hQ6OG2-ntg7wiATHCsDR7AVWdpHB_O7bJjgs?e=xn9UML #2 Let me know if you prefer a flat Table instead of a PivotTable Report How to use: With your data in TableData > Right-click somewhere in PivotTable Report > Refresh Notes: 1) If you double-click on a number in the [Total Hours] col. of the PivotTable Report a sheet will give you the details of what made that number. However, you might get less records than in TableData as the query filters out some records for perf. reason. As an ex. : on 11/06/25 Freddie has 9 records but double-clicking on his 10.5 [Total Hours] for that date you get 8 records. The one that's filtered out is: because [Start Time] > [End Time] 2) Also filtered out are records, where for the same [Supervisor] on a given [Date], we have duplicated [Start Time] & [End Time] 3) With the provided sample dataset, [Supervisor] Foo isn't reported as it has only 1 record where [End Time] = [Start Time]Re: Conditional data validation drop downs
Hi Assuming your data is on Sheet1 with Names in A2:A5: Create a new Sheet2 (you can hide later): #1 On Sheet2 in A2 then copy right as necessary: =FILTER( Sheet1!$A2:$A5, Sheet1!B2:B5 = "available", "" ) #2 Switch to Sheet1 > In Data Validation on B7 and copy right as necessary: =Sheet2!A$2# (Don't forget the # sign at the end) Sample at: https://1drv.ms/x/c/1cd824d35610aacb/IQBN8UpV0kxfQJ5AFzs1WF3hARCxMpkCqNADQBR6vtlVPMY?e=uaMDfXRe: Data Pulling
Hi No feedback/mark as solved on your previous case (Add Letter Into Number) - this doesn't encourage people to respond… You seem to have a relatively complex workbook + it's not obvious how you get the desired result. Not sure someone will spend time re-creating existing data, running the risk it doesn't reflect the reality Suggestion : Share - with OneDrive, Google Drive… - your workbook inc. your current PivotTables Indicate which version of Excel you run + on which platform (Windows, Mac, Web…)Re: Name Manager using for create name which is contain atleast two name.
Hello #1 Unclear to me what you actually expect to achieve. Below is what I understood: One column values from each Table to be combined as a single list. Then keep unique values only. If this isn't what you expect post a similar picture with your Tables + the expected result #2 What version of Excel do you run + on which platform (Windows, Mac,...)?Re: show a due date using data from multiple columns
Alternative with PIVOTBY Credit due djclements (Unpivot/repivot option in Need help with excel | Microsoft Community Hub) =LET( source, TAKE(Table1[#All],, 7), body, DROP(source, 1), today, TODAY(), startWeek, today - WEEKDAY(today, 2) +1, endWeek, startWeek +6, dueValues, DROP(body,, 2), arrDueThisWeek, (dueValues >= startWeek) * (dueValues <= endWeek), IF( SUM(arrDueThisWeek), LET( headers, TAKE(source, 1), dueHeaders, DROP(headers,, 2), pvtRows, TOCOL( IFS(arrDueThisWeek, SEQUENCE(ROWS(dueValues))), 2), HSTACK( VSTACK( TAKE(headers,, 2), CHOOSEROWS(TAKE(body,, 2), UNIQUE(pvtRows) ) ), DROP( PIVOTBY( pvtRows, TOCOL( IFS(arrDueThisWeek, dueHeaders), 2), TOCOL( IFS(arrDueThisWeek, dueValues), 2), SINGLE,, 0,, 0 ),, 1 ) ) ), VSTACK( "Comment", "Nothing due this week" ) ) )
