Assistance pulling information from one sheet to another automatically

SmartieSheet

Hello, SmartSheet Community! I could use your help.

I have created an example of my problem below. The actual sheets have many more columns. Sheet A needs information sorted by Person Name; Sheet B has information sorted by Pet Type.

Sheet A - This is where I would like information to be filled in automatically based on what is in sheet B.

Sheet B - This is where the Sheet A should draw information from. When new rows are added to Sheet B, I would like the appropriate rows in Sheet A to be automatically updated. So, if Fred is added as a Cat Sitter in Sheet B, Sheet A should update to show Fred as a Fish and Cat Sitter.

←- What Sheet A should look like when information is automatically collected from Sheet B

I've tried vlookup and indexmatch functions, but I can't seem to get it right. Sticking points seem to be having multiple names in a single cell in Sheet B and getting the different pet types into a single cell in Sheet A. I'd rather not have to purchase an extra feature, like data shuttle. I feel like I should be able to use a combination of formulas and automations.

Thank you in advance for your help!

Protonsponge

Can I clarity in your 3rd picture - are you looking for the pet names to go in to the Sitter column and owner column or is there an error in that image?

SmartieSheet

Hi Protonspounge, thanks for replying!

Allow me to try to clarify, the final result I'm looking for (picture 3) is: Sheet A will have Person Names in the first column and pet types in the following two columns. For example, Abby is a dog owner and can be a hamster-sitter. Betty owns a cat and fish and does not pet-sit for any type of animal. (There are no pet names.)

I hope that helps!

Protonsponge

Hello @SmartieSheet - I am not 100% sure on your data flow… apologies for the confusion but I think something in this direction could be helpful for you…

So in sheet A you have peoples names and you want to bring in more than 1 or more animals based upon data in another sheet.

Sheet A for example

If in sheet B you could have a column for animal 1 and a separate column for animal 2 as per below

you could use the following formula to bring in the two animals based upon the name using INDEX/COLLECT and if you set the column to wrap text, the two animals will sit on top of each other in a cell like the image below.

=INDEX(COLLECT({01_Another Sheet_Animal 1}, {01_Another Sheet_Owner_1}, [Person Name]@row), 1) + CHAR(10) + INDEX(COLLECT({01_Another Sheet_Animal 2}, {01_Another Sheet_Owner_1}, [Person Name]@row), 1)

Again, sorry for not following your data flow ask but maybe there is something useful in the above?

SmartieSheet

Thank you, @Protonspounge! I appreciate your suggestion. I have limited ability to modify Sheet B, but I am giving it a try. I'll report back :)