Concatenating text from 3 cells into a usable formula

[pbrooks89924]

Hi All!!,

I want to concatenate text in a couple of cells to into a formula to retrieve information from another workbook.

I am able to retrieve the information using a direct formula:

=‘file:///C:/Users/PB/Documents/Timesheets/517.TimeKeeping.xlsx’#‘Job Summary’.B5

but I want to have a concatenating formula, so id have:

a1:file:///C:/Users/PB/Documents/Timesheets/
b1:517.TimeKeeping
c1:.xlsx’#‘Job Summary’.B5
d1: concetanate a1, b1 and c1 to create a formula to retrieve the data from the other workbook.

I can concetanate using & but this just creates a long text string - how do I turn this into a formula?

Thanks,

PBrooks

[doug]

The worksheet reference you are using converts easily to a DDE() spreadsheet function, which can be used in a CONCATENATE() statement. Thus, your cell reference becomes:

=DDE("soffice"; "file:///C:/Users/PB/Documents/Timesheets/517.TimeKeeping.xlsx"; "Job Summary.B5")

And then the concatenation function becomes:

=CONCATENATE(DDE("soffice"; "file:///path/filename.ods"; "Sheet1.A1"), A1)

Or you can perform any other function on the result, such as addition

=DDE("soffice"; "file:///path/filename.ods"; "Sheet1.A1") + A1

And you can force treatment of the data as a number via conversion function such as VALUE().

The & method of concatenation noted in your question does not work for me using the file reference syntax in the question.

[pbrooks89924]

This is very close to what im trying to achieve!! What I want is the 2nd parameter of the DDE function to be determined from the text in a local cell (for example A1), so that this text can be manipulated using concatenate to change the data its referring to.

So in this example i would have in A1:“file:///C:/Users/PB/Documents/Timesheets/517.TimeKeeping.xlsx”

And then the DDE would be: =DDE(“soffice”; A1; “Job Summary.B5”)

But this doesnt work!

[doug]

It works if you enclose A1 in its own CONCATENATE function. Thus, your last statement would be =DDE("soffice"; CONCATENATE(A1); "Job Summary.B5")