#### Excel 2003-Beginner

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!

 0
Utf
1/29/2010 3:11:01 AM
excel.misc 78881 articles. 5 followers.

7 Replies
975 Views

Similar Articles

[PageSpeed] 56

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!
>
 0
Utf
1/29/2010 3:30:01 AM
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!
> >
 0
Utf
1/30/2010 2:54:01 PM
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!
> > >
 0
Utf
1/30/2010 3:58:01 PM
You're missing a closing parenthesis.

--
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!

 0
David
1/30/2010 6:03:13 PM
=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

On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
<Marilyn@discussions.microsoft.com> 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!
>> > >

 0
Gord
1/30/2010 7:33:10 PM
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,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
>
>
>
> On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
> <Marilyn@discussions.microsoft.com> 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!
> >> > >
>
> .
>
 0
Utf
1/30/2010 11:20:01 PM
Thanks for the feedback

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

I copied your original formula then ran this macro.

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
<Marilyn@discussions.microsoft.com> 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,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
>>
>>
>>
>> On Sat, 30 Jan 2010 07:58:01 -0800, Marilyn
>> <Marilyn@discussions.microsoft.com> 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!
>> >> > >
>>
>> .
>>

 0
Gord
1/31/2010 4:14:46 PM

Similar Artilces:

Hi We are using Excel 2000 and have a Addin that create a extra Tool bar. What we want to do is get this automatically loaded everytime someone loads Excel. If I go to Tool \ Addin and tick our addin it loads everytime OK. But we don't want to get all our users to have to do this themselves, if we know either the regit key or file that we needed to puch out to our users we could turn this setting on for them. Does anyone know how to do this? Thanks Paul www.purenetworking.net Jan Karel Pieterse used a helper workbook that added an addin in his namemanager utility. You can find it a...

Mark "track changes" in MS excel
Hi folks, I am stuck with this topic so maybe you can help me. We sent out a template with data that have to be updated by our employees. We enabled "Track changes" (shared document) to verify what data was changed. So far so good. No I want to mark the cells that have changed, for example set the background color to red, and unshare the document (history will be lost). I have written a macro that marks a cells which have comments in it but how can I check if the cell was changed in the past? Thx in advance for your help. Christian P.S. the code to find a comment in a cell ###...

I need to display leading zeros in whole numbers eg 05324 Format as 00000 -- HTH Bob Phillips "kiwi" <kiwi@discussions.microsoft.com> wrote in message news:4560E85F-CE45-487F-812F-C7CA9D7FB042@microsoft.com... > I need to display leading zeros in whole numbers eg 05324 If that is for a US zip code, they really should be formatted as text then they can also be consistent with zip+4 codes as well, and with codes used in other countries as well. Format as text is best, or precede with a single quote. If they aren't going to be treated arithmetically then ...

clipboard in Excel collects but will only paste most recent item
I have checked option settings and cannot discover why all items cannot be pasted. Gregory The default clipboard is Windows Clipboard and will accept one item only. For multiples go to Edit>Office Clipboard to bring that up. Collect the items then "Paste All". Gord Dibben Excel MVP On Tue, 17 May 2005 10:11:32 -0700, "Gregory1558" <Gregory1558@discussions.microsoft.com> wrote: >I have checked option settings and cannot discover why all items cannot be >pasted. ...

Money 2003 Problem
The age old problem. Never really backed up upon exiting Money. Computer packs up - no backup file. However a money file (.mny I think) is left in my documents. On attempting to restore it, it initially tells me a location where it will save the backup file. Then it gives the following error message. 'Money cannot locate C:\Program Files\Microsoft Money\Backup2 or cannot open it because it is a read-only file, or you do not have permission to change it, or your disk drive is protected. If you have chosen the correct file and it cannot be accessed,you will need to click OK and ope...

Access 2003, sharing application over a Novell Network
I recently built a simple 12,000 record database (a little over 7 MB) for my assoicates all over the country via our VPN . It was created in Access 2003, converted to Access 2000, and runs off a shared drive on a Novell server in Washintgton DC. The response times have been incredibly slow, upwards of a minute to query an initial record tied to the primary key. (although it responds instantly when running off a single stand alone PC) Does anyone have any tips about how I can make it run quicker or respond faster over a Novell network via VPN? It's pretty much dead in the water no...

MS Project 2003 Clarity Integration Field Mapping
I am using MS project 2003 with full Clarity integration and noticed that there is a neat field available when you use the Workbench scheduling tool - Pending Time Entry. Does anyone know how I can get the field/column mappings between MS Project 2003 and Workbench when integrating with Clarity? ex. in Workbench ETC is the same field as Remaining Work in MS Project 2003. What about all the others??? ...

GAL and Exchange 2003
I am simply(?) trying to set up some external contacts for our church office so that everyone has access to them all. I have added a contact to AD (and a distribution list) but they refuse to appear in all users or the GAL. We are running SBS2003 with Exchange. I have looked for solutions on Microsoft tech net but cannot seem to find anything. Any help would be very much appreciated.. Andrew/ On Thu, 4 Feb 2010 05:02:02 -0800, AndrewOfAzotus <AndrewOfAzotus@discussions.microsoft.com> wrote: >I am simply(?) trying to set up some external contacts for our church o...

2000 to 2003: Drop-down box crashes system inconsistantly
2000 version works fine. Users that run 2003 on XP crash when they use the drop-down box, which, after update opens filtered form. Solution: They copy the source FE file from the network location to local HD, then it works for a while, until it crashes again. Then they replace local file with network source. Weeks go by without a problem, on other days they have to do this 3-6 times a day. Source file remains the same. Very strange!!!!! Anyone any ideas?????? Presumably this combo is unbound. First thing to try is to force the record save before trying to do th...

How many computers can I install ms office basic edition 2003 on?
We have two computers with it installed on already, but one of them is dying and I want to install it on a new computer. Is it okay to do this? What does the EULA say? Generally a retail version can be installed on 2 working devices "scrumpyjill" <scrumpyjill@discussions.microsoft.com> wrote in message news:7126D521-E248-4733-B7CA-30D30D9114FB@microsoft.com... > We have two computers with it installed on already, but one of them is > dying > and I want to install it on a new computer. Is it okay to do this? To my knowledge all MS Office Basic Editi...

Exchange 2000/2003 ASP/ISP Deployment Guide
Hi NG, where has the "Exchange 2000 ASP Deployment Guide" gone? Is there any "Exchange 2003 ASP/ISP Deployment Guide" out there? Any comments, tipps around hosting Exchange 2003 for many "virtual organizations"? Tobias Redelberger StarNET Services (HomeOffice) Schoenbornstr. 57 D-97440 Werneck Tel: +49-(0)9722-4835 Mobil: +49-(0)179-25 98 341 Email: T.Redelberger@starnet-services.net Try this link http://www.kapilaggarwal.com/pdf/Exchange%20Deployment%20Guide.doc "Tobias Redelberger" <T.Redelberger@starnet-services.net> wro...

Being a complete newbie here, my question is as follows. I have created a spreadsheet for our Little League baseball team by position and inning. In each cell I have created a dropdown menu that lists each of our 11 players by name so that all I have to do is select the players name and it fills in that cell for that position/inning. What I would like this drop down menu to do, if possible, is as I move down the player position (in a single inning) and choose a players name to fill that position, the dropdown menu then removes that players name in the next position to be filled. EX: ...

excel database 25,000 plus names
I am working with a database i set up in excel. It has over 25,000 names, total size of file is 274mb. If i transfer all my data from an excel database to access, will i work faster. Right now it takes forever to open and save. I am told it is not my computer. any tips??? -- mikedipaol ----------------------------------------------------------------------- mikedipaola's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3568 View this thread: http://www.excelforum.com/showthread.php?threadid=55465 If this is just a datastore, then yes, it is better in Access and wh...

Bringing variables from Excel to be used in Microsoft Query
I just started using Microsoft Query and it looks like it does a good job. However, I want it to pass over a special number that represents the number of seconds after 1/1/1970. What I do is create the value using date functions to get the current date then subtract and multiply to get the value. Now I'm trying to figure out how to modifiy Microsoft Query to read that value that's in that cell. Anyone know how I can do that? ...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

How can I create a graph & execute Excel from a non- MS environme
I would like to set up an application that can be run from Unix or another operating system that can be used to display a graph in Excel.(maybe a perl script, or something?) I can create a csv file from the data coming from a non-microsoft environment. I would like to be able to then run a unix system command, or a command that can be executed from the "run" line from MS Start menu that would use this data to open Excel and display the data as a chart. CJ Dev Wrote: > I would like to set up an application that can be run from Unix o > another > operating system that...

Excel Formula Copy
Why would a formula copy down the whole column but NOT provide the correct answers within the cells at one computer and at another computer would work? Jennifer schreef: > Why would a formula copy down the whole column but NOT provide the correct > answers within the cells at one computer and at another computer would work? Could you be more specific? What does it say on the other computer? Maybe in Tools > Options> Calculations "manual" is selected. Maybe the Automatic Calculation option is off on the computer it wont work on?? Thats my only guess "Jennifer&quo...

Problems w/ Outlook 2003
Starting yesterday I have been having problems sending e-mails with Outlook 2003 SP1. I use Comcast and while I can recieve I can not send emails with multiple recipients. I get the following messages: Task 'mail.comcast.net - Sending' reported error (0x8004210B) : 'The operation timed out waiting for a response from the sending (SMTP) server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' Task 'mail.comcast.net - Sending' reported error (0x800CCC60) : 'Your outgoing (SMTP) e-mail server has r...

unintentional re-sizing of documents embedded in Excel
Within the last few days, Ive been experiencing a problem with Word documents embedded in an Excel worksheet. While I'm typing in the embedded document, the document size will change without any apparent cause. I've been using this form for years without any problem. We have virus protection software that is current, so I doubt that is the problem. Any ideas or suggestioins?? TIA, Rich Hi no solution but this is probably not a virus or a setting. This happens in my experience quite often to embedded Excel documents. Would consider this more as a bug (with no workaround I know...

deleting resources 2003
For over a year, I've tried to delete user accounts from MS Project Server 2003 using both my own administrator account or a generic account. It worked 2 or 3 times, deleting one name at a time, but has failed ever since, giving the Request Status error 9600. I find other cries for help all over the web, but no one has ever given an answer. Is there NO ONE who understands why this happens? Along with this, I have tried PSCleaner. I can pull the lists of resources, but when I click the delete button, after a 60-90 second pause, I get the following message: Deleting the selected R...

exchange 2003 public folder sync, which one is updated
If I have public folders replicateing between 2 exchange 2003 systems and someone makes a change, which one is updated? The one they are connected to is the one that is updated. Users are connected to the one referred to in the properties of the Mailbox Store that houses their mailbox. "Trey" <Trey@discussions.microsoft.com> wrote in message news:271997FD-E0BD-469B-A64D-2FC52FB68575@microsoft.com... > If I have public folders replicateing between 2 exchange 2003 systems and > someone makes a change, which one is updated? ...

How to have multiple columns in excel that will expand and colaps.
I have a large about of tiered data, multiple columns, that I would like for the users to be able to expand and colapse on by each column level to view that level of data. Is there a method of doing this. It's hard to understand what you are wanting from what you say. Provide some detail about what the data looks like at the start, then what it looks like after expanding and then what it looks like after collapsing. Don't attach a file to your post. Just describe what you have and want. HTH Otto "columnhelp" <columnhelp@discussions.microsoft.com> wrote in me...

Excel data query goes away.
I have a work book that gets information from an SQL database. About every two months the query goes away. Why? and how can I stop this from happening? ...

how do I set up Outlook 2003?
I installed Office Pro 2003 on my office and home computer. The setup on my office computer went great. I can't get Outlook to work on my home computer. I un-installed and then re-installed the entire package on my home computer, but I can't get Outlook to work. I get the screen about migrating account settings, but it will not accept my password. I finally get the error message "Unable to open your default e-mail folders. Outlook.ost is not an offline file folder." I have un-installed and re-installed the entire package several times w/o any effect. Is ther...