Get a value from an other workbook with a path changing according a cell value
Hi,
I'm trying to get data from others workbooks.
I have a workbook (summary) with a list of name (12MIRC026, 11MIRC032 for example)
and a directory with a folder named 12MIRC026 containing a workbook (data) named
12MIRC026.xlsm with of course the same thing concerning all the name of
the list.
I want to get a value from 12MIRC026.xlsm'!'!Validation!R5C3' and put it in my
summary workbook in order to fill my summary workbook (cell in red).
So I need to create a path changing according the list, so the path is
modified by a cell content(C7 for example).
Can you give me a hand
Many many Thanks
| Attachment | Size |
|---|---|
| Summary sheet.xlsx | 39.57 KB |
| 12MIRC026.xlsm | 355.22 KB |
the only way I can think of
the only way I can think of off the top of my head is to create all the relevant linking formulae for the various sheets, then use a vlookup to pick the appropriate one based on the value in your "Sheet" cell
However - a big caveat is that the reason you are having to resort to these nasty solutions is that your data is structured badly. You could hugely benefit from putting all your data in one place, and then creating your customised reports via pivot tables.
We can help with this.
Why keep different workbook
Thanks Nick for your answer.
The first option is interesting, I'll try to do something on this way.
Concerning the second option, the problem is different people are working on the same project and they haven't accessed to the server during the day. So It's good for them to pick up the data source worksheet on the field and then merge the excel sheet at the end of the day. And keeping the summary file on the office, I can have a look after the data if they are news and which data are missing.That the reason why I have separated the differents worksheets.
Thanks again for your help.