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