Autofill but skipping three columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2935229%22%20slang%3D%22en-US%22%3EAutofill%20but%20skipping%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935229%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20I%20have%20a%20project%20I'm%20doing%20which%20involves%20Auto%20filling%20data%20from%20a%20different%20workbook%20and%20I%20feel%20like%20there's%20a%20shortcut%20to%20what%20I'm%20doing.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSheet16!D2%20and%20then%20I%20skip%20a%20couple%20rows%20to%20find%20the%20value%20from%26nbsp%3B%3DSheet16!H2%20and%20then%26nbsp%3B%3DSheet16!L2%20etc.%20etc.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20feel%20like%20there%20must%20be%20something%20in%20fill%20so%20I%20don't%20need%20to%20complete%20this%20respective%20process%20but%20I'm%20not%20sure%20and%20would%20love%20any%20feedback.%3C%2FP%3E%3CP%3EIt's%20located%20on%20the%20MonthyAccounts%20sheet%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2935229%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2935310%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20but%20skipping%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209908%22%20target%3D%22_blank%22%3E%40JackReed%3C%2FA%3E%26nbsp%3BYou%20are%20making%20it%20so%20difficult%20for%20yourself.%20And%20you%20already%20experienced%20what%20it%20leads%20to.%20You%20are%20gathering%20transactional%20data%20in%20way%20how%20you%20would%20do%20in%20a%20paper%20notebook.%20On%20page%20for%20every%20month%20and%20then%20add%20up%20every%20page%20and%20transfer%20totals%20to%20another%20page.%20It%20goes%20to%20far%20to%20redesign%20your%20whole%20schedule%2C%20but%20I've%20added%20a%20sheet%20with%20a%20%3CEM%3E%3CSTRONG%3Estructured%20table%3C%2FSTRONG%3E%3C%2FEM%3E%20that%20collects%20all%20expenses%2C%20but%20date.%20Just%20enter%20them%20as%20they%20incur.%20Excel%20can%20do%20the%20grouping%20by%20month%20for%20based%20on%20the%20date%20entered.%20Then%20create%20a%20%3CEM%3E%3CSTRONG%3Epivot%20table%3C%2FSTRONG%3E%3C%2FEM%3E%20that%20summarises%20the%20expenses%20in%20the%20blink%20of%20an%20eye.%20No%20formulae%20needed.%20Add%20expenses%20as%20they%20incur%2C%20refresh%20the%20pivot%20table%2C%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2935333%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20but%20skipping%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935333%22%20slang%3D%22en-US%22%3EHey%20I'm%20not%20completely%20sure%20if%20I%20made%20my%20problem%20clear%20it's%20almost%20like%20on%20Sheet%2016%20where%20I%20have%20all%20my%20monthly%20budgets%20I%20need%20to%20be%20able%20to%20put%20a%20value%20there%20and%20then%20it%20automatically%20updates%20to%20the%20Monthly%20Accounts%20worksheet%20if%20you%20look%20at%20cell%20G5%20and%20G6.%20I%20want%20the%20values%20to%20be%20able%20to%20update%20on%20that%20sheet%20when%20any%20of%20the%20months%20data%20values%20are%20taken%20as%20the%20pivot%20table%20doesn't%20really%20have%20enough%20in-depth%20information%20for%20this%20budget%3C%2FLINGO-BODY%3E
New Contributor

Hey I have a project I'm doing which involves Auto filling data from a different workbook and I feel like there's a shortcut to what I'm doing.

=Sheet16!D2 and then I skip a couple rows to find the value from =Sheet16!H2 and then =Sheet16!L2 etc. etc.

I feel like there must be something in fill so I don't need to complete this respective process but I'm not sure and would love any feedback.

It's located on the MonthyAccounts sheet

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5 Replies

@JackReed You are making it so difficult for yourself. And you already experienced what it leads to. You are gathering transactional data in way how you would do in a paper notebook. On page for every month and then add up every page and transfer totals to another page. It goes to far to redesign your whole schedule, but I've added a sheet with a structured table that collects all expenses, but date. Just enter them as they incur. Excel can do the grouping by month for based on the date entered. Then create a pivot table that summarises the expenses in the blink of an eye. No formulae needed. Add expenses as they incur, refresh the pivot table, done.

 

 

Hey I'm not completely sure if I made my problem clear it's almost like on Sheet 16 where I have all my monthly budgets I need to be able to put a value there and then it automatically updates to the Monthly Accounts worksheet if you look at cell G5 and G6. I want the values to be able to update on that sheet when any of the months data values are taken as the pivot table doesn't really have enough in-depth information for this budget

@JackReed I understood your intentions, but as an accountant I'm horrified by the schedule and I merely tried to convince you to change your approach. The way you have set-up the report is prone to error and difficult to maintain (as you already experience yourself).

 

Attached, you'll find an updated file showing what a few structured tables can do with the help of Power Pivot (i.e. the Data Model). It's a very quick-and-dirty example, but I would prefer to work with these. But it's of course up to you to decide how you want to control the budget for your sports club.

@Riny_van_Eekelen Don't worry as this project is only for practice give by a professor of mine. I like the pivot table that you made but how am I able to add data from future months as the pivot table is very reluctant to new data

@JackReed Just type new expense transactions to the first table. It will expand automatically. Then, press Refresh All on the Data ribbon.