row to column problem with applying functions to other entries/formatting concerns

What I am trying to do is have two sheets, one in a sort of chart format and one in a filterable list format. The end goal would be to have the chart sheet be automatically updated as one changes the list sheet, this allowing for a good visual chart of products along with a quick way to find the one you want.

What my issue is: the products are organized in rows in one sheet, and columns in the other. I can get functions relating the sheets, but know there must be a way to transfer the function from one product to another without it taking the wrong information. That is, I have basically a matrix of products organized by different categories. Category A contains products 1, 2, 3, and 4. Category B contains 5, 6, and 7, etc I associate product 1 in the chart with product one in the list with no problem, but it does not like it when I try to drag the formula over to product 2 in the chart, and just shifts to the right one in the list - giving me product 1 information, just shifted. How do I easily apply a formula to all the products without doing it one by one? (I have tried TRANSPOSE as well, but it does not carry over correctly) I would ideally like the format to be used in the future and expanded upon, as well.

Then one more side issue - for some of the categories under each product, there are multiple entries....and in others just one. I was going to just use "ALT-ENTER" and get multiple entries in the same box so everything works out well in the chart, but then the formatting doesn't stay the same. That is,
"kitty
puppy"
goes to "kittypuppy"
This obviously would not be the most ideal for the filterable list, though, but I can't think of a better way without having to deal with cells displaying error messages that there is no data or displaying 0 or something.

Any help would be greatly appreciated!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!