Indirect function closed workbook

Question

Hello,

I am using an Indirect formula in a file named "September," which refers to cell A1 in another file named "01-September." The reference file ("01-September") varies depending on the date, which is why I am using the Indirect function. The problem is that I can't retrieve the information if this file is closed.
I have already looked at quite a few forums, which suggest that a VBA function would be appropriate, but I haven't found any code examples to apply.
Do you have a solution to propose?
Thank you in advance,
Chrystele

Raymond PENTIER · Answer

It depends on the formula... More precisely on the search function that includes the INDIRECT function: IF - COUNTIF - SUMIF - VLOOKUP - SUMPRODUCT - OFFSET - MATCH  -- Retirement is great! Especially in the Caribbean... :-) Raymond (INSA, AFPA, CF/R)

CHRYSTELE LIMOU · Answer

Hello and thank you for your response.
I do not use any search or calculation function but directly the indirect function.
You will find the two files attached.

http://www.cjoint.com/c/GIdlc053hz5

Chrystele

Raymond PENTIER · Answer

Chrystèle, I believe I'm going to let you figure it out with your file: The one you attached doesn't match the explanations in your initial message at all: it's almost empty, with just 4 data points and no formulas or functions...  You stated, "You will find attached the two files" but there is only one file containing 2 filled sheets and one blank sheet.  -- Retirement is nice! Especially in the Caribbean... :-)Raymond (INSA, AFPA, CF/R)

chrystele · Answer

I recreated the links.
Here are the links, (one link per file):
http://www.cjoint.com/c/GIdnL1tArU5
http://www.cjoint.com/c/GIdnNfnJlw5

In the file "September", I want to retrieve the information from the file "01 September" (the 100,000 from cell A2) into cell B2 of the file "September". I use the INDIRECT formula for this:
=(INDIRECT("'["&B1&".xlsx]"&"Equipment 1"&"'!$a2"))
B1 = Name of the search file -> 01-September
Equipment 1 = the tab where I want to look for the information
A2 = the cell where the information is located
I get 100,000 if the file 01-September is open.

Thank you,
Chrystèle

eriiic · Answer

Hello,

no, Indirect does not work on a closed workbook.
Here is a custom function:
https://excel.developpez.com/faq/?page=Recherche#IndirectClasseurFerme
eric

--
By continually trying, one eventually succeeds.
So the more it fails, the more chances it has to work. (the Shadoks)
In addition to the thank you (yes, it does happen!!!), remember to mark it as resolved. Thank you

Raymond PENTIER · Answer

Chrystèle, working on Excel is not difficult, but it requires the utmost rigor! Your formula in B2 refers to the file whose name is in B1, so ""01 Septembre"; however, the file you sent us is named ""01-septembre"! Once you correct this error, close the file 01-septembre; Excel will automatically indicate, in the formula in B2, the complete path by adding the name of the directory in which the closed file is located. But if you start moving one of the folders, or sending it via USB or email, everything falls apart! -- Retirement is nice! Especially in the Antilles... :-) Raymond (INSA, AFPA, CF/R)

chrystele · Answer

Thank you Eric,I am sending you back the file "September". I copied the function and the formula LireCellule_ClasseurFermé, but I feel like the function is not being taken into account, I really don't see how to do it... http://www.cjoint.com/c/GIdqQ7wAzS5 Chrystèle

ccm81 · Answer

Hello everyone

Your file in return
http://www.cjoint.com/c/GIduJnaQueB

> eric.
1. people are mean, I reset the counter to zero ;-)
2. I had to modify the Target calculation like this
Target = Cell.Value & ":" & Cell.Value
3. if you could comment the macro code ....

best regards

Indirect function closed workbook

8 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.