Apache OpenOffice Community Forum

Hi People.

Im trying to build a path to an external files cell contents for each row in the attached file, depending on the Agent number. So the formula will take the Agent number for the cell and build it into the path name and the filename. This way as Agent files are created, teh overview file will have the scores stored already.
All of the external files fall into the same naming convention and location based on Agent numbers:
e.g. All filenames are [Department][Agent number][file format] ("QA Agent 7007.ods") - QA/Voice/Agent 7007/QA Agent 7007.ods.....so as you can see its just the Agent number which needs to built into the external reference.

Should I use CONCATENATE?

Formula in Sheet "Results Voice" cell D8 (currently only the direct reference)
Agent name in cell B8
Path in Sheet "Menus" cell F3
Naming convention in Sheet "Menus" cell G3

Im imagining something like:
=file:///W:/QA/Call Quality/Agents results/Agent(CONCATENATE(B8;".ods'#$Calls Sheet 1.O42";))
.....hopefully resulting in
='file:///W:/QA/Call Quality/Agents results/Agent 7001/Quality Agent 7001.ods'#$'Calls Sheet 1'.O42

Any help is very much appreciated Gents
Cheers.
P

Strange: You have two (or more?) occurrences of the specialising part (AgentNumber) in your example, but you only try to insert one by your call to CONCATENATE(). I would suppose the "7001" is expected to show in every place as the substitute for a placeholder.

Anyway: You need to use the INDIRECT() function if you want to creata a reference based on text.
You can work with the ordinary concatenation operator then which gives better readable formulae mostly. In your specific case you might better use the SUBSTITUTE() function, because you basically want to substitute for a placeholder.

Unnegociated advices/remarks:
Stop using everywhere names containing spaces or special characters. Useless complications.
I never would create a spreadsheet containing dozens of external references into different files. Each one of these files needs to be opened when loading and updating the current one. There are additional reasons. (What are your important reasons to try it that way?)
If you want to get a view looking somehow like good old listing paper, you might better use a CF for it.
I would probably not feel well being an agent affected by the results of automated means of the kind.

OpenOffice V 3.1 is very old (2009?) meanwhile. I can no longer tell which current features it might support only buggy / not at all.

See reworked and returned example.

Hi Lupp.
Thanks for your reply. Let me try to answer some of your questions.
Firstly my stance on Calc and its correct usage is that I am trying to automate a lot of different manual tasks, but I need to present the files to management in a way that doesnt involve a large learning curve. Unfortunately none of them are familiar with the gutss of Calc/Excel.
This is why it appears I am attempting things from the wrong angle sometimes. So apologies for that.

External references - Like I said, its difficult enough to get them to stick to things like naming conventions. so my plan was every time we execute an assessment, as the file closes the master file (this one) is automatically updated. However I didnt know that the referenced file needs to be opened for that to happen.
The results are not automated, only the reporting of them, Im soo good to my Agents!

Lastly the formulas you very kindly proposed return the #REF! error......could this be because of my Libreoffice Caveman Calc version?
Cheers.
P

Glad to hear you are good to your agents! (Just had an hour of reading John Steinbeck.)

Concerning my proposal to use INDIRECT() and how to do it in detail (two variants): Preparing the reworked file for you I also saw, of course, the #REF! error alert, because the referred files didn't exist on my system. If you also see this alert, you need to check for these files once more... and for the (badly) named sheet (If there are two spaces instead of one in a place, e.g, reference must fail.)...

I cannot create a tree of files having the exactly same pathnames as those on your system (I would even need to create a drive W: to be precise.), and I would never create files with spaces in their pathnames if not forced by terrible threats.

I can, however, verify that the INDIRECT() function still works the way I told you. I did it with LibO V 6.2.3 under Win 10 to make sure there isn't a bug.
Thus: Use an extra cell to calculate the result of whatever is placed as INDIRECT()'s argument, and compare it character by character with what you would need to get the reference by directly entering it into a cell (preceded by the "=". If the comparison results in "equal", the #REF! error must vanish. Also try the directly entered reference to verify the validity!

PS INDIRECT() and SUBSTITUTE() should work without changes for decades now, but I cannot test.

Hi Lupp.
Yeah there was a couple of user error fails on my part....unequally formatted reference files meant that the cell reference (O42) was empty in many.

So in essence the entire way im doing this looks shaky. It needs only one Assessor to move a column or row and the overview file sees no data and hence #REF!.
I really like INDIRECT used with SUBSTITUE though (and your level of sarcasm, its hilarious)....hoping I can further use it in this formula......
Is there a way of referencing a cell from a text string, then next to the target cell?
The cell Im looking for in all of the Agent files (data files), in other words the previously mentioned O42 is titled (i.e. cell to the left) "Total Average Score in %", this way if the cell was moved I would still attain the result.

Any help as always is appreciated.

...update.
So Ive defined the name for the "cell agent_score" in the file were the score exists.
Created it globally, and I cant accesss the data from my Overview file....
Ive tested the names range in the document and it works fine (agent_score*4 returns correct result) but not when accessing from the other overview file.

='file:///w:/qa/call quality/quality agent template.ods'#$'calls sheet 1'.#agent_score