cross referencing to another workbook

G

green72

Thanks for all the help on the last question, it was very useful and so I am
hopeful someone can once again save me...

I would like to be able to use a numerical ID from one workbook to reference
to a text cell from another workbook, and insert that text to replace the
numerical ID for a report that will be prepared from the data.

Ideally this would be a part of a macro...

Once again, any help would be appreciated.

Thanks
 
J

jlclyde

Thanks for all the help on the last question, it was very useful and so I am
hopeful someone can once again save me...

I would like to be able to use a numerical ID from one workbook to reference
to a  text cell from another workbook, and insert that text to replace the
numerical ID for a report that will be prepared from the data.

Ideally this would be a part of a macro...

Once again, any help would be appreciated.

Thanks
Click to expand...

Can you specify what cells you want to go where? Also anything about
workbook names and sheets woudl be helpful. In the mean time, you
shoudl check out the indirect function and address.

Jay
 
G

green72

I will be more specific, hopefully it will help...

We have data being generated in the field regarding customer services. The
forms (titled Pick Ups), our employees fill out are entirely numerical, and
each customer has an ID #.
This ID#s reference to each customer's name, and this data is stored in
another workbook (titled Pick Up Locations), it consists of merely a column
of ID#s and customer names.
When I prepare a report of the days activities, based on the forms filled
out by our employees, I would like the report to include the customer's name,
and would like to know how I can cross reference the ID#s in the "Pick Ups"
form, extract the customer's name from the "Pick Up Locations", and insert
the name into the final report.

I hope that made it clearer, though I suspect I may have made it less clear...

Thank you in advance.
 
J

jlclyde

I did not mean to leave you hanging on that one. I had a weekend come
up out of no where.
There are a lot of ways to do this. I will cover three of them.

If the information is in Column A to reference and Column B to get
Customer name then you can use Vlookup. this is a vertical look up
formula. It finds what you are looking for in one column and then
moves over a number of columns that you specify. So it woudl be
=Vlookup(D4 - Assuming that is where the number is,A3:B24 - Assuming
this is your array of data, 2 since it is in the "B" column, True
Finds closet match False finds exact match)

=LOOKUP(D4,A3:A24,B3:B24) Same principal but you just specify what to
lookup, where to find it, and what column to chose result.

and last but not least is SUMPRODUCT((A3:A24=D4)*(B3:B24)) You can
add as many conditions as you want and it works when workbooks are
closed.

Jay
 
Top