indirect....scan through workbook and find name

[jw01]

Hello

im trying to compile some data together for a contest for sales employee revenue.

each week i get from 33 branches, all 200 sales ppl data (the worksheet are named after their branch i.e. Washington-Philadelphia); is there are seperate worksheets for individual worksheets, with D1 holding their names.

now can i possible get a indirect function to scan for the name and then do find data for me in certain columns/rows?

so basically, just need some help or if there is another method for a formula to scan thru the workbook, use the appropriate name and then get the data i want.

in my "master sheet" i have the sales ppl name starting in cell C9, with the branch name aka branch workbook name in B9.

thx you so much for the input!

[jw01]

hello

this is the formula im trying to use but im getting a lilttle messed up on my method:

=index(&$E$3&$D$9&$E$9&E4,I$4,I$5,1)

where
E3 = 'J:\Sales Analyst\SWAT\Archieve 2011\
D9 = Edmonton-Winnipeg
E9 = Peter Vepstas
E4 = A1:X63 (array of data)

I4 & I5 = row and column reference.

basically, this is what formula im trying to achieve:
='J:\Sales Analyst\SWAT\Archieve 2011\P3W1\[Edmonton-Winnipeg.xls]Peter Vepstas'!$Q$21

i have over 200 sales ppl, so itd b so very tedious to go rough each file on by one and reference it, so im trying to use that index, but if someone can pls help me, thxs!

[bentleybob]

Seems like the following would work (except you are missing the \P3W1\ part of your file path in E3:

=INDIRECT(E3&"["&D9&".xls]"&E9&"'!"&I4&I5)

This assumes I4 and I5 are the column letter and row number, respectively. Given what you might have, you may need to combine INDEX and INDIRECT, but it's hard to tell from your description.

[jw01]

hello bob

i tried to incorporate your formula, and added the "P3W1"

=INDIRECT(E3&I8&"["&D9&".xls]"&E9&"'!"&I4&I5)

so:
I8 =P3W1\

but for some reason, im getting a ref. error.

basically, i wanted to use index as i wanted the value to show, even if the workbook is closed. also, do i need to define a range in other worksheet?
E4 = A1:X63 (array of data)

thxs for your time!

i also tried it with index, but got a value error:

=INDEX(E3&I8&"["&D9&".xls]"&E9&"'!"&E4&"",I4,I5,1)

again, i tried to define a range,
where E4 = A1:X63

[bentleybob]

Either you need the trailing "\" after the reference to I8:

=INDIRECT(E3&I8&"\["&D9&".xls]"&E9&"'!"&I4&I5)

or maybe I4 is not Q (or $Q) but the column number?

[jw01]

hello bob

sorry i used Q as a reference, it should really be this

=INDEX('J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Edmonton-Winnipeg.xls]Peter Vepstas'!$A$1:$X$63,I4,I5,1)

...the range is A1:X63

also,
I4 and I8 are rows and column reference, for when i was using the index formula.

im still getting the REF error.

[jw01]

i have included a sample workbook; not that it may help as the file is located on our serve, but maybe im not saying it correctly?

also, i have highlighted in red what im looking to achieve, w/o having to go in all 33 workbooks. thxs for your time again!

i even tried this, maybe you can have some of your thought to this formula:

=INDEX("'J:\Sales Analyst\SWAT\Archieve 2011\"&I8&"\["&D9&".xls]"&E9&"'!"&E4&"",I4,I5,1)

[bentleybob]

The formula evaluates correctly. Could it be that Archive is spelled incorrectly?

[jw01]

hey bob

yes, it seems incorrect, but that is how it's spelt in the folder.

i even tried to open the file, but not sure whats taking place to make it wrong?

atleast the index portion with the references :S

i just dont get why im getting the value error wit this formual:
=INDEX("'J:\Sales Analyst\SWAT\Archieve 2011\"&I8&"\["&D9&".xls]"&E9&"'!"&E4&"",I4,I5,1)

[bentleybob]

Try both the INDIRECT and INDEX formulas with no cell references. In other words, type everything you wouldget by evaluating the references. If that works, it's in our formulations. If it doesn't, that points to a different problem.

[bentleybob]

Sorry to report that INDIRECT will give the #REF error unless the referenced workbook is open.