Help with Excel Macro

[davidjearly]

Hi guys,

I'm looking for some help with the above.

I have an Excel workbook with 5 different sheets. One of the sheets contains a list of customers and there is another sheet where I would like data from the 1st sheet to be copied into.

e.g. Sheet 1 has the following columns and below these are a list of customers.

ID
First Name
Last Name
Date Entered
Order Number
Date Received
Date Shipped

I have another sheet, say Sheet 5 which has some of these columns, and I would like the data to be automatically copied across when entered onto the first sheet.

Sheet 5 has the following columns:

ID
First Name
Last Name
Date Entered

When the ID field is completed in Sheet 1 (this contains a unique alphanumeric ID for each customer), I would like the data for all columns mentioned above in Sheet 5 to be automatically completed with the same values as Sheet 1.

Can someone help me with a Macro for this task.

I'm working with Excel 2003 on Windows XP.

Thanks,
David

 

[MacDawg]

Are you sure you want a macro to run?
If I understand what you are wanting, this sounds like it could be accomplished with vlookups

Woof, Woof - Dawg

 

[davidjearly]

I'm not familiar with VLOOKUPS. I'd be happy with whatever solution works TBH!

So if I wanted to link data entered in cell K5 (Sheet 1) to cell B3 (Sheet 5), and the same for other cells, I can do that with VLOOKUPS?

Also, is this something that would need to be enabled on every machine that would be used for editing the spreadsheet?

Many thanks,

David

 

[MacDawg]

It is a formula that would be entered in the cells, so nothing needs to be enabled

Use the Help function and search vlookup and see if gives you the info you need... it is pretty straightforward

If not, let me know and I'll try to walk you through it and give you a sample

Woof, Woof - Dawg

 

[exegete77]

And if you need a formula with more flexibility than VLOOKUP, then consider the combination INDEX/MATCH, which allows lookup in any direction.

 

[MacDawg]

And if you need a formula with more flexibility than VLOOKUP, then consider the combination INDEX/MATCH, which allows lookup in any direction.

Is that available in 2003?

Woof, Woof - Dawg

 

[davidjearly]

Tried the vlookup function and I understand how it can be used to display a name when certain conditions are met, but I'm not sure how I could get it do what I described in my last post.

That is to copy values from particular cells in sheet 1 to particular cells in sheet 5.

Could you maybe provide an example so I can understand if this will do what I need?

If Sheet 1 contains the ID in A2, how would I use vlookup to automatically put this ID in D5 of Sheet 5?

From there, I think I could manage.

Many thanks,
David

 

[Veldek]

Tried the vlookup function and I understand how it can be used to display a name when certain conditions are met, but I'm not sure how I could get it do what I described in my last post.

That is to copy values from particular cells in sheet 1 to particular cells in sheet 5.

Could you maybe provide an example so I can understand if this will do what I need?

If Sheet 1 contains the ID in A2, how would I use vlookup to automatically put this ID in D5 of Sheet 5?

From there, I think I could manage.

Many thanks,
David

Why don't you just use the formula

=IF('Sheet 1'!A2<>"";'Sheet 1'!A2;"")

in D5 of Sheet 5? Then you can use VLOOKUP to get the corresponding data:

=VLOOKUP(D5;'Sheet 1'!A1:F65536;x;TRUE)

where x is the column from which you want the data.

 

[exegete77]

Is that available in 2003?

Woof, Woof - Dawg

Yes, it is. I had used it regularly at a Fortune 50 company until I left last year at this time. These examples assume using XL 2000/2002/2003, and possibly 2007, although I have never tried 2007.

Here is a simple example of the combination formula:

=INDEX($A$3:$A$8,MATCH(-B3,$B$3:$B$8,0))


Here is an example of two conditions lookup:

=INDEX(C1:C12,MATCH(1,(A1:A12="A")*(B1:B12="Type1"),0))

(which has to be entered with CTRL + SHIFT + ENTER, instead of just ENTER)


Many other combinations possible.

.

 

[exegete77]

Here is an excellent site: Excel User. Be sure to explore everything. I attended one of his [Charley Kyd] training seminars 4 years ago. Best seminar ever!