Sorry for the long entry, I just want to provide all info.

I have created a vlookup formula with a dynamic table_array. It basically looks for a value (the number assigned to a piece of equipment) in cell C8 in a series of sheets (each named after the students who are checking out gear). If the formula doesn't find the number, it looks in the next sheet and the next until it finds it. It then returns the value I'm looking for--in this case, the name of a student who has checked out a piece of equipment, located in cell D8--to an inventory sheet named after the type of gear being checked out (Sleeping Bags, for example).

Here is the formula I would put on sheet "Sleeping Bags" in cell C3 when cell B3 = the sleeping bag number being looked up:

{=VLOOKUP($B3,INDIRECT("'"&INDEX(StudentList,(MATC H(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$ B3)0),0)))&"'!$c$8:d$8"),2,FALSE)}

"StudentList" referes to a named range of students on sheet "Students". Each student has a sheet name that matches his or her name on the StudentList.

So far it works great. For example, on sheet "Smith, Johnny", I enter that he checked out Sleeping Bag #3 in C8, and then on the sheet titled "Sleeping Bags", the formula puts his name next to sleeping bag #3. Perfect.

The problem is that I have 11 inventories and 32 students. Too many sheets to keep it neat. I want to separate the inventory sheets from the student checkout sheets and have the vlookup search the new "Checkout" workbook. So now the formula becomes:

{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)0),0)))& "'!$c$8:d$8"),2,FALSE)}

When shft+cntrl+enter the formula, I get #n/a.

Any ideas?

Here is a simplified workbook example. It only has two students and two inventories. Change data on the student sheets in cells c8 and c9, and e8 and e9 to see how it works. Again, my goal is to separate the student sheets and the inventory sheets into two separate workbooks.

On Wednesday, 7 August 2013 01:54:08 UTC+10, sporenta wrote:
Sorry for the long entry, I just want to provide all info.



I have created a vlookup formula with a dynamic table_array. It

basically looks for a value (the number assigned to a piece of

equipment) in cell C8 in a series of sheets (each named after the

students who are checking out gear). If the formula doesn't find the

number, it looks in the next sheet and the next until it finds it. It

then returns the value I'm looking for--in this case, the name of a

student who has checked out a piece of equipment, located in cell D8--to

an inventory sheet named after the type of gear being checked out

(Sleeping Bags, for example).



Here is the formula I would put on sheet "Sleeping Bags" in cell C3 when

cell B3 = the sleeping bag number being looked up:



{=VLOOKUP($B3,INDIRECT("'"&INDEX(StudentList,(MATC H(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$ B3)0),0)))&"'!$c$8:d$8"),2,FALSE)}



"StudentList" referes to a named range of students on sheet "Students".

Each student has a sheet name that matches his or her name on the

StudentList.



So far it works great. For example, on sheet "Smith, Johnny", I enter

that he checked out Sleeping Bag #3 in C8, and then on the sheet titled

"Sleeping Bags", the formula puts his name next to sleeping bag #3.

Perfect.



The problem is that I have 11 inventories and 32 students. Too many

sheets to keep it neat. I want to separate the inventory sheets from the

student checkout sheets and have the vlookup search the new "Checkout"

workbook. So now the formula becomes:



{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)0),0)))& "'!$c$8:d$8"),2,FALSE)}



When shft+cntrl+enter the formula, I get #n/a.



Any ideas?





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+







--

sporenta

I didn't understand your formula. What does the -- signify? It looks like you're trying to decrement a variable, but as far as I know standard Excel doesn't allow for this. If there is such an operator, can you tell me where it's documented?

Howard

Any ideas?


sporenta



I didn't understand your formula. What does the -- signify? It looks like you're trying to decrement a variable, but as far as I know standard Excel doesn't allow for this. If there is such an operator, can you tell me where it's documented?


Howard

Hi Howard,

(MATCH(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$ B3)0),0))

I believe this returns TRUE or FALSE if COUNTIF 0 and the -- converts TRUE and FALSE to 1 and 0.

The 1 or 0 are then later in the formula to do some magic I really don't understand.

I got the explanation from Peo Sjoblom in 2006 and have really never been fully capable of understanding the entire formula. But it has been useful.

Regards,
Howard (L. Howard responding to Howard)

Hi,

Am Tue, 6 Aug 2013 16:54:08 +0100 schrieb sporenta:

{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)0),0)))& "'!$c$8:d$8"),2,FALSE)}

When shft+cntrl+enter the formula, I get #n/a.

INDIRECT requires a open workbook

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks for your replies. See the following site for an explanation of the formula:

http://www.myonlinetraininghub.com/e...#comment-17217

Sorry I didn't include that originally.

I have solved the problem, though. It wasn't working whether or not the other workbook was open. The trouble was with the StudentList. The formula didn't know where to look, and I thought that putting the workbook location in the formula would fix it, but it wasn't enough. I had to put the workbook location in front of each sheet name on the list. So, my original SheetList looked like:

Doe, Jane
Smith, Johnny

...with a sheet named after each student. Now, it looks like:

drive:folder:[workbook.xlsx]Doe, Jane
drive:folder:[workbook.xlsx]Smith, Johnny.

Now it works perfectly!

I got it to work by putting the full file location in front of each student on the StudentList. So instead of:

Doe, Jane
Smith, Johnny

...it reads:

file:folder:[workbook]Doe, Jane
file:folder:[workbook]Smith, Johnny

Works fine now. Thanks for the ideas!