Formula needed to auto populate in sheet2 & sheet3 based on test typed into cells in sheet

[excelteam777]

Please Help,

When I enter text on sheet1 in cells B2:B11, C2:C11, and D2:D11, I want the text entered in the B cells from sheet1 to populate onto sheet2 in either B5:B8, C5:C8 or D5:D8 based on the corresponding text from sheet1 C1

The same should go for sheet3, when I enter text on sheet1 in cells B2:B11, C2:C11, and D2:D11, I want the text entered in the B cells to populate onto sheet3 in either B2:B5, C2:C5 or D2:D5 based on the corresponding text from sheet1 D1

Example: On sheet1 I entered the word APPLES into B2, ONLINE into C2, and FRUITS into D2.
On sheet2 the word APPLES should populate into cell B5 which is under the heading ONLINE in B4.
On sheet3 the word APPLES should populate into cell B2 which is under the heading FRUITS in B1.

[FlameRetired]

Try this:
in B5 of sheet 2 array enter this formula:

Please Login or Register  to view this content.

and fill down and across to D8. Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then in B2 of sheet 3 array enter this formula:

Please Login or Register  to view this content.

and fill down and across to D5.

The workbook is attached.

[oeldere]

I would solve this with a pivot table.

See the attached file.

[excelteam777]

Thank you so much