Multiple Lookups...tried other threads

[ammdumas]

Asked for some help yesterday concerning muliple VLOOKUPs and gosh darn it if I didn;t try.

I tried just_jons test http://216.92.17.166/board2/viewtopic.php?t=73228

and the variation from Aladin Akyurek.

I suspect the problem had something to do with the 'Bigstr' portion of the formula but in any case, no matter how I tried I ended up with #N/A.

I'll explain in detail what I need. The sample table below is the data source on sheet1 (Columns 1,2,6 and 8 respectively)

Trans# Review# Name Status
1 1000 Bill Approve
2 1111 Ed Concur
3 1111 Joe Approve
4 1000 Steve Inform
5 1000 Phil Inform

On Sheet3 there is a transmittal form that has 12 possible spaces for names to be added in. C9-C18 would house the names and E9-E18 would house the status of each person. All other fields on the form have VLOOKUP's which work fine (based on Trans# where other data in columns for similar Review#s are identical, so first one found is fine)

I would like to autopopulate those fields C9-C18 and E9-E18 with however many people have the same Review#s (could be 1, could be all 12 spaces). The first part from those threads I mentioned is already set up so that the user would select the appropriate Review# on Sheet3 via INDEX and validation. Now I just need to get those names and statuses.

Hope thats clear. Maybe its a formula solution, maybe a VBA solution, the choice is up to you 'cause I'm stumped.

 

[Aladin Akyurek]

...I suspect the problem had something to do with the 'Bigstr' portion of the formula but in any case, no matter how I tried I ended up with #N/A...

Try using BigNum, which must be defined as referring to

9.99999999999999E+307

instead of BigStr.

 

[ammdumas]

How do you define it? With just_jons I tried putting it actually into the formula (his being REPT("z",255)). What does actually do anyways?

And also, I couldn't wrap my mind around the 'N' (i.e. ...A:A,N(A2)...or...+N(A2),""...). Where does that fit in?

 

[ammdumas]

Yay! Danka...Merci Beaucoup...Thanks...Domo Origato.... ray:

Figure out how to define Bigstr and BigNum. And everything is now cool.