VLOOKUP with INDIRECT and Multiple Workbooks

[Dagoom]

Hey Everyone,

I am curious if anyone can help me out with a problem.

I want a formula to use a value in a cell, use that value to go to a different file, use the value for the sheet name (a single digit number), then use the same value with different math to vlookup the project number, then get the value beside it.

So:

Value in Cell J6 in Workbook A = Client Number and Job Number.

Sheet Number in Workbook B = Client Number

Value I want the vlookup to return is beside the job number.

What I have so far is:

=(INDIRECT("'[Test.xlsm]"&(ROUNDDOWN((J6/100),0))&"'!NeedVlookupCommandHere"))

I'm not quite sure where the Vlookup would go, either before or in the middle of the formula. :/


The Value of the Client Number is the Rounded down J6/100

The Value of the Job Number is J6-100

I know this seems all out of joint, however I have no idea how to continue.

EDIT:

Just realized I didn't put a range for the vlookup, I want $A$5:$B$10000 if possible.

For the record, if I put A6 into the area where I want the Vlookup Command, I get a value from the desired sheet, however I have no idea how to match the value of J6-100 from that sheet, then pull the result beside it.

Maybe this is on the right path?
=VLOOKUP(((INDIRECT("'[Test.xlsm]"&((ROUNDDOWN((J6/100),0))&"'!"))(J6))-100)$A$5:$B$1000,2,0),””)

[FDibbins]

Thanks for the title change

And just FYI, INDIRECT() only works on open workbooks

[Dagoom]

I'm aware that it only works in opened workbooks, I have a macro that re-validates the information upon editing of the cells in question, so it pulls the correct data. I appreciate the warning though.

Also, thanks for telling me about the forum rules, I will make sure I follow them carefully.

[Dagoom]

Anyone have any advice?

Thanks in advance.

[Dagoom]

For anyone who is interested, this worked for me.

IFERROR(VLOOKUP(J5,INDIRECT("'[Test.xlsm]"&LEFT(J6,1)&"'!$A:$B"),2,0),"")

I opted for the left command as it's shorter however you COULD use ROUNDDOWN in it's place.