Using cell contents in an equation

[GazNicki]

Hi,

Sorry about the wording of the title, I don't know how best to word this.

I have a large number of Excel spreadsheets in subfolders that I wish to obtain information from. Currently, I need to type in the path and filename into each cell. There is a spreadsheet for every single day, and they are all individual.

I do not have control over how these sheets are setup, so I have to use what I have.

Here is the formula in cell E8 which works perfectly:

='G:\ORDER CONTROLS\SAINSBURYS ORDER CONTROL\2018\06 June 2018\WE 02 JUNE 2018\[Sun 27 MAY 2018 JS.xlsx]PALLET SHEET CALCULATOR'!$A$16*$D$8

What I would like yo do is this:

In cell E2 I would like to have the directory name (G:\ORDER CONTROLS\SAINSBURYS ORDER CONTROL\2018\06 June 2018\WE 02 JUNE 2018\)
In cell E3 I would like to have the Day (Sun)
In cell E4 I would like to have the Date (27 May 2018)
In cell E8 I would like to be able to use the contents of E2:E4 in the equation, but I don't know if this is possible.

This is how I envisioned it@

="E2"&"E3"& "[E4"&" JS.xlsx]"PALLET SHEET CALCULATOR'!$A$16*$D$8

Is this even possible? I would like to do this as then I can change the directory information with ease for the rest of the year and for future years with little input.

 

[Aladin Akyurek]

see: https://www.mrexcel.com/forum/excel...hpoint-aka-sharepoint.html?highlight=INDIRECT

 

[GazNicki]

Forgive my inability, but that thread doesn't seem to help me.

My workbooks will be closed, hence the braces around my equations.

My biggest issue is that when I link to cell E4, I am given the numerical number for the date and not the text. I can't get around that.

 

[Aladin Akyurek]

On the contrary, it does. It's something like INDIRECT that you need. Since INDIRECT won't work, you need PULL.

Try to examine the PULL usage in some examples, you'll get the gist.

 

[GazNicki]

I've read some of the examples, and currently can't see the fit.

Right now, the only issue I have is that if I link to Cell E4 (27 May 2018) with "=E4" - the result is 43247

This makes including it in the sum impossible. Is it possible to include the text value rather than the numerical value.