Generating formula from drop down boxes

[swankyg]

I'm trying to create a sheet that will compare data between two other sheets (at least one from different workbooks). Using drop down boxes in column A you can select which workbook to use (the name of the workbook follows a pattern: text/FY/Quarter/Region). This is shown in B29, generated from the drop downs in column A.

I've written a formula to compare the values that I can copy across cells (shown without =). I want to generate this formula, so that when I select from the dropdown boxes, the formulas all change to reflect new data.

How do I do this? I've tried a couple of things that didn't work, I'm at a loss. Thanks for any help.

 

[Toadstool]

Hi Swankyg,

You'll need to use INDIRECT, something like this.

Your FY sheet

Front Office-KPIs-FY21Q3 (HK).xlsx
	F	G	H
5
6		Dog
7		Cat
8		Mole
9
HK

The retrieval sheet

Swankyg.xlsx
	A	B	C	D	E	F	G	H
4	Select
5	21						This sheet	Same cell from column G in Front Office-KPIs-FY21Q3 (HK).xlsx
6	3						Rabbit	Dog
7	HK						Goat	Cat
8							Sheep	Mole
HK

Cell Formulas
Range	Formula
H6:H8	H6	=INDIRECT("'[Front Office-KPIs-FY"&$A$5&"Q"&$A$6&" ("&$A$7&").xlsx]"&$A$7&"'!"&CELL("address",G6))

Cells with Data Validation
Cell	Allow	Criteria
A5	List	20,21,22,23
A6	List	1,2,3,4
A7	List	EMEA,HK,US,LAD