Best Of
Re: Three criteria SUMIFS
Continued from above
3 Adding in dates to the SUMIFS
You need to follow the pattern of range to look in, thing to look for, range to look in, thing to look for. AND is assumed in all SUMIFS. The result will be where all the parts are true.
So, to add your requirement for starts on or after you would add this part in bold. Note the is only one parenthesis between DATE and 2024.
=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1))
And then to add an end date you need to repeat the pair:
=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1), {Date}, @cell <= DATE(2024, 1, 31))
So now this:
Gives us this:
4 And finally
I noticed a little "Month" column on your sheet. I would love to include that in the formula so that you can change that one cell with "January" in it and update all your formulas without doing anything to the start and end dates.
Let me know if you are interested.
KPH
Re: DataMesh & Locked columns
It will depend on who built the Data Mesh workflow.
- If the user who created/runs the workflow is an Admin or Owner, any locked columns/rows will updated.
- If the user who created/runs the workflow is only an Editor, then no, the locked row won't update.
Data Mesh is an automated substitute for the shared collaborator and takes on their permitted actions. Whatever they can do on the sheet, the workflow can do. Whatever they're restricted by, the workflow is restricted by. Does that make sense?
Genevieve P.
Re: February Question of the Month - Join the conversation and receive a badge
When I was a kid - I wanted to be a veterinarian or doctor... organic chemistry put paid to that dream!! Ended up with an Economics degree and an interesting and varied career in banking and start ups - a nice long gap to raise a family - and finally as a PM in a group insurance company. Most proud of my kids for sure!
Dixie Morse
Re: Is there a way to adjust row height?
It's hard to believe that it's now September 2023, and this simple feature is still not available.
DianaR
Re: Create Report Summary, Similar to Sheet Summary
Agreed, this would be helpful. To expand on the idea, these Report Summary fields could be referenced during PDF export in the "Add note to header" field to allow for more dynamic control over issued text.
Kyle Chipman
Re: VLOOKUP on a Multi DropDown List
Hi @@Robert McKnight
Hope you are fine, i changed your current formula
=IFERROR(VLOOKUP(Barcode@row, {Current Hard Drive Requests Range 1}, 9, false), "")
with the following formula to solve the problem of pick more than one dropdown value.
=JOIN(COLLECT({Responsible Owner(s)}, {Loaned Drive}, CONTAINS(Barcode@row, @cell)))
please check the workspace you shared with me. and don't hesitate to call if you have more issues.
Bassam Khalil
Re: Combine Data from 2 sheets using one common column as a link.
We found out we need to use Brandfolder to allow for the metadata to link between the sheets.
Kevin7859
Re: Do not see "snapshot" option to request previous version of document.
Figured it out! I did not realize that not every line of the history can give a snapshot. Once I scrolled down a bunch, I found lines with down arrows where I could request a snapshot! Thanks.
jccarley
Re: Sheet formulas are not auto-updating until the sheet is opened
Hello @Ami Veltrie
I believe it's still an issue.
I update sheets with data shuttle using expressions, such as the TODAY() function within them, and that seems to also update the TODAY() function without having to open the sheet.
MichaelTCA
Re: Populating parent and child rows with the same data
Hi @Tamsin
Your formula is working correctly! In your screen capture, there are blank cells in helper VRN column for the child rows. This means the COUNT of the CHILDREN in that specific column is 0, since there is no data in those child rows.
Instead of referencing your helper column in the first COUNT, make sure you're referencing a column that will always contain text or data when a row is submitted. For example:
=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([helper VRN]@row), [helper VRN]@row)
Does that make sense?
Cheers,
Genevieve
Genevieve P.