+ Reply to Thread
Results 1 to 5 of 5

Fetching data from another WS

  1. #1
    Registered User
    Join Date
    01-16-2007
    Posts
    6

    Question Fetching data from another Worksheet

    Hi Everyone,

    I need to perform a task and wanted to ask someone if it can be done.

    I have a worksheet that is used for inventory.

    The worksheet is being used to build an parts catalog.
    Items are broke down by catagory.
    There may be only a 100 - 500 parts in the sheet and all of them are in the same catagory.
    So in other words there is a sheet for every catagory.

    Example:Sheet maybe named "Hand Tools" another may be "Power Tools"
    You get the ideal..

    It has about three columns. (could be more but does not matter)

    Col A- Part Nbr
    Col-B- Description
    Col-C- Cost

    We will call the catagory sheet -- "Sheet A"

    I have another worksheet that has the same information but it is the complete inventory about 10,000 items.

    We will call this sheet -- "Master"

    Now that I have said all of that, this is what I need to do.

    Look for "Part Nbr" in "Master" that matches "Part Nbr" in "Sheet-A"
    Get the "Cost" of that "Part Nbr" (from the column were it is at) and move that cost to "Sheet - A".

    I know that this is a bit worded but I want to be sure that it makes sence.

    At least I hope it does!

    Just some how loop through the hole sheet and get the data .

    This might be easy but I need some help please.

    Thanks for your time,

    Jim
    Last edited by Jhedge; 01-16-2007 at 11:36 PM.

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Let me see if I have the idea ... you want to BUILD a mastersheet based on EXISTING sheets by category?

    Is this a one-time deal? Or, do you also want is so that any time that a new part number appears on any of the category sheets, it would (more or less automatically) appear on the master sheet?

    If all you want is a one time deal, it is going to be faster to do it manually, even if you have 200 sheets. All you have to do is:
    Click in cell A1 of a category sheet, hold down Ctrl & Shift keys and press the End button. This selects all USED cells on the sheet, then copy and paste to the master sheet.

    If you want it to update automatically, you will need a VBA macro that is triggered by a workbook change event, then ensures that there is a one-to-one matching of items in that category with the master sheet (either adding to or deleting from the master sheet as necessary). This is not too difficult. How much VBA do you know?

  3. #3
    Registered User
    Join Date
    01-16-2007
    Posts
    6
    Thanks MS for your reply,

    You wrote:If you want it to update automatically, you will need a VBA macro that is triggered by a workbook change event, then ensures that there is a one-to-one matching of items in that category with the master sheet (either adding to or deleting from the master sheet as necessary). This is not too difficult. How much VBA do you know?


    The catagory is not important, I was just using that as an example.

    Just need to match the part nbrs from the mastes sheet to get the current Cost/prices.

    At times this maybe a lot of items 1000,s, so I guess a macro would be the way to go. Don't know much VBA.

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Now I am confused.

    Are you reading data from the master sheet to update prices in the category sheets? Or are people adding items to the cateogry sheet and you want to have some way of begin sure that the master sheet has all of the individual items listed on it? Or both?

    To do the former, a simple looked up (I would use MATCH to find the match, then INDEX to do the look up, but some people prefer to do both with a single function named VLOOKUP). Use Insert >> Function and select the category of "Lookup & Reference" for help with any of the 3 functions I have mentioned.

    Automating the latter is what would take some VBA. If you have never used VBA before, no time like right now to start. Turn on the macro recorder (Tools >> Macro >> Record New Macro), click OK to accept whatever name it suggest (probably "Macro1"). Click in a cell, then turn off the macro recorder (Tools >> Macro >> Stop Recording).

    Use Tools >> Macro >> Macros to open the Macro dialog. Select the macro you just recorder and click "Edit". Whatever you find there, delete it and replace it with this.
    Please Login or Register  to view this content.
    This is just a start. It will read to you every item in column A on every sheet until you hit Cancel. When you get bored with this, press the Cancel button.

  5. #5
    Registered User
    Join Date
    01-16-2007
    Posts
    6
    Thanks MS,

    I will give that a try - like you said " no time like the present"!

    Jim

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1