Need to consolidate Daily Data into a separate workbook

[Excelearner3]

I am working on a sheet through which I can consolidate the daily data for the entire year (2016) into a one single sheet/separate workbook.

The working required here seems simple but the problem is I have a very large data set (date-wise) for the entire year in separate workbooks and from there I have to consolidate it one sheet.

I have attached the working sheet in the post for viewing, the data may not appear correctly by you may get the general idea of the formula I am using to get the data from each day's sheet.

The daily data is saved in separate excel files with file name of that day's respective date for example the data for 01-01-2016 will be saved under a file name of 01-01-2016.xls and the formula I am using to fetch the data in the consolidation sheet is

Please Login or Register  to view this content.

The problem here is, I have to manually edit each cell to change the date in the formula in order to fetch the data from that date's respective workbook. I am wondering if there is someway I can automate this process or use a different approach rather than manually editing the date in each cell.

Here is another external download link for the my excel sheet.

Please Login or Register  to view this content.

[FlameRetired]

I put this in R7 of the upload.

=TEXT(B7,"dd-mm-yyyy")&".xls"

It output 04-01-2016.xls

Is this something you can work with?

[FlameRetired]

I put this in R7 of the upload.

=TEXT(B7,"dd-mm-yyyy")&".xls"

It output 04-01-2016.xls

Is this something you can work with?

[Excelearner3]

I put this in R7 of the upload.

=TEXT(B7,"dd-mm-yyyy")&".xls"

It output 04-01-2016.xls

Is this something you can work with?

Thanks for the input, but its not working.

[FlameRetired]

Can you be more specific? In what way is it not working? Did it output what I reported? What output ... specific examples ... did you want? Are you having to manually type the rest of the output and is it variable?

The problem here is, I have to manually edit each cell to change the date in the formula in order to fetch the data from that date's respective workbook. I am wondering if there is someway I can automate this process or use a different approach rather than manually editing the date in each cell.

That is all my formula does. I assumed it would be embedded in what ever concatenating formula you were already using.

[Excelearner3]

Can you be more specific? In what way is it not working? Did it output what I reported? What output ... specific examples ... did you want? Are you having to manually type the rest of the output and is it variable?

That is all my formula does. I assumed it would be embedded in what ever concatenating formula you were already using.

The formula I am using to fetch the value is;

'E:\Desktop Files\Operations Statement\MIS\2016\Jan''16\[19-01-2016.xls]Sheet1'!$F$9

The data this formula is fetching is from a file created on 19-01-2016 (having the same file name) the next row will need data from the next day i.e. 20-01-2016. So I will have to change the part of the formula colored red to 20-01-2016.

The output of your given formula works and gives me the name of the file but how can I incorporate that piece of text inbetween a file path?

So currently I am resorting to manually change the date for each new row, I am looking for a way that I don't have to change the date/file name manually for each row.

[FlameRetired]

Try this:

Put the file path in a helper cell ... say C1. Reference that in the formula.

Then use this formula to append to the path the rest of the string including the "month''two digit year\" preceding the [filename.xls] and the Sheet name and the cell address.

Please Login or Register  to view this content.

[Excelearner3]

Try this:

Put the file path in a helper cell ... say C1. Reference that in the formula.

Then use this formula to append to the path the rest of the string including the "month''two digit year\" preceding the [filename.xls] and the Sheet name and the cell address.

Please Login or Register  to view this content.

Ok this is working fine and is giving the right dates for all rows, but now the problem here is, its not fetching the value (numerical) from the other sheet, when I paste this formula in a row it just displays the path name like a text value.

For example that formula you gave displays the following text instead of fetching the value from the other respective worksheet.

Please Login or Register  to view this content.

[FlameRetired]

Oh! I thought you had that part solved and just needed a string build.

That's a different problem, and I don't have a way to test a solution at my end.

I'll see if I can get us some help.

[Kyle123]

Try wrapping your formula in the indirect function

[Excelearner3]

Oh! I thought you had that part solved and just needed a string build.

That's a different problem, and I don't have a way to test a solution at my end.

I'll see if I can get us some help.

Yeah thats the problem I am facing.

Try wrapping your formula in the indirect function

Can you please give some example? Or link me to some website which explains it?

[Kyle123]

Something like

Please Login or Register  to view this content.

[Excelearner3]

Something like

Please Login or Register  to view this content.

I went with the idea you quoted, and used the INDIRECT function but now the working sheet shows a #REF! error until I open that respective date's sheet.

For example, I put this INDIRECT function in the 04-01-2016 row so the sheet will show me the #REF! error till I open that days sheet, then it displays the value I want.

Now its not possible for me to open each day's file everytime I decide to work with this current consolidated sheet.

[Kyle123]

Ok, in that case you'll either need VBA, or you'll need to download the MOREFUNC.XLL and install it, it provides an indirect function that will work with external workbooks. There is no other way of doing it

[ciwakumar]

Refer to the below site - some thing similar to your requirement
http://www.excel-ticker.com/create-a...iles-in-excel/

[Kyle123]

@ciwakumar, did you read the link you posted? It won't work, the links are external

[Excelearner3]

@ciwakumar, thanks for the input but I link mentions the same thing

That’s all; the configuration value in D3 then easily permits to choose the week. However, for each chosen value, the corresponding file may be opened. Otherwise the formula will return the error value #REF!. Of course, the formula can be extended by an IF-Statement and

I am also trying to get rid of that #REF! error, without having to each sheet one by one.

[Kyle123]

Did you read my post? You can't without VBA or Morefunc, it's not possible

[FlameRetired]

@ Kyle123,

I'll tell you "in-thread" since the system won't allow me to add rep. .

Thank you for answering the Cavalry call.

Dave