I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

You probably would need VLOOKUP or INDIRECT.EXT for this. VLOOKUP is an
excel function and would require the workbook with the info you need to be
open. If the data is on different worksheets within the same workbook, you
should only need VLOOKUP.

INDIRECT.EXT does not require the workbook to be open, but you probably
would need to have a VLOOKUP within your function. MOREFUNC is available
he

http://xcell05.free.fr/english/index...func_Functions

HTH,
Barb Reinhardt
"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

Would it be possible to give me an example formula? I'm new at this and am
struggling with it.
If I need to have both worksheets that's fine as long as the formula works.
I would like to type the store # in column M and it pull up the address (col
N), city (col O), state (col P) and zip (col Q) from the other worksheet.
Thanks!

"Barb Reinhardt" wrote:

You probably would need VLOOKUP or INDIRECT.EXT for this. VLOOKUP is an
excel function and would require the workbook with the info you need to be
open. If the data is on different worksheets within the same workbook, you
should only need VLOOKUP.

INDIRECT.EXT does not require the workbook to be open, but you probably
would need to have a VLOOKUP within your function. MOREFUNC is available
he

http://xcell05.free.fr/english/index...func_Functions

HTH,
Barb Reinhardt
"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

If you could give me the following information, I could give you a formula
that works for your application:

1) What sheet is the information you need on?
2) What column is the store number in?
3) What column are the address, city/state, and ZIP code in?
4) How many rows of data do you have on this sheet?

I can get you something close with that info.

"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

1) WMstorelist 3_20_06 - the master store sheet
2) Column A - Store #
3) Column C - Address
Column D - City
Column E - State
Column F - Zip
4) 3678 - Total Rows

On the invoice sheet, the address info needs to go into columns

Column N - Address
Column O - City
Column P - State
Column Q - Zip

Thanks!!

"Barb Reinhardt" wrote:

If you could give me the following information, I could give you a formula
that works for your application:

1) What sheet is the information you need on?
2) What column is the store number in?
3) What column are the address, city/state, and ZIP code in?
4) How many rows of data do you have on this sheet?

I can get you something close with that info.

"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

This is what you'll need

=VLOOKUP(A1,WMstorelist 3_20_06!A2:E3678,FALSE)

A1 is the store # that you want to match. Change as necessary,

This assumes that the store #'s are sorted from low to high.

"S. Hernandez" wrote:

1) WMstorelist 3_20_06 - the master store sheet
2) Column A - Store #
3) Column C - Address
Column D - City
Column E - State
Column F - Zip
4) 3678 - Total Rows

On the invoice sheet, the address info needs to go into columns

Column N - Address
Column O - City
Column P - State
Column Q - Zip

Thanks!!

"Barb Reinhardt" wrote:

If you could give me the following information, I could give you a formula
that works for your application:

1) What sheet is the information you need on?
2) What column is the store number in?
3) What column are the address, city/state, and ZIP code in?
4) How many rows of data do you have on this sheet?

I can get you something close with that info.

"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

Thank you so much for your help!!

"Barb Reinhardt" wrote:

This is what you'll need

=VLOOKUP(A1,WMstorelist 3_20_06!A2:E3678,FALSE)

A1 is the store # that you want to match. Change as necessary,

This assumes that the store #'s are sorted from low to high.

"S. Hernandez" wrote:

1) WMstorelist 3_20_06 - the master store sheet
2) Column A - Store #
3) Column C - Address
Column D - City
Column E - State
Column F - Zip
4) 3678 - Total Rows

On the invoice sheet, the address info needs to go into columns

Column N - Address
Column O - City
Column P - State
Column Q - Zip

Thanks!!

"Barb Reinhardt" wrote:

If you could give me the following information, I could give you a formula
that works for your application:

1) What sheet is the information you need on?
2) What column is the store number in?
3) What column are the address, city/state, and ZIP code in?
4) How many rows of data do you have on this sheet?

I can get you something close with that info.

"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!

Oops, forgot one thing. And it's IMPORTANT

=VLOOKUP(A1,WMstorelist 3_20_06!A2:E3678,2,FALSE) gets 2nd column
=VLOOKUP(A1,WMstorelist 3_20_06!A2:E3678,3,FALSE) gets 3rd column

You may want to add some $ to get this:

=VLOOKUP(A1,WMstorelist 3_20_06!A$2:E$3678,2,FALSE) gets 2nd column

If you copy down, the first row # will remain the same in the lookup.



"S. Hernandez" wrote:

Thank you so much for your help!!

"Barb Reinhardt" wrote:

This is what you'll need

=VLOOKUP(A1,WMstorelist 3_20_06!A2:E3678,FALSE)

A1 is the store # that you want to match. Change as necessary,

This assumes that the store #'s are sorted from low to high.

"S. Hernandez" wrote:

1) WMstorelist 3_20_06 - the master store sheet
2) Column A - Store #
3) Column C - Address
Column D - City
Column E - State
Column F - Zip
4) 3678 - Total Rows

On the invoice sheet, the address info needs to go into columns

Column N - Address
Column O - City
Column P - State
Column Q - Zip

Thanks!!

"Barb Reinhardt" wrote:

If you could give me the following information, I could give you a formula
that works for your application:

1) What sheet is the information you need on?
2) What column is the store number in?
3) What column are the address, city/state, and ZIP code in?
4) How many rows of data do you have on this sheet?

I can get you something close with that info.

"S. Hernandez" wrote:

I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.
Help please!!