Sep 28 2023 11:53 AM
Sep 28 2023 11:53 AM
In my attached spreadsheet on the Table tab, I would like to be able to type a responsibility in column B and have it populate the picture boxes on the Farmer and Builder tabs with each duty title listed next to the responsibility. In this example, on the Farmer tab the first box would say "Plant" the second "Fertilize" and the third "Harvest". I would also like it to reorganize the boxes if a responsibility changes. In this case if the Farmer's duty is no longer "Plant", then the first box would be "Fertilize" and the second would be "Harvest", with the remaining boxes being blank until the Farmer gets another duty listed in the Table tab. I don't think I can write a formula to make this work in the picture box. I'm thinking that the formula would have to be written on the Table tab and then link the result to the picture box. Anyone know if this is possible?
Sep 28 2023 02:19 PM
See the attached version. Cell A1 on the Farmer and Builder sheets contains a FILTER formula. Its results have been hidden by applying the custom number format ;;;
The first rectangle has formula =A1, the second one =A2 etc.
Sep 29 2023 05:47 AM
@Hans Vogelaar That worked great, thank you! I did change the formula to add an IFERROR to the front to eliminate a #CALC! error if nothing was assigned to someone. If I can further complicate it for you, the final step I'm trying to make work is to link the results of your filter formula to another tab. In my New Duty Example, once it populates the text picture boxes, can those boxes be linked to my newly created tabs? For example, on my Farmer tab, when it populates the "Plant" text in the first box, can the first box be linked to my new "Plant" tab? I tried linking the words on my Table tab hoping it would then link the words on the Farmer and Builder tab, but that didn't work. I'm trying to find a way to have it auto link without having to click on each box and manually link it once it is populated. It would be nice if it could make it so the whole box would be linked, not just the text in the box too. If it's not possible, I can just manually link the picture box. Thank you for any insight.
Sep 29 2023 06:22 AMSolution
See the attached workbook. It is now a macro-enabled workbook (.xlsm).
You'll have to allow macros when you open it.
To view/edit the code, right-click the sheet tab of the Table sheet and select 'View Code' from the context menu.
Sep 29 2023 07:28 AM
Sep 29 2023 07:32 AM
Make sure that you copy the code to the module belonging to the Table sheet (or its equivalent in that workbook).
Sep 29 2023 08:53 AM
@Hans Vogelaar Here is a stripped-down version of the file I tried to copy the code to. I'm getting a "Reference isn't valid" error on my Template Tab "Additional Duties" column of boxes when I try to click the box to link me to the corresponding tab. Instead of putting you original Filter function in the A1 cell and hiding the function, I moved it to my Drop Down tab instead. Is this where I'm messing up?
Sep 29 2023 11:06 AM
The formulas of the rectangles for Additional Duties return values such as UTM (P). There is a sheet UTM but no sheet named UTM (P). So the hyperlinks fail.
Sep 29 2023 12:12 PM