Excel sheet cell referencing.

[SBrooky]

I have a spreadsheet which when clicking a button prompts you to select a folder. It then lists all the folder within that folder in column AA (5 onwards)

Each one of these folders contain a file called 'Monthly engagement measure.xlsm'.

In the B column (5 onwards) i want it to open the said spreadsheet for each folder location adjactent to the column its on.

Its laid out like

A B C.. AA
5 Name1 *MY PROBLEM* C:\Folder\Name1\
6 Name2 *MY PROBLEM* C:\Folder\Name2\

So in the B column it needs to reference the folder specified in AA\Monthly engagement measure.xlsm.


Tried the google to no avail! Can anyone sped some light on this please?

*EDIT* I realise the column names dont line up..not sure how i can do that either haha.
A column lines up with the Names
B Column with My Problem
C onwards is other stuff not related
AA is the folder locations

[BrianMH]

Are you wanting to open more than one workbook at the same time? Even if they are in different paths you cannot open 2 workbooks with the same name.

If you need to open workbook set a workbook variable and use workbooks.open

[VBA]
dim wb as workbook
set wb = workbooks.open("C:\Folder\Name1\Monthly engagement measure.xlsm"
[/VBA]

[SBrooky]

Thanks for the reply. Not sure I explained my problem correctly.

AA lists the folders within a folder selected by a prompt so:
They press a button.
Select a folder: AA then is a list of all the folders inside
B5 needs to be a reference to a cell in AA5\'mem.xlsm'!D5
B6 needs to be a reference to a cell in AA6\'mem.xlsm'!D5
etc.etc.

My sub does it in this order:
Get folder locations and list them
open the file in the first folder
close file from first folder
open file from next folder
close file from next folder
etc. in a loop.

I want B column just to be references so that when the files are opened the spreadsheet will update from #Ref to the value.

If you want I can post the code but I just want B to be a reference to AA\mem.xlsm!D5

[BrianMH]

Yes please post your workbook.

[SBrooky]

Ive attached the workbook. Ive done a little formatting so its the D column which needs to be working. My current lame attempt at a formula is in there.

[BrianMH]

Ok. Going to excel help for indirect it lists the following:

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

So the workbook has to remain open for the formula to work. You may be better off filling in the values with your vba as it opens and closes the workbooks instead of using a formula or you will need to update the formula to point to the cell with out using indirect and this would also be done using the VBA.

[SBrooky]

Ahh ok thanks alot for your help =)