+ Reply to Thread
Results 1 to 6 of 6

Formula needed for auto fill across multiple worksheets-see attached spreadsheet

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    I have attached a WS to show what I am asking for.

    I have 11 Worksheets. I want WS1 to be the main sheet where data is entered.
    WS2-WS11 is where the data will populate.

    When I enter data on WS1 I want it to populate in the corresponding WS2-11 based on text entered in a colummn in WS1.

    As I continue to enter data on WS1, I want it to fill the rows in order on the corresponding Worksheets.

    Please see attached.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    Sample-Multiple ws formula_EDIT.xlsx

    B2 =
    Please Login or Register  to view this content.
    C2 =
    Please Login or Register  to view this content.
    Adding A1 = your worksheet name, A2 = count of how many it occur at Main.

    Drag down the formula to how many you want.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    It works but there is a glitch. I attached the spreadsheet to show.
    When I entered WS "Data 1" in A4, A9 & A10, it does not populate each entry accordingly. It does show on WS "Data 1" in cell A2, the total number of overall entries associated from WS Main which is a total of 3.

    Can you fix it to where it will continue to populate on corresponding worksheets no matter what row I enter the info on WS Main. Basically if I go A1:A1000 on the main, and I select "Data 2" in 10 random places throughout column A on the Main, then I want it to populate all that info on the first 10 rows of "Data 2".
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    Not sure you realized that the formula being shown is an array formula, not a normal formula. I'll make a small tweak to the formula in B2, change it to this:

    =IF(ROWS($B$2:B2)<=$A$2,INDEX(Main!B$2:B$100,SMALL(IF(Main!$A$2:$A$100=$A$1,ROW(Main!$A$2:$A$100)-ROW(Main!$A$2)+1),ROWS($B$2:B2))),"")

    ...and confirm the formula by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.


    Now, copy B2 down column B a short ways and across into column C and it will fill out all your matching rows. Repeat on the other sheets.

    The array formulas MUST be confirmed with Ctrl+Shift+Enter, every time you edit them.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    I think JBeaucaire has give you correct answer. Kindly change the 100 to 1000, and drag down to cell to show the answer.

  6. #6
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Formula needed for auto fill across multiple worksheets-see attached spreadsheet

    works great thanks

+ 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