table/s

  • Thread starter Thread starter trey
  • Start date Start date
T

trey

i have 2 drop down menu/s.. the list is on another
sheet.. the drop down's on my form...
ex:
Type YR 1 2 3
1% / 1mo Int 1 40 30 20
2% / 2mo Int 2 35 25 10
3% / 3mo Int 3 30 15 0
4% / 4mo Int 25 10 10
5% / 6mo Int 20 0 20
3-2-1% 30 20 10
5-4-3% 20 5 15
- when you choose 5%/6mo Int and 2yrs = " 0 " with the 0
being assigned to a specific cell on my form...
what formula do i need to use the vlookup?
presently the drop down menu's on my form are: j29,k29
the " type " listsheet3 u6-u12... the " yr " list: ac6-
ac8... and the table is y6:aa12... im very confused...
plz help...
thanks
trey
 
Hi
hope I got your cell references right. Try the array formula (entered
with CTRL+SHIFT+ENTER):
=INDEX('listsheet3'!Y6:Y12,MATCH(1,('listsheet3'!U6:u12=J29)*('listshee
t3'!AC6:AC12),0))

Note: the years columns has to be filled for all rows
 
My interpretation is a little bit different than Frank's -- hi Frank!

Assuming that your headers for the data in listsheet3 are contained in
Row 5, and that your actual data starts in Row 6, try the following
formula:

=INDEX(listsheet3!Y6:AA12,MATCH(Sheet1!J29,listsheet3!U6:U12,0),MATCH(She
et1!K29,listsheet3!Y5:AA5,0))

Hope this helps!
 
when you say this: the years columns has to be filled for
all rows: what are you telling me to do...
thanks
trey
 
Hi Doemnic
thought this also but decided to go the other way :-)

So lets see what the OP needs
 
Hi
what I meant is that your column AC should contain a year value for all
rows. But see also Domenic's post. Not sure against what you want to
compare your year value agains (column heading or AC column)
 
when i enter in the formula and hit enter it prompts me
to find a file... confusing...plz help... row 5 is
labeled only for my reference-no headers- though since
this is a validated list...
trey
 
Frank Kabel said:
Hi Doemnic
thought this also but decided to go the other way :-)

So lets see what the OP needs
Click to expand...

I'm waiting with baded breath. :-) (I think I've got the spelling
wrong for "baded" -- I'll have to check my dictionary.)
 
trey said:
when i enter in the formula and hit enter it prompts me
to find a file... confusing...plz help... row 5 is
labeled only for my reference-no headers- though since
this is a validated list...
trey
Click to expand...

That's because it's trying to find "listsheet3", which is specified in
the formula. So make sure that the sheet reference in the formula
matches the name of your sheet exactly.
 
Domenic said:
I'm waiting with baded breath. :-) (I think I've got the spelling
wrong for "baded" -- I'll have to check my dictionary.)
Click to expand...

Ahh yes...that should be bated, not baded. Oh well, can't win them all.
:-)
 
=INDEX(sheet3!Y6:AA12,MATCH(PREQUAL!J29,sheet3!
U6:U12,0),MATCH(PREQUAL!K29,sheet3!Y5:AA5,0))
it matches everythis in my workbook and it still does the
search? confused...any reason why it still may be doing
that.
trey
 
trey said:
=INDEX(sheet3!Y6:AA12,MATCH(PREQUAL!J29,sheet3!
U6:U12,0),MATCH(PREQUAL!K29,sheet3!Y5:AA5,0))
it matches everythis in my workbook and it still does the
search? confused...any reason why it still may be doing
that.
trey
Click to expand...

Check your tab and make sure that there are no spaces before or after
the sheet name. Otherwise, I don't know why it would still be
happening. Maybe Frank or someone else has some other ideas.
 
you were right there was a space..corrected and the
formula took... it is giving me an error: #n/a though..
any thoughts....
 
it works..yippee
-----Original Message-----


Check your tab and make sure that there are no spaces before or after
the sheet name. Otherwise, I don't know why it would still be
happening. Maybe Frank or someone else has some other ideas.
.
Click to expand...
 
Back
Top