Ask Your Question
1

Concatenating text from 3 cells into a usable formula

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

pbrooks89924 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?

Thanks,

PBrooks

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

Comments

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 359 times

Last updated: Jan 02 '17