🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Thread View: microsoft.public.excel.misc
2 messages
2 total messages Started by Michelle Sun, 01 Nov 1998 00:00
Need help bringing over location
#3882
Author: Michelle
Date: Sun, 01 Nov 1998 00:00
26 lines
1139 bytes
I am just learning about macros and queries.  What would be the best way to
bring over a customer's location.  I have been trying to do it with a macro but
I keep running into problems.  Please help.  In my workbook I have two sheets
one named "Basic Info" and another named "Current".  What I have been trying to
do is set up a macro that will go through a for the person's account number it
will bring over their location.

For Example:

"Basic Info"                       *         "Current"
                                   *
Acct.#   Name     Location         *         Acct.#   Name  Location   Amount
1111    xxxxx     Sunnyhill        *         4413     xxxx  Chicago    $$$$$
1223    xxxxx     Eureka           *         1111     xxxx  Sunnyhill  $$$$$
1324    xxxxx     New York         *
4413    xxxxx     Chicago          *



Since both sheets are in the same workbook so I know it shouldn't be difficult
but I am having problems writing a macro.  The basic information rarely changes
but every two weeks I have to bring over a new set of current accounts a macro
would certainly save me a lot of work.


Thank you,
Michelle
Re: Need help bringing over location
#3883
Author: "Thomas Ogilvy"
Date: Sun, 01 Nov 1998 00:00
59 lines
2001 bytes
Michelle,
Using the Vlookup function would probably be easier.  In the cell whereyou
want the location, put in a formula like

=vlookup(A2,Current!$A$2:$D$1000,3,0)

this assume a sheet named Current with data in rows 2 through 1000, acct# in
column A, Name in Column B, Location in Column C and Amount in Column D.  On
Sheet Basic_Info the account number is in column A.  The vlookup
automatically looks in the left column of the specified range for the match
to the first argument (in row 2, cell A2).  The third argument says to
return a value for a match in column A from the 3rd column of the range (in
this case, column C, Location).  The zero says look for an exact match on
account number.

The formula is set up so you can drag fill it down the column.


HTH,
Tom Ogilvy


Michelle wrote in message <71in74$2q7@edrn.newsguy.com>...
>I am just learning about macros and queries.  What would be the best way to
>bring over a customer's location.  I have been trying to do it with a macro
but
>I keep running into problems.  Please help.  In my workbook I have two
sheets
>one named "Basic Info" and another named "Current".  What I have been
trying to
>do is set up a macro that will go through a for the person's account number
it
>will bring over their location.
>
>For Example:
>
>"Basic Info"                       *         "Current"
>                                   *
>Acct.#   Name     Location         *         Acct.#   Name  Location
Amount
>1111    xxxxx     Sunnyhill        *         4413     xxxx  Chicago
$$$$$
>1223    xxxxx     Eureka           *         1111     xxxx  Sunnyhill
$$$$$
>1324    xxxxx     New York         *
>4413    xxxxx     Chicago          *
>
>
>
>Since both sheets are in the same workbook so I know it shouldn't be
difficult
>but I am having problems writing a macro.  The basic information rarely
changes
>but every two weeks I have to bring over a new set of current accounts a
macro
>would certainly save me a lot of work.
>
>
>Thank you,
>Michelle
Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads