I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C"
"D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G
C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C"
"D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G
C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

JR: I have used data validation and vlookup to get to the point where I am
now. I know need to have the value in F3 dictate which table column "C" will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C" "D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

Have you looked at "dependent lists", where each choice from a drop-down
list generates another different drop-down?
So a list with 5 choices can display five other different drop-down lists.
And each of those could generate other drop-downs, and so on, and so on!

Check out Debra Dalgleish's web page on this subject:

http://www.contextures.com/xlDataVal02.html

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Lew" wrote in message
...
JR: I have used data validation and vlookup to get to the point where I
am
now. I know need to have the value in F3 dictate which table column "C"
will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation",
then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a
VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C"
"D"


--------------------------------------------------------------------------
------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105


--------------------------------------------------------------------------
----------
Table 2


--------------------------------------------------------------------------
--------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H
C303
Install A357 Dist U\G
C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311


--------------------------------------------------------------------------
--------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want
to
show a list of column "C" table 1 (where "Repair with out replacing"
came
from) that I can choose from and in I1 the corresponding Account
number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1
should
then display a list of column C table 2 (where "Install" came from)
that I
can choose from and the corresponding account number should be
displayed in
J1. The problem is I have 14 seperate tables with different numbers
of
entries in columns A & C that are possible sources for cell E1. Any
one got
any ideas on how I should go about solving this?

....how about this, since your codes are distinct (A103, A110, C203, etc.),
you could just combine the lists off to the side or on a new sheet. You
would then have all codes within one range. Hope this helps.

"Lew" wrote:

JR: I have used data validation and vlookup to get to the point where I am
now. I know need to have the value in F3 dictate which table column "C" will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C" "D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

I just realized the codes are not distinct. So, you could combine them, then
put them in one table. (A103-C200, A103-C303, A103-C304)

"Lew" wrote:

JR: I have used data validation and vlookup to get to the point where I am
now. I know need to have the value in F3 dictate which table column "C" will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C" "D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

JR: I have the "A" codes in a common list, which works for E1 & F1 Now I
have been told that not all of the "C" codes can be used by all of the "A"
codes, ie if A101 is in F3 then only C302 can be in I1, if A103 is in F1 then
I1 can contain any of the following: C200, C303, C304, C315, C203, C310, or
C311. I have 11 tables that have from 1 to 9 values for a total of 31
choices from which I can populate E1 and F1, and each of the tables has from
1 to 7 values for a total of 13 choices from which I can populate H1 & I1.
Now I am told that I can only choose a value for H1 from the same table as
the E1 choise was made, so if I pick a value for E1 from table 6 the value
for H1 also has to com from table 6 or if I choose a value for E1 from table
2 then the value for H1 must also come from table 2 ans so on. I am at a
loss on how to do this with unequal length columns in the tables.

"JR" wrote:

I just realized the codes are not distinct. So, you could combine them, then
put them in one table. (A103-C200, A103-C303, A103-C304)

"Lew" wrote:

JR: I have used data validation and vlookup to get to the point where I am
now. I know need to have the value in F3 dictate which table column "C" will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C" "D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

Lew: I read your first post again and plotted it out. It looks like a lot of
your problems could be solved by redesigning the sheet that is using the
tables. For example, you have F1 and I1 being driven by two different
inputs. I think you need to add a column after F1. I assume you want to see
"A103" and "A357", not either/or, right? I think you should also add a
column after I1, because you have it being a single code based on one
criteria and a list based on another criteria. See below...
E1 F1 G1
Repair (without replacing) A103 Install
or A357

H1 I1 J1
List Col C tbl 1 Col D tbl 1 Col D tbl 2
or List Col C tbl 2

This is a difficult problem to decipher without seeing the spreadsheet.

"Lew" wrote:

JR: I have the "A" codes in a common list, which works for E1 & F1 Now I
have been told that not all of the "C" codes can be used by all of the "A"
codes, ie if A101 is in F3 then only C302 can be in I1, if A103 is in F1 then
I1 can contain any of the following: C200, C303, C304, C315, C203, C310, or
C311. I have 11 tables that have from 1 to 9 values for a total of 31
choices from which I can populate E1 and F1, and each of the tables has from
1 to 7 values for a total of 13 choices from which I can populate H1 & I1.
Now I am told that I can only choose a value for H1 from the same table as
the E1 choise was made, so if I pick a value for E1 from table 6 the value
for H1 also has to com from table 6 or if I choose a value for E1 from table
2 then the value for H1 must also come from table 2 ans so on. I am at a
loss on how to do this with unequal length columns in the tables.

"JR" wrote:

I just realized the codes are not distinct. So, you could combine them, then
put them in one table. (A103-C200, A103-C303, A103-C304)

"Lew" wrote:

JR: I have used data validation and vlookup to get to the point where I am
now. I know need to have the value in F3 dictate which table column "C" will
be shown in H1. From that point I can again use a vlookup to populate I1.
"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C" "D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?

Between the vlookup and data validation using indirect everything is working
fine. Thanks for your assistance.

"JR" wrote:

Lew: This will give you a drop down list. Go to "Data", "Validation", then
select "List" from menu on the Settings tab. Make sure the "in cell
dropdown" box is checked. Once you have created this, you can use a VLOOKUP
based on the value in the dropdown list to obtain the other data that is
needed.

"Lew" wrote:

I need some help with the following:

I have the following tables as examples:

Table 1
Column "A" "B" "C"
"D"

--------------------------------------------------------------------------------------
Repair (With out Replacing) A103 Trans O\H C200
Troubleshoot & Switching A103 Dist O\H C303
Maint. Demand (emergency) A103 Dist U\G C304
Operate A103 Substation Dist C315
Replace in Kind A111 Substation Trans C203
Tree Trim A110 Services O/H C310
Sub Charts A376 Services U/G C311
Inspection A108
Maint. Planned A105

------------------------------------------------------------------------------------
Table 2

----------------------------------------------------------------------------------------
Drafting \ Mapping A349 Trans O\H C200
Inspect \ Patrol A356 Dist O\H C303
Install A357 Dist U\G
C304
Elect Dist Integrated C305
Line Xfmrs C317
Services O/H C310
Services U/G C311

----------------------------------------------------------------------------------------

If E1 = "Repair (With out replacing)" then F1 = "A103". In H1 I want to
show a list of column "C" table 1 (where "Repair with out replacing" came
from) that I can choose from and in I1 the corresponding Account number
should be displayed.. If G1 = "Install" then F1 = "A357". Cell I1 should
then display a list of column C table 2 (where "Install" came from) that I
can choose from and the corresponding account number should be displayed in
J1. The problem is I have 14 seperate tables with different numbers of
entries in columns A & C that are possible sources for cell E1. Any one got
any ideas on how I should go about solving this?