Getting Excel to read a text string created by CONCATENATE as a formula in another cell?

Hi All,

I am trying to create a template document for work that automates a few processes. The process involves importing CSV data through the Get Data button and as such will import a new sheet of which the name will vary.

I have got a macro that will list this imported sheet's name in a specified cell (for ease I have not included this in the attached I have instead just typed the name). I need to then use the imported sheet's name as part of an XLOOKUP formula.

Using CONCATENATE I can create the formula needed (shown in cell A4 of the attached), however, I cannot figure out how to get excel to read this as a formula in cell A7.

Any help would be greatly appreciated.

Many thanks,
Roy

Apologies I have not explained this brilliantly.

The formula that I have created through CONCATENATE in cell A4 works, the problem I have is that I have to copy and paste as value into another cell, then copy and paste that into the formula bar of cell A7. I am sure with all of excels capabilities that it should be able to read this directly from the result created in cell A4 I am just unsure of how to get it to do so.

Unfortunately, I can't share the complete workbook as it contains sensitive information.

Regards,
Roy

Slightly adapted a solution I found here, as you are using 365 https://superuser.com/questions/2533...were-a-formula

1. Open Name Manager
2. Create new name, call it "eval", refers to =LAMBDA(x,IF(TODAY(),EVALUATE(x),))
3. =eval(a4) should then do what you are asking.

Another way of achieving the same result is to use INDIRECT and ADDRESS:

=XLOOKUP(A6,INDIRECT(ADDRESS(1,1,,,A2)&":"&ADDRESS(1,11)),INDIRECT(ADDRESS(2,1,,,A2)&":"&ADDRESS(50,11)))

Note you only put the sheet reference in the first of the two ADDRESS functions which make up each range.