Populate text in a picture box by referencing a table?

[lobo114]

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?

[#2]

@lobo114 

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.

[#3]

@HansVogelaar 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. 

[#4 · Accepted solution]

@lobo114 

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.

[#5]

You truly are amazing! I hope I am half this good in the future. Thank you again!

[#6]

If I want to use this Macro in another excel file that contains tabs that aren't labeled Farmer and Builder, do I export the macro to my other workbook and change the "Farmer" and "Builder" to what the other tabs are named?

[#7]

@lobo114 

Yes, indeed.

Make sure that you copy the code to the module belonging to the Table sheet (or its equivalent in that workbook).

[#8]

@HansVogelaar 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?

[#9]

@lobo114 

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.

[#10]

I understand now, it works now. Thanks for putting up with all of my questions.

[#11]

If a text picture is blank, is there a way to hide the blank box with another macro and then unhide it and populate the box with text once the other macro criteria is met?

[#12]

@lobo114 

See the modified version of the workbook that I attached earlier.