+ Reply to Thread
Results 1 to 6 of 6

Auto completion and filling through multiple sheets

  1. #1
    Registered User
    Join Date
    10-29-2014
    Location
    BC, Canada
    MS-Off Ver
    Home and Business 2010
    Posts
    6

    Question Auto completion and filling through multiple sheets

    Hi all you wonderfully knowledgeable spreadsheet gurus,

    I have looked through everywhere and cannot seem to find what I'm trying to do. I have a spreadsheet where I have multiple sheets. The first is a sheet for my budget with breakdowns for different sections (Blocks) and further breakdown within. I then used different sheets for data entry for each section. I was later asked for a more detailed breakdown so made a ledger to enter all info in. I now am doing the same entries twice, first into the ledger then into the data sheet. What I am trying to do now is eliminate the data entry sheet (or automate it - whichever is easier) and only use the ledger for entry. What I can't figure out is how to pull/point/push the info properly into the main budget sheet. I am attaching a scaled down copy of what I have created and am hoping someone may be able to point me in the right direction.

    Thanks in advance for your help
    djw

    *Edit - I created it and am using in Google Sheets
    Attached Files Attached Files
    Last edited by djw76; 12-10-2021 at 01:52 PM.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Auto completion and filling through multiple sheets

    If you fill in blank cells for the Ledger Block, then you can go direct from the Ledger to the Budget, by using a SUMIF-INDEX-MATCH

    SUMIF the Ledger Set# matches the BUDGET cell, then INDEX the values you want to sum, and match to the correct form, the following example we are doing for "Amort". In cell BUDGET!E15, place this formula, then drag down:
    Please Login or Register  to view this content.
    then you will need to increase the decimal places by two places otherwise you will get whole dollar amounts

    you can then adjust the formula for rentals etc, and for the other forms (1,2,3,4, instead of "Amort")

    the results are completely different to the values you had because it seems you did not supply matching data between the Ledger and BUDGET.
    Last edited by janmorris; 12-10-2021 at 03:58 PM.

  3. #3
    Registered User
    Join Date
    10-29-2014
    Location
    BC, Canada
    MS-Off Ver
    Home and Business 2010
    Posts
    6

    Re: Auto completion and filling through multiple sheets

    Thank you. I'm still having a little trouble. Can I send you the full (not scaled down) version of my sheet? I can also remove the "data" sheets that I don't want to use. So the only sheet tabs will be the BUDGET and Ledger, but will be the full thing

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Auto completion and filling through multiple sheets

    just the Ledger and BUDGET sheet is enough, you wont need the data sheets anymore

    if you can post the g-sheets file link, with the link as "docsdotgoogledotcom/".. so replacing the dots with the word "dot", then i can convert back and take a look at it for you.

  5. #5
    Registered User
    Join Date
    10-29-2014
    Location
    BC, Canada
    MS-Off Ver
    Home and Business 2010
    Posts
    6

    Re: Auto completion and filling through multiple sheets

    docsdotgoogledotcom/spreadsheets/d/1yuHKki_BJWBf8QMJQHnaDqP9YFa8DokuGRxXDq2iP14/edit?usp=sharing

  6. #6
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Auto completion and filling through multiple sheets

    with the complete dataset, the SUMIF-INDEX-MATCH combination fails to give correct results.

    but your SUMIFS solution calculates correctly.

+ 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. Auto filling different sheets from one master sheet
    By spencer695 in forum Excel General
    Replies: 2
    Last Post: 02-08-2020, 07:08 AM
  2. Auto Filling multiple Values, based on range.
    By sc0ttp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2016, 08:19 AM
  3. [SOLVED] Looking up data between two sheets and auto filling a field
    By Beatnik10 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-17-2015, 07:43 AM
  4. Auto Completion of Master Sheet from multiple Pages.
    By harsh_sultania in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-24-2013, 05:44 AM
  5. Auto-filling formulas across multiple sheets
    By kestefon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2013, 08:35 PM
  6. Replies: 0
    Last Post: 04-27-2011, 06:27 PM
  7. Auto filling cells across mutliple sheets
    By Alec H in forum Excel General
    Replies: 5
    Last Post: 02-06-2006, 10:39 AM

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