I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

The only thing I can think of would be a setting in AutoCorrect that is
changing it? Might look in Tools | Options | [Spelling] tab, [Auto Correct
Options..] button. Check the list to be auto corrected and see if somehow a
setting of from \ to / got put in there?

If that's not it, I'm afraid a wiser head than me is going to have to jump
in and help. I just seem to be missing something here.

At a different machine now and I went thru these steps in a new workbook:
typed in some text in a cell,
chose that cell and used Insert | Hyperlink
in 'Link to' I made sure [Existing File or Web Page] was chosen along with
"Look in" of [Current Folder]
then I used the pull down and selected My Network Places and browsed to a
drive on another system and chose a file there. The text immediately changed
to hyperlink format (blue underlined text) and hovering over it showed the
hyperlink to be:
File:///\\Antec\Documents\Somefile.xls
closed and re-opened the workbook and it was still that way, as it should
have been.


"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

Disregard the above - response was directed to another thread. Too many
windows open.

Examining your posted data very shortly.

"JLatham" wrote:

The only thing I can think of would be a setting in AutoCorrect that is
changing it? Might look in Tools | Options | [Spelling] tab, [Auto Correct
Options..] button. Check the list to be auto corrected and see if somehow a
setting of from \ to / got put in there?

If that's not it, I'm afraid a wiser head than me is going to have to jump
in and help. I just seem to be missing something here.

At a different machine now and I went thru these steps in a new workbook:
typed in some text in a cell,
chose that cell and used Insert | Hyperlink
in 'Link to' I made sure [Existing File or Web Page] was chosen along with
"Look in" of [Current Folder]
then I used the pull down and selected My Network Places and browsed to a
drive on another system and chose a file there. The text immediately changed
to hyperlink format (blue underlined text) and hovering over it showed the
hyperlink to be:
File:///\\Antec\Documents\Somefile.xls
closed and re-opened the workbook and it was still that way, as it should
have been.


"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

I took the liberty of rearranging things a little and I think this will work
well for you.

On the Tech Data Sheet, I moved selection of names into Column A vs C. This
lets us use VLOOKUP() on the individual salesperson sheets very reliably.
Having done that, on the individual sales person sheets I added a new column
A to be used as a 'helper' column that could be hidden when actually in use.

Assuming that the salesperson's name is going to be in B1 on the individual
sheets (instead of A1), and that you have column headers in row 1, then in A3
on those sheets I put this formula:
=IF(ISNA(VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE)),"",VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE))

The way that's set up the lookup range moves down the sheet as the formula
is filled down it. That is, in A3, lookup range is A2:B$65536, and in A4 it
will change to A3:B$65536, etc, etc. Keeps us from always finding the first
entry for the salesperson's name on the Tech Data Sheet.

Then in B3 I put this formula:
=IF(COUNTIF(A$2:A2,A3)0,"",A3)

Which counts how many times a PO# has appeared in column A on the individual
sheets and only echos it in column B the first time it comes up.

This leaves things looking odd, because you'll have blank cells in column B.
To get those out of view, you can use Data | Auto Filter and choose
NonBlanks for the PO# column.

I ended up doing this after trying to use LOOKUP() to pick up the numbers,
but it's a little more restrictive than VLOOKUP() in this case, so I ended up
moving things around so that VLOOKUP could be used.

A working version of this can be downloaded from:
http://www.jlathamsite.com/uploads/R...ed_Working.xls

If you need the sequence of data on Tech Data Sheet to be
PO #, Date, Salesperson...
then you could insert a column between Date and Customer and simply echo
the selection made in column A on that sheet.

I hope this helps some. We could have worked something out in VB to prevent
the blank cells in column B on the individual sheets, but it would have been
problematic with a locked sheet, formulas aren't.

"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

Great! Thanks for your help. Last couple questions and I will leave you
alone. hah

Is there an issue with using VLOOKUP to find the PO #, now that it is in
column B instead of column A. The reason I ask, in the individual salesperson
sheet all of the vlookup's return #N/A. (issue date, due date, status, etc)

Finally, where can I filter out the blank rows in the individual sheets.
Data | Filter | Auto Filter | ....

Cheers!

"JLatham" wrote:

I took the liberty of rearranging things a little and I think this will work
well for you.

On the Tech Data Sheet, I moved selection of names into Column A vs C. This
lets us use VLOOKUP() on the individual salesperson sheets very reliably.
Having done that, on the individual sales person sheets I added a new column
A to be used as a 'helper' column that could be hidden when actually in use.

Assuming that the salesperson's name is going to be in B1 on the individual
sheets (instead of A1), and that you have column headers in row 1, then in A3
on those sheets I put this formula:
=IF(ISNA(VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE)),"",VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE))

The way that's set up the lookup range moves down the sheet as the formula
is filled down it. That is, in A3, lookup range is A2:B$65536, and in A4 it
will change to A3:B$65536, etc, etc. Keeps us from always finding the first
entry for the salesperson's name on the Tech Data Sheet.

Then in B3 I put this formula:
=IF(COUNTIF(A$2:A2,A3)0,"",A3)

Which counts how many times a PO# has appeared in column A on the individual
sheets and only echos it in column B the first time it comes up.

This leaves things looking odd, because you'll have blank cells in column B.
To get those out of view, you can use Data | Auto Filter and choose
NonBlanks for the PO# column.

I ended up doing this after trying to use LOOKUP() to pick up the numbers,
but it's a little more restrictive than VLOOKUP() in this case, so I ended up
moving things around so that VLOOKUP could be used.

A working version of this can be downloaded from:
http://www.jlathamsite.com/uploads/R...ed_Working.xls

If you need the sequence of data on Tech Data Sheet to be
PO #, Date, Salesperson...
then you could insert a column between Date and Customer and simply echo
the selection made in column A on that sheet.

I hope this helps some. We could have worked something out in VB to prevent
the blank cells in column B on the individual sheets, but it would have been
problematic with a locked sheet, formulas aren't.

"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

works great! thanks again

"JLatham" wrote:

I took the liberty of rearranging things a little and I think this will work
well for you.

On the Tech Data Sheet, I moved selection of names into Column A vs C. This
lets us use VLOOKUP() on the individual salesperson sheets very reliably.
Having done that, on the individual sales person sheets I added a new column
A to be used as a 'helper' column that could be hidden when actually in use.

Assuming that the salesperson's name is going to be in B1 on the individual
sheets (instead of A1), and that you have column headers in row 1, then in A3
on those sheets I put this formula:
=IF(ISNA(VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE)),"",VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE))

The way that's set up the lookup range moves down the sheet as the formula
is filled down it. That is, in A3, lookup range is A2:B$65536, and in A4 it
will change to A3:B$65536, etc, etc. Keeps us from always finding the first
entry for the salesperson's name on the Tech Data Sheet.

Then in B3 I put this formula:
=IF(COUNTIF(A$2:A2,A3)0,"",A3)

Which counts how many times a PO# has appeared in column A on the individual
sheets and only echos it in column B the first time it comes up.

This leaves things looking odd, because you'll have blank cells in column B.
To get those out of view, you can use Data | Auto Filter and choose
NonBlanks for the PO# column.

I ended up doing this after trying to use LOOKUP() to pick up the numbers,
but it's a little more restrictive than VLOOKUP() in this case, so I ended up
moving things around so that VLOOKUP could be used.

A working version of this can be downloaded from:
http://www.jlathamsite.com/uploads/R...ed_Working.xls

If you need the sequence of data on Tech Data Sheet to be
PO #, Date, Salesperson...
then you could insert a column between Date and Customer and simply echo
the selection made in column A on that sheet.

I hope this helps some. We could have worked something out in VB to prevent
the blank cells in column B on the individual sheets, but it would have been
problematic with a locked sheet, formulas aren't.

"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers

Are you having a problem now, or not. I'm not sure. You'd have to slightly
modify your existing VLOOKUP() to account for the added/removed columns.
Remember that the column number to return is the relative column number in
the table. So if you look for a name in a 'table" that has name in column A,
PO# in B, Address in C,

VLookup(name,B1:G99,2,False) would return the PO# (in 2nd column of table)
VLookup(name,B1:G99,3,False) would return the address, etc, etc.

the ,False just says that the list of names in column B doesn't have to be
in alphabetical order.

"scott" wrote:

works great! thanks again

"JLatham" wrote:

I took the liberty of rearranging things a little and I think this will work
well for you.

On the Tech Data Sheet, I moved selection of names into Column A vs C. This
lets us use VLOOKUP() on the individual salesperson sheets very reliably.
Having done that, on the individual sales person sheets I added a new column
A to be used as a 'helper' column that could be hidden when actually in use.

Assuming that the salesperson's name is going to be in B1 on the individual
sheets (instead of A1), and that you have column headers in row 1, then in A3
on those sheets I put this formula:
=IF(ISNA(VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE)),"",VLOOKUP(B$1,'Tech Data
Sheet'!A2:B$65536,2,FALSE))

The way that's set up the lookup range moves down the sheet as the formula
is filled down it. That is, in A3, lookup range is A2:B$65536, and in A4 it
will change to A3:B$65536, etc, etc. Keeps us from always finding the first
entry for the salesperson's name on the Tech Data Sheet.

Then in B3 I put this formula:
=IF(COUNTIF(A$2:A2,A3)0,"",A3)

Which counts how many times a PO# has appeared in column A on the individual
sheets and only echos it in column B the first time it comes up.

This leaves things looking odd, because you'll have blank cells in column B.
To get those out of view, you can use Data | Auto Filter and choose
NonBlanks for the PO# column.

I ended up doing this after trying to use LOOKUP() to pick up the numbers,
but it's a little more restrictive than VLOOKUP() in this case, so I ended up
moving things around so that VLOOKUP could be used.

A working version of this can be downloaded from:
http://www.jlathamsite.com/uploads/R...ed_Working.xls

If you need the sequence of data on Tech Data Sheet to be
PO #, Date, Salesperson...
then you could insert a column between Date and Customer and simply echo
the selection made in column A on that sheet.

I hope this helps some. We could have worked something out in VB to prevent
the blank cells in column B on the individual sheets, but it would have been
problematic with a locked sheet, formulas aren't.

"scott" wrote:

Thanks for your input.

Maybe I could be a little more precise. I have a TECH DATA SHEET, of which I
fill in entirely too much information.
As I fill in the data, I choose a PO #, Date, Salesperson, etc. (Ex)

TECH DATA
A B C D
E F G
1 PO # | DATE | Salesperson | Customer | Address | Item |
QTY |etc
2 10001 9-5-06 Tim Disney x
y z
3 10007 9-5-06 Josh Starbucks x
y z
4 10018 9-5-06 Kent Microsoft x
y z
5 10034 9-5-06 Josh Apple x
y z
6 10067 9-5-06 Josh NIKE x
y z

Column C:C is a DROP DOWN (validation list) that refers to the whole list of
company salespeople (W2:W14).

From this TECH DATA SHEET, I have generated an individual sheet per
salesperson with only basic, need to know information. (Ex)

JOSH
A B C D
E
PO # | Issue Date | Due Date | Cost | Status
1
2
3
4

As of right now I have the issue date(B:B), due date (C:C), cost (D:D) and
status (E:E) in a VLOOKUP dependant on the reference of a PO # (A:A).
However, I have to manually type in the PO # in the cell (A2) to bring up the
other information (B2)(C2) (D2) (E2). I would prefer to have the hole sheet
locked, and have the PO # be entered when I select that given Salesperson in
the DROP DOWN list located in the TECH DATA SHEET.

So in the TECH DATA SHEET, if I were to select "JOSH" from the DROP DOWN
LIST(C3), I would like to have that PO # (A3) be referenced in the JOSH SHEET
(A2) . In the TECH DATA SHEET, if I were to select "JOSH" again from the drop
down list, (C5), I would like to have that PO # (A5) be referenced in the
JOSH SHEET (A3). And etc.

Hope this helps. And thanks for your time.

cheers!

Scott
"JLatham" wrote:

Duplicates are always a concern. Here's one way to start handling it.

Set up a cell using Data | Validation and choosing List as the type of data
and identify the entire list of names as the source. That way you'll be able
to pick a valid name at that cell. Let us assume you set that up in cell D1
and the names list is in column C.

Then to get the PO#, for the FIRST instance of the name you choose in D1 as
it appears in column C, use this formula somewhere (maybe D2?)
=LOOKUP(D1,C1:C20,A1:A20)

In the example, I'm assuiming there are PO #s in A1:A20 and the list of
names is in C1:C20.

"scott" wrote:

I want to return information based on a name I select from a drop down list
of salespeople. Setting up the basic equation was simple enough.

In a sheet (call it JOSH), I set up for an indivudual salesperson's account,
I entered this formula.

=if('techdata'C9="josh",A9,"") where A9 was a PO # i wanted to return.

This works fine on an individual row basis, however I would like to set up
an equation that would search a whole column (C:C) for any cell that "josh"
is selected from a drop down list and return cell A from that cell's row.

I have a general idea, but I wonder if duplicates would be an issue.

=if('techdata'C:C="josh",A:A,"")
i doubt this would work, is there a way that i could search and only return
the same PO # once?

Maybe i am just a wishful thinker. Hope I was clear enough.

Cheers