Return value of a consistent cell from many excel worksheets

I’m constructing a summary document that returns the value of a consistent cell from many excel worksheets located in a shared area (Sharepoint 2016 team site)

I don’t want to manually reference the cell from each of the source documents (by selecting "=" and then selecting the cell in the source file). There are several hundred source documents - although all are based on the same template so the cell I reference is consistent.

I have the address of each of the source documents (as a URL) in an excel list.

How can I construct the formula to fetch the value of that consistent cell into a list within the summary document?

Many many thanks!

StKi1da

Originally Posted by Glenn Kennedy

I assume that you will have a list of workbook names as your starting point. INDIRECT is the best way of picking up a workbook name and returning tha cell value... but for the fact that it doesn't work on closed sheets.


Try this. Download all 3 sheets to the same location. Open destination.xlsm (enable macros). Adjust the path in column B to suit your PC.

Happy to explain or modify (if I can - I'm useless with VBA).

Ok thanks. Yes i have a list of the documents and as you point out, they cannot be open. I'll download your files shortly and see where that takes me. Tx

Your cells work in themselves. My issue seems to be in specifying the path to the "real" documents.

Forgive my ignorance - should the INDIRECTEX function work on any sheet - or is it calling some specific code in the worksheet you gave me?

The Destination.xlsm sits in a master folder, with each source document I want to pull the cell value from sitting in sub-folders within that master folder.

I modified the code as follows:

=INDIRECTEX("'SB252263/201780/["&A2&".xlsx]Metrics and QC!$N$29") with the document name sitting in A2

This does not appear to be returning the value from N29. I'm clearly doing something basic wrong - please correct me!

Thanks for your help!

Thanks. I think my issue is around the path - so I'll gone back to the start!

As the files are in Sharepoint I seem to have to use a url as the full path and not a C:\ path. This might be causing my issues.

So this is the formula I'm trying to use. It throws "there is a problem with this formula" and suggests adding a '. When I hit OK, the cursur is located between the : and the first / of the https:// . Anythung I need to do so it recognises the url as the path?

=IndirectEx(“’https://team.bhj.com/sites/Index/Team Documents/SB104567/201780/[“&A2&”.xlsx]Metrics and QC’!$N$29”)

Thanks for your patience.

I re-typed just to be sure. This cured this issue - but now I simply get a 0 returned (when the cell is not 0). Very frustrating.

I've taken a slightly different approach which you might be able to help me complete.

I manually added the cell reference for the first source doc to the summary sheet (ie = and then navigate to the source and select the cell where the value resides). I copied down this cell and manually edited the reference for the next source location. This worked when the source document was closed.

Since I have a list of the "source" documents and their paths. I thought I could concatenate and "create" the cell reference.

So this looks like

="'"&[@Column1]&[@Path]&"/["&[@Name]&"]Metrics and QC'!$N$29"

This creates a cell with the following:

'https://team.bhj.com/sites/Index/Team Documents/SB240563/200862/[Index _V4_200862_UK.xlsx]Metrics and QC'!$N$29

This however is the text returned to the cell (it's missing the = ) and I can't seem to add this.

If I could use this (modified if needed) to pull the value of the cell referenced into the sheet - this would do what I need.

Obviously happy to pursue your original plan - but I'm struggling to troubleshoot what to me looks like it should be working!

Happy to entertain suggestions! Thanks for helping with this.