Formula with filename reference

[MyKroFt]

I am trying to use a cell ref with a weekly changing filename but I keep getting a error 502

here is what I have

Cell B1 = Daily_Box_Office_Report_08-14-09_thru_08-20-09.ods

Cell with the formula needs to ref a static path to the file location/filename/sheetname/cell #

Am trying to use: =INDIRECT("//pbx-rena/Rena%20Files/Daily%20Inventory/"&B1&".Weekly.F5")


Have never tried this before - but have been useing OO for almost 2 years now - 1st time I am stuck and cant figure this out.

Help/suggestions please

Thanks
Myk

[squenson]

Welcome to this forum, MyKroFt!

I don't think it is possible to reference a cell from another document with a formula. My explanation is that an 'external' cell requires special handling and its absolute value must be stored in the current document so you are not obliged to have the external document always available. You cannot achieve this with the INDIRECT() function.

[Villeroy]

=DDE("soffice" ; A1 ; C1)
where A1 should be a file name in the system's notation. I think you have to map your network path to a drive letter
A1: "C:\pbx-rena\Rena Files \Daily Inventory\"&B1
B1: the file name
C1: A cell's address string or an equivalent name of a cell.

[Tutorial] External Links In Calc
The INDIRECT("file://path/name'#Sheet.X99") may work again in future versions. They messed up the new implementation.

[MyKroFt]

=DDE("soffice" ; A1 ; C1)
where A1 should be a file name in the system's notation. I think you have to map your network path to a drive letter
A1: "C:\pbx-rena\Rena Files \Daily Inventory\"&B1
B1: the file name
C1: A cell's address string or an equivalent name of a cell.

[Tutorial] External Links In Calc
The INDIRECT("file://path/name'#Sheet.X99") may work again in future versions. They messed up the new implementation.

Ok, Im not sure how to reference C1 then (#Weekly.F5), it needs to be a specific sheet & Cell comming from the B1 filename

This weekly filename changes each week with a date range etc, and I want to have my weekly summary sheet pull the data by just having to change the current filename and have it recalc vs hand inputting all the data.

[MyKroFt]

when do I a past special of one of the cells from the other external spreadsheet i get the following:

=DDE("soffice";"\\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods";"Weekly.F5")

what I need to do is split up and sub the 08-14-09_thru_09-20-09 part of the filename depending on the date range

I tried making B1 = \\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods

and C1 = {=DDE("soffice"; B1 ; "Weekly.F5")}

but i just get #N/A for the value of C1

any more suggestions?

Thanks
Myk

[Villeroy]

Ok, Im not sure how to reference C1 then (#Weekly.F5),

The ordinary address string should work: "Weekly.F5"

"#Weekly.F5" denotes a jump mark in an URL.
#Weekly.F5 jumps to Weekly.F5 in this file.
'file://path/doc.ods'#Weekly.F5 in another file.
'file://path/doc.odt'#Weekly jumps to bookmark "Weekly" in a Writer document.
http://user.services.openoffice.org/en/ ... 655#p98424 jumps to bookmark #p98424 in this thread.

I tried making B1 = \\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods

Try if you can do trick with a drive-letter path. If so, you need to map the Windows specific network path to a regular drive letter. OOo supports half a dozend of operating systems. None of them knows paths starting with "\\", except Windows. That particular operatings system differs from all the others in too many ways.

Edit: Copy the cell Weekly.F5 from the opened file and paste special with option "Link" in the other file and see what Calc produces.

[MyKroFt]

I will map a drive letter and try

but

=DDE("soffice";"\\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods";"Weekly.F5") <---- came from paste special > link

works and pulls the value from the other spreadsheet

using R:\Daily Inventory\Daily_Inventory_08-14-09_thru_08-14-09.ods comes up with #N/A also

so

=DDE("soffice";B1;"Weekly.F5")

must not be doing the DDE call correctly?

[Villeroy]

Try a copy&paste-special between both files and check option "Link".

[MyKroFt]

Try a copy&paste-special between both files and check option "Link".

that is where:

=DDE("soffice";"\\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods";"Weekly.F5")

comes from, and it works, but I have several hundred links that would have to be manually changed if I can not use a cell for the filename

[Villeroy]

Try a copy&paste-special between both files and check option "Link".

that is where:

=DDE("soffice";"\\Pbx-rena\rena files\Daily Inventory\Daily_Inventoy_08-14-09_thru_08-20-09.ods";"Weekly.F5")

comes from, and it works, but I have several hundred links that would have to be manually changed if I can not use a cell for the filename

I thought that UNC notation would not work with DDE. Cretainly you should avoid quotes and dots in the file names. Spaces should be OK with DDE. Escaping spaces with %20 belongs to the URL syntax with "protocol:server.domain/path%20name".

[acknak]

I couldn't get the DDE formula to work when referencing another cell, but it does work if you use a named constant in place of the file name. I could modify the path in the constant and get all the DDE formulas updated to the new location.

Will that work for you?

[MyKroFt]

I couldn't get the DDE formula to work when referencing another cell, but it does work if you use a named constant in place of the file name. I could modify the path in the constant and get all the DDE formulas updated to the new location.

Will that work for you?

That is what I am trying to do and cant get a named constant to work, but selecting a cell from the other sheet, and then using paste special > link, gets me what I have pasted above and that works for me, but every cell then is hard coded with a specific filename, and I need to change that filename weekly in hundreds of cells

[acknak]

Maybe it doesn't work on Windows, but it's ok here. Changing the file path defined as a range name causes all the DDE formulas to refresh with data from the new path.

Just in case, here's a sample that's working for me: Just change the DDEFile name to point to "sheet_a.ods" on your system and it should work. Change it to point to "sheet_b" and the values should change.

[MyKroFt]

Maybe it doesn't work on Windows, but it's ok here. Changing the file path defined as a range name causes all the DDE formulas to refresh with data from the new path.

Just in case, here's a sample that's working for me: Just change the DDEFile name to point to "sheet_a.ods" on your system and it should work. Change it to point to "sheet_b" and the values should change.

ok, I see how it works now and got it working the insert > names > define was hanging up solution....

Thank you much guys for the help/suggestions - you have now cut my week summary work in 1/2

Thanks again

Myk

[MyKroFt]

Ok, here is what I did

In my weekely spreadsheet I inserted 3 blank rows at the top for the info
A1 = Inventory Filename
B1 = BoxOffice Filename

Insert > Names > Define > DDEINV > "\\Pbx-rena\rena files\Daily Inventory\Daily Inventory 08-14-09 thru 08-20-09.ods"
Insert > Names > Define > DDEBO > "\\Pbx-rena\rena files\Daily Box Office Report\Daily Box Office Report 08-14-09 thru 08-20-09.ods"

A2 = Insert > Names > Insert > DDEINV (Puts =DDEINV in cell for formula)
B2 = Insert > Names > Insert > DDEBO (Puts =DDEBO in cell for formula)

A2 and B2 show the define's set above in those cells

C9 = =DDE("soffice";DDEINV;"$Weekly.$F$5";0)

Weekly is Sheet8 renamed and need value of F5

but I am still getting #N/A in C9 instead of a numeric value of 119


any other ideas?

Myk

[acknak]

I don't see any problem with what you've described--I tried changing the example files to something more like your example, but it still works for me.

Does your sheet work at all? It's usually a good strategy to start with something simple that works, and then try to modify it incrementally, making sure that each change doesn't cause any problems.

E.g., try the DDE formula with the actual path string instead of the range name. If that doesn't work, then you'll know there's something wrong with the path or the formula, or the . If that does work, then you should be able to copy the working path value to the range name definition and change the formula to use the named constant.

If that still isn't working, I'm not sure what else to suggest.

[MyKroFt]

Yes it does work, If I go to the external sheet and copy and cell, come back to my weekly summary sheet and paste special > link - it works, i just cant get it to work when trying to use a cell to hold the filename...

[MyKroFt]

since these files are coming over a remote VPN off a linux box, I think I am just gonna make a sym link called current.ods linked to that weeks filename, and just hard code them into my weekly summary sheet. Which is a shame, cause it was easy to do in Excel

[acknak]

If this is something that Excel supports but is not working in Calc, then it would be appropriate to file a request for enhancement. I have no idea what priority such a request might be assigned, or when something might happen, but at least the issue would be "on the radar".

If the range name workaround works on Linux and not on Windows, then I'd say that sounds like a pure bug, and would more likely to be addressed sooner rather than later.

[Tutorial] Reporting bugs or suggestions