+ Reply to Thread
Results 1 to 5 of 5

Fetching data from one sheet to another with some logic!?

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Fetching data from one sheet to another with some logic!?

    Hi, I have a workbook in which there is a database sheet called DTL and a query sheet called QRY. In the QRY sheet Ive create a list containing all items mentioned in sheet DTL and Ive added a button ADD from active-x control in developer tab. What Im trying to do is when I select a item from the drop down list in sheet QRY and hit the button ADD a macro should run and fetch data from sheet DTL using the data in the drop down as lookup value and fill in the table in sheet QRY.

    Please find attached the sample file to work with.
    Attached Files Attached Files
    Last edited by 0Cool; 11-05-2019 at 03:22 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    347

    Re: Fetching data from one sheet to another with some logic!?

    Hi

    I have added code to your [ADD] button.
    This will append data for the selected drop-down item, to the next available row in your Product Name column.

    I have used some 'helper-cells' in range [F1:H2] on the [QRY] sheet.
    (You can hide these columns if required etc etc etc)

    This uses INDEX/MATCH formulas to 'retrieve' data from the [DTL] sheet.
    NOTE: I don't know where you get the CARTON NO.'S for column [C]

    zeddy
    Excel Soft Skin Analyst
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Fetching data from one sheet to another with some logic!?

    Hi zeddy, Carton no. is fetched from sheet DTL's No. Of SB column. The first item in DTL sheet is "HYDRANET CREAM", so it's no.of cartons will be 1/113 to 5/113 in first row of the table and 6/113 to 6/113 in the second row, where 113 is the total of the column No.of SB.
    Check the desired table in QRY sheet that I provided.

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,858

    Re: Fetching data from one sheet to another with some logic!?

    Hi 0Cool,

    NOTE: I deleted your 'Active X' Control and replaced it with a 'Forms' Control. 'Active X' controls are prettier, but can cause Workbook Corruption problems.

    Try the following code which is in the attached copy of your sample file (Ordinary Code Module ModExtractBatchAndCartonData) for a VBA solution to your problem. The items in Red should be modified if Sheet Names or the 'DTL' sheet format changes.
    Please Login or Register  to view this content.
    Lewis

  5. #5
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2007, 2010, 2019
    Posts
    37

    Re: Fetching data from one sheet to another with some logic!?

    Thanks LJMetzger. I need some time to digest it all

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Fetching data from another sheet
    By Patwardhan in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-04-2018, 03:22 AM
  2. [SOLVED] Combinations and fetching in the Forms Sheet
    By fareedexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2017, 10:38 PM
  3. Replies: 4
    Last Post: 04-14-2014, 01:42 PM
  4. [SOLVED] Fetching average from another sheet.
    By Khoori7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2013, 09:57 PM
  5. Replies: 0
    Last Post: 11-05-2013, 01:51 AM
  6. [SOLVED] Generate multiple invoices fetching data from rows of a sheet
    By devang.vakharia in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2013, 08:14 AM
  7. Replies: 2
    Last Post: 02-04-2012, 12:00 PM

Tags for this Thread

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