+ Reply to Thread
Results 1 to 9 of 9

Auto-Populate a row based on data input in another row and associated cell inputs

  1. #1
    Registered User
    Join Date
    07-13-2020
    Location
    San Diego, CA
    MS-Off Ver
    Office 365
    Posts
    26

    Auto-Populate a row based on data input in another row and associated cell inputs

    Hello,
    I am trying to have Excel auto-populate a row based on data entered in another row if a specific cell is selected. See snapshot and example worksheet. Referring to my example, row 2 has data entered with 'Dog' as the category. Because the input is 'Dog', that row gets copied down into row 11. Row 3 has 'Cat' as its category, and therefore, that row gets copied down into row 14. Row 4 has 'Bird' as its category, so that row gets copied down into row 18. So and so forth as I continue to enter data in rows 5, 6, & 7, it gets filtered and copied down below.

    Excel Q.png
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    If you will accept a formula based solution then the following will work (with variations for each sub table as modeled in the attached file):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the Price and DOB columns will need to be formatted as desired.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    07-13-2020
    Location
    San Diego, CA
    MS-Off Ver
    Office 365
    Posts
    26

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    Thank you JeteMc! That worked!!! I will add to your reputation.

  4. #4
    Registered User
    Join Date
    07-13-2020
    Location
    San Diego, CA
    MS-Off Ver
    Office 365
    Posts
    26

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    Sorry JeteMc. I have one quick question. I tried to replicate your formula in my worksheet, but it didn't work. Could you help me understand what the integers 15 and 6 stand for in your formula? I think that's probably where my error lies. Thank you.

    =IFERROR(INDEX(B$2:B$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$1))/($A$2:$A$7=$A$9),ROWS($A$1:$A2))),"")

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    15 is the function number for SMALL and 6 is the option number for ignore error values.
    Here is some information on the AGGREGATE function.
    https://support.microsoft.com/en-us/...6-e19993fa26df
    By the way to start from the first row of data the formula should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  6. #6
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    Hi MarcellaB

    Using Power Query solution
    I had inserted the fourth entry of Dog and do a right-click refresh (or you can click Data-Refresh All), it will be updated.

    Just bring in the data entry into Power Query Editor, created three references filter by category (Cat, Bird, Dog etc)

    Rgds
    Attached Files Attached Files
    Christopher Yap

  7. #7
    Registered User
    Join Date
    07-13-2020
    Location
    San Diego, CA
    MS-Off Ver
    Office 365
    Posts
    26

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    Thank you bluesky63

  8. #8
    Registered User
    Join Date
    07-13-2020
    Location
    San Diego, CA
    MS-Off Ver
    Office 365
    Posts
    26

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    Got it to work! Thank you JeteMc!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Auto-Populate a row based on data input in another row and associated cell inputs

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Auto-populate cell range based on user input/value selection
    By skydivetom in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-05-2020, 10:33 PM
  2. Auto Populate a cell based on another cells input
    By Roomba in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2020, 05:58 AM
  3. Help! I need to populate data based on single cell input
    By markh719 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2014, 11:40 PM
  4. [SOLVED] auto Populate message on cell B based on input on cell A
    By dineshsachidananda in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2014, 03:26 PM
  5. auto-populate based on input
    By JessicaSam in forum Excel General
    Replies: 1
    Last Post: 06-08-2012, 01:47 PM
  6. Auto populate based on user input
    By lotusblossom in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-20-2011, 02:58 AM
  7. Formula to populate cell based on data input elsewhere
    By Unca Wook in forum Excel General
    Replies: 1
    Last Post: 11-03-2010, 07:05 PM

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