Forum Discussion
Populate text in a picture box by referencing a table?
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?
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.
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.
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.
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.
