User Profile
Martin_Weiss
Bronze Contributor
Joined Apr 04, 2017
User Widgets
Recent Discussions
Re: Conditional Formating Formula
Hi Frank1060 that's a bit tricky because you need to calculate the time difference between current date/time and the date/time of the call. To do this, you need to combine the date and the time of the call (add them up). Then you can calculate the difference to the current time by using the NOW() function. The formula in the formatting rule would be: =AND((NOW()-($A3+$B3))*24*60>40;$C3="Not contacted yet") In column E you see the time difference in minutes. This column is not important for the solution, it just illustrates how the formula works. Hope this helps. Cheers, MartinRe: If, and, isnumber, match, hyperlink
Hi AngelaMarieCarumay thanks for the additional information. The issue is, that even if already the first criteria is met and the formula delivers "unavailable", it creates a invalid hyperlink. So my proposal would be to create also for "unavailable" and "full" a hyperlink which directs only to the current cell. So in fact, there would be a hyperlink that does nothing. Maybe try this: =IF(B6=TODAY(),HYPERLINK("#","Unavailable"),IF(ISNUMBER(MATCH(D9,'Sheet 2'!B2:B20,0)),HYPERLINK("#","Full"),HYPERLINK("#'Sheet 2'!A1","Book"))) Please note, that this formula only checks the time in D9 against the times in range B2:B20, but it does not consider if the date in A2 equals the booking date.Re: If, and, isnumber, match, hyperlink
Hi AngelaMarieCarumay could you be a bit more precise on the exact criteria, it would be easier to provide a good solution (maybe a screenshot etc.) Generally spoken, the answer would look like this: =IF(logical_test,HYPERLINK(...),something_else) So, if the logical test results in TRUE, the hyperlink function will be used. Otherwise something else (e.g. a blank cell, a comment, a value etc.)Re: pivot table and pick list problems
Hi HHengler ok, second issue could be the type of the data table. If it's not formatted as a table (menu "Home | Format as Table") then the pivot table refers to the original dimensions of the source table. In this case you have to click in the Pivot table and then choose from the menu "PivotTable Analyze | Change Date Source" And then select the complete range of the data table, including the new lines. To avoid this, use the above mentioned "Format as Table" function. This way, your source table is completely dynamic and grows automatically when new data is added. And a pivot table the refers to such a formatted table gets always the correct data range (after a refresh, of course). Kind regards, MartinRe: pivot table and pick list problems
Hi HHengler pivot tables show new data only after a refresh. So, usually all you have to do is a right-click somewhere in the pivot table and choose "Refresh" After that, you should see the new values in the dropdown lists in the pivot table. Kind regards, MartinRe: Pivot with a mix of static and transactional data
Hi WillSmyth yes, it's possible datamodel / Power Pivot. You need to load the transaction table into the datamodel (menu "Power Pivot | Add to Data Model") In the datamodel, you need to create a calendar table (menu "Design | Date Table | New) Then create a relation between transaction table and calendar table based on the date field. Create a pivot table from the datamodel (menu "Insert | PivotTable | From Data Model") Add a timeline and use the date field from the calendar table (not from the transaction table!) Open the PivotTable Option, Display tab and tick the checkboxes "Show items with no data on rows/columns" I have done this already in the attached workbook. Kind regards, MartinRe: Conditional Formatting controlled by Drop Down
Hi Julli_Einarsson thanks for the screenshots. Ok, this is a bit of a challenge 😉 I prepared a proposal in the attached file. It requires that you maintain all the blackout date ranges in a single table, like this: This table gets loaded into Power Query and gets transformed to a list with all single blackout dates and then loaded back into Excel, like this: If you change or add apps or date ranges in the blue table, all you need to do is to refresh the green table with a right-click. That green table is then the base for the conditional formatting rules. As you have now every single relevant day per app, you can easily setup the appropriate rules, e.g. =COUNTIFS($R$5:$R$232,$B$2,$S$5:$S$232,A5)<>0 I hope, this helps. Kind regards, MartinRe: I've got the SUMIFS blues
Hi MBMAdmin I do not have a formula solution for you. But instead, I propose Power Query. In the attached file you find my solution. The list with transactions has been converted into a formatted table. Beside, there is a reference table with the transaction fees per code. Both tables are then imported into Power Query and transformed, so the individual fees can be calculated. The results are directly loaded from Power Query into a pivot table. All you have to do is maintain the table with the transaction fees and then do a refresh on the pivot table once there are new payments made. Kind regards, Martin
