You say that you want the value of D5 on every sheet returned to cells in column E.
If that is what you want, then all you need is a series of formulas in the form = 'sheetX'!D5
... but this will not change based on the value in cell Report1 D5
You could lookup the value selected in the drop-down in D3, in a range of cells on each sheet and return an adjacent value.
The drop-down in cell D3 on a worksheet named Report1 has letters A to E.
There are 4 worksheets named Sheet2 through Sheet5
On each of these sheets is a table in the range D5 to E9
Column D cells contain the letters A to E
Column E contains values to be returned
5 A 2-D5
6 B 2-D6
7 C 2-D7
8 D 2-D8
9 E 2-D9
Sheet 3 has values in column E 3-D5 etc
Sheet 4 has values in column E 4-D5 etc etc.
Back on the Report1 worksheet you have a lookup formula for each of the worksheets Sheets 2 to 5
In cell E5 you have
=VLOOKUP($D$3,Sheet2!$D$5:$E$13,2,FALSE)and the same formula in cells E4, E5 and E6, but changed for the different worksheets. E8 is:
When you select E from the drop down you return the value from each worksheet adjacent to the letter E:
4 Sheet Value
5 Sheet 2 2-D9
6 Sheet 3 3-D9
7 Sheet 4 4-D9
8 Sheet 5 5-D9
Is this the sort of thing you were trying to do?