Ask Your Question

Concatenating text from 3 cells into a usable formula [closed]

asked 2017-01-01 22:47:44 +0100

pbrooks89924 gravatar image

updated 2020-08-09 20:03:45 +0100

Alex Kemp gravatar image

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?



edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-09 20:04:27.844348

1 Answer

Sort by » oldest newest most voted

answered 2017-01-02 02:03:35 +0100

doug gravatar image

updated 2017-01-02 02:04:05 +0100

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.

edit flag offensive delete link more


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!

pbrooks89924 gravatar imagepbrooks89924 ( 2017-01-03 01:09:22 +0100 )edit

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

doug gravatar imagedoug ( 2017-01-07 23:44:18 +0100 )edit

Question Tools

1 follower


Asked: 2017-01-01 22:47:44 +0100

Seen: 635 times

Last updated: Jan 02 '17