Excel 2003-Beginner

[Marilyn]

I'm using Excel 2003.

Sorry, I'm if not explaining this well. I'm trying to put information in a
column that comes from another worksheet in the same book but it depends on
what is entered in 2 other columns in the first worksheet. I'll try to give
complete info:

Let's say Column A asks you to pick 1 of 5 gems: Agate, Turquoise, Garnet,
Diamond, Pearl
Let's say Column B asks you to pick 1 of 6 Countries: US, Can, UK, China,
Japan, Germany

The other worksheet has a table with data (its a number in each cell)
representing all the possible permutations of those choices. However (and
this might present a problem) the row/column headers of this table are not
gems and countries. Its just a stand-alone table. Is that a problem?

So, on Column C, is there a formula to recognize which gem you picked in
Column A, which country you picked in Column B then go to the table and bring
in the
number from a table and put it into Column C. (Eg if I picked Agate and
China, I want Column C to say 1394).

Thanks!

 

[JLatham]

It would work beautifully if on the "other worksheet" you add both row labels
and column headers on the table. That way you can easily use a MATCH and
INDEX formula in column C to give you the number you want. Quick example of
the layout of that table, and while I've put the jewels across as column
headers, it doesn't have to be that way, we'd just change the formula in
column C. But as an example

A B C D E F
1 China USA ...
2 Agate 1394 1222
3 Pearl 999 444
4 ...
5 ...
6 Topaz
hopefully that gives you an idea of the layout of that table. Let's also
say the name of that worksheet is 'TableSheet'.

Now on your sheet where you are choosing gems and countries, let us presume
you have a choice made in A2 (gem) and B2 (country) and you put this formula
in C2
=INDEX(TableSheet!$A$1:$F$6,MATCH(A2,TableSheet!$A$1:$A$6,0),MATCH(B2,TableSheet!$A$1:$F$1,0))

and it would work great for you.

 

[Marilyn]

Thank You! It did! However, if I could ask for additional? This sheet will be
used by others and they will fill in the data. So, I have dragged the formula
down to additional rows. However, in those rows (until data is entered) the
results cell is displaying as #N/A. Is there a way to have it as blank until
it has a value? The other reason is that there is ANOTHER cell that is using
this value for a SUM (as a running tally if you would) and it's showing as
#N/A as well. I'd like it to be able to run the tally of those completed and
ignore those that aren't completed, but pick them up automatically when there
is a value. Do these questions make sense?

 

[Marilyn]

I should also mention I went into other posts and found some options (eg
wrapping my formula in something like IF(ISERROR(my formula)," ",(my formula)
but it won't take it. Maybe there's too much there? Maybe I'm entering it
wrong? My formula (that works correctly) is
=INDEX(Tablesheet!$A$1:$E$31,MATCH(F13,Tablesheet!$A$1:$A$31,0),MATCH(G13,Tablesheet!$A$1:$E$1,0))

It works beautifully when there are entries in F and G but if they're blank
its returning #N/A. I just need it to be blank.

 

[David Biddulph]

You're missing a closing parenthesis.

Try =IF(ISERROR(your formula),"",your formula)

 

[Gord Dibben]

=IF(ISNA(INDEX(Tablesheet!$A$1:$E$31,MATCH(F13,Tablesheet!$A$1:$A$31,0),MATCH(G13,Tablesheet!$A$1:$E$1,0))),"",INDEX(Tablesheet!$A$1:$E$31,MATCH(F13,Tablesheet!$A$1:$A$31,0),MATCH(G13,Tablesheet!$A$1:$E$1,0)))


Gord Dibben MS Excel MVP

 

[Marilyn]

Gord, that did it! Thanks so much for your patience in typing the whole darn
thing out. And thanks to everyone for their help

 

[Gord Dibben]

Thanks for the feedback

BTW.......I didn't type the whole thing out.

I copied your original formula then ran this macro.

Sub NATrapAdd()
Dim mystr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
mystr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & mystr & "),""""," & mystr & ")"
End If
End If
Next
End Sub

Handy for adding the ISNA trap to a cell or cells.


Gord