I have a Google Sheet linked to a Series of Smart Containers.
The Container has a formula that adds up the Total Man Hours and Estimated Man Hours. The formula works, until I ask it to display in the container then it creates errors. It also moves the container out of order and puts it at the bottom.
As a side note, if I move a shape between containers, shouldn’t it also update the Google Sheet?
Also, how do I refresh the data and have it appear in the shapes?
Best answer by Lance
Hi Industrial12,
I have a solution for part of your question that I hope works for you. I don’t know the answers to your questions about syncing the data back to the Google sheet and refreshing the data in the shapes.
Here is a link to the document I used as reference in the images. Feel free to explore it. https://lucid.app/lucidchart/caa04b60-4835-4025-878f-bd8d0962de35/edit?viewport_loc=552%2C198%2C1249%2C743%2C0_0&invitationId=inv_7256c867-d698-4a1d-9c01-0101e8aebedf
Adding the formula to the container
I have formulas like yours on these containers to sum the total available and scheduled (I said “used”) hours on the shapes in the container.
On the container, I type my pre-text “Total available hours: “, then “=” to open the formula editor. In the formula editor, I type “=this.”Total available hours” to add the data. I couldn’t see what you were typing that gave the error, so I hope this works.
Adding the formulas to all containers
If you add the formulas to the smart containers group (click the boundary surrounding the containers), you can click the triple-dot menu next to the formula and choose to “Apply to All Shapes”. This will add it to all of the containers and cards, but I don’t know of a way to only add to the containers.
Alternate solution
One problem with the solution above is that it requires you to add the text to every container individually, which sounds like a huge pain. Or you can delete the existing containers and duplicate the one with the formulas, but then your data is detached and you have to change the dates. I propose using conditional formatting instead.
You can add a conditional formatting rule (found by clicking the magic wand icon in the top bar) to apply only to shapes with the shape data “Total used hours” and that are of type “Container View Container”. And you can tell it to apply a Text Badge Icon where the text is a formula. My formula for available hours is “=CONCATENATE("A: ", this."Total available hours")”. That will make a badge appear in the top right corner of every container telling you the hours available. You can configure the color and position of the badge. I did the same for scheduled hours and made them different colors to quickly see what’s going on.
Here’s a final view.
Let me know if you have any questions about this solution.
+5Good afternoon,
Thank you for sending this in, and for the useful video of the issue. I am checking internally to get you assistance, and will follow up when I have an answer.
+11Hi Industrial12,
I have a solution for part of your question that I hope works for you. I don’t know the answers to your questions about syncing the data back to the Google sheet and refreshing the data in the shapes.
Here is a link to the document I used as reference in the images. Feel free to explore it. https://lucid.app/lucidchart/caa04b60-4835-4025-878f-bd8d0962de35/edit?viewport_loc=552%2C198%2C1249%2C743%2C0_0&invitationId=inv_7256c867-d698-4a1d-9c01-0101e8aebedf
Adding the formula to the container
I have formulas like yours on these containers to sum the total available and scheduled (I said “used”) hours on the shapes in the container.
On the container, I type my pre-text “Total available hours: “, then “=” to open the formula editor. In the formula editor, I type “=this.”Total available hours” to add the data. I couldn’t see what you were typing that gave the error, so I hope this works.
Adding the formulas to all containers
If you add the formulas to the smart containers group (click the boundary surrounding the containers), you can click the triple-dot menu next to the formula and choose to “Apply to All Shapes”. This will add it to all of the containers and cards, but I don’t know of a way to only add to the containers.
Alternate solution
One problem with the solution above is that it requires you to add the text to every container individually, which sounds like a huge pain. Or you can delete the existing containers and duplicate the one with the formulas, but then your data is detached and you have to change the dates. I propose using conditional formatting instead.
You can add a conditional formatting rule (found by clicking the magic wand icon in the top bar) to apply only to shapes with the shape data “Total used hours” and that are of type “Container View Container”. And you can tell it to apply a Text Badge Icon where the text is a formula. My formula for available hours is “=CONCATENATE("A: ", this."Total available hours")”. That will make a badge appear in the top right corner of every container telling you the hours available. You can configure the color and position of the badge. I did the same for scheduled hours and made them different colors to quickly see what’s going on.
Here’s a final view.
Let me know if you have any questions about this solution.
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to log in.
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to log in.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
