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!

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" wrote:

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!

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?

"JLatham" wrote:

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" wrote:

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!

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.

"Marilyn" wrote:

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?

"JLatham" wrote:

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" wrote:

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!

You're missing a closing parenthesis.

Try =IF(ISERROR(your formula),"",your formula)
--
David Biddulph


Marilyn wrote:
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.

"Marilyn" wrote:

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?

"JLatham" wrote:

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" wrote:

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!

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


Gord Dibben MS Excel MVP



On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
wrote:

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.

"Marilyn" wrote:

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?

"JLatham" wrote:

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" wrote:

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!

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" wrote:

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


Gord Dibben MS Excel MVP



On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
wrote:

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.

"Marilyn" wrote:

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?

"JLatham" wrote:

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" wrote:

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!


.

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

On Sat, 30 Jan 2010 15:20:01 -0800, Marilyn
wrote:

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" wrote:

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


Gord Dibben MS Excel MVP



On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
wrote:

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.

"Marilyn" wrote:

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?

"JLatham" wrote:

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" wrote:

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!


.