Convert text to formula in Excel Solved

Question

Hello everyone,

I'm presenting my problem:
I have several quote files in a folder and I would like to have a summary file with all the elements of these data in comparison mode!

Basically, in my quote files I have:
A1 Item Name
A2 Max Q/year
A3 Series detail
A4 Material type

I would like to have in an annex file:
Data \ File Names Quote 1 Quote 2 Quote 3
Item Name x x x
Max Q/year x x x x
Series detail x x x x
Material type x x x

For this, I can open the different quotes and make links manually (that way if I change something the comparison is still valid).

The thing is that I might end up with about ten files and in reality I have about a hundred lines! And I would have to repeat this for every new estimate (several per week).

So I found a way to create the formulas with the file locations, etc... (you can find all this in the attached file here ==> https://www.cjoint.com/?0IflGSCd8Hj). However, my problem is that these formulas are in text and I would like to activate them as formulas.

Could someone give me a hand?

Thank you!

Vaucluse · Answer

Hello
to keep it simple:
if you place in C3 the text corresponding to the full address except for the cell, you can simply write:

=INDIRECT($C$3&A4)

otherwise you can compose the address based on the principle:

=INDIRECT("["&C3&"]'Calculation sheet'!"&A4) while respecting all the elements of the required address text according to your files

(I can't see the exact address on my PC, as the links don't exist)

All that's left is to adjust the height to have the correspondence with the cell in A

Best regards

--
To err is human, to persist is diabolical

XavierMIHB · Answer

Hello Vaucluse,

Thank you for your reply, but it's not working, I have a #REF error.

It is possible to create a Quote 1 file in the same folder as the downloaded file; you just need to add the file extension in the filename cell.

Thank you in advance.

XavierMIHB · Answer

I just can't manage to do it:
- in C1 I have the file path which is exactly:
=> ='\\serveur-data\MIHB_DATA\COMMERCIAL\PROSPECTS\Gun\130619_RFQ_Plasturgie\
Obtained with the formula:
=> ="='"&LEFT((CELL("filename"));(SEARCH("[";(CELL("filename"))))-1)

- in C3 I have the file name hardcoded:
=> Battery Drawer.xlsm

- in A4 I have the target cell written in hard:
=> A4

- in C4 I therefore construct the path with the file name:
=> ='\\serveur-data\MIHB_DATA\COMMERCIAL\PROSPECTS\Gun\130619_RFQ_Plasturgie\[Battery Drawer.xlsm]
Obtained with the formula:
=> =Sheet1!$C$1&"["&Sheet1!C$3&"]"

- So I would like to construct the complete path which should be:
=> ='\\serveur-data\MIHB_DATA\COMMERCIAL\PROSPECTS\Gun\130619_RFQ_Plasturgie\[Battery Drawer.xlsm]Calcul Sheet'!B12
By writing the following formula:
=> =INDIRECT(C4&"Calcul Sheet1!"&A4)

From there I get a #REF.

XavierMIHB · Answer

Anyway, thank you Vaucluse for your help.

Convert text to formula in Excel

4 answers

Intrusive antivirus alerts and proposals

Account login hurdles on ps4

All-in-one pc search

Forgot password

Bluetooth for android galaxy a21s on windows 11 25h2

Asus vivobook pc from 2024

Excel, fill cells based on the given data specifications

Evolution of the pdf format

Good evening, i have a bug that prevents me from logging into an app

Unable to print from my smartphone.