SOLVED
Home

Autopopulate from one worksheet to the next available space in worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-309001%22%20slang%3D%22en-US%22%3EAutopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309001%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%20two%20worksheets%2C%20one%20labeled%20daily%20and%20the%20other%20Sig%20events.%20The%20sig%20events%20tab%20has%20multiple%20types%20from%20a-I.%20it%20also%20has%20a%20timeframe%20column%26nbsp%3Bwith%20different%20numbers.%20If%20the%20timeframe%20is%20%26lt%3B%3D1%2C%20it%20will%20populate%20in%20the%20Daily%20tab%2Fworksheet%20under%20significant%20events%20(last%2024hrs)%20column%20C%20row%203.%20If%20the%20timeframe%26nbsp%3B%3D%202%2C%20it%20will%20populate%20in%20the%20significant%20events%20(Next%2048hrs)%20Column%20C%20row%2018%2C%20and%20if%20the%20timeframe%20is%20greater%20than%2048hrs%20it%20will%20populate%20in%20Column%20C%20row%2033.%20My%20issue%20is%20I%20don't%20know%20what%20to%20use%20to%20automate%20the%20information%20into%20the%20first%20available%20space%20(Please%20see%20attachment).%20It%20is%20doing%20exactly%20how%20I%20am%20telling%20it%20to%20populate%20row%20by%20row.%20I%20want%20the%20information%20to%20be%20populated%20into%20the%20first%20available%20space.%20Can%20someone%20please%20assist%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313632%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313632%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20assistance.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313290%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313290%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20is%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECopying%20and%20pasting%20the%20cell%20won't%20work%20as%20the%20last%20part%20of%20the%20formula%20is%20not%20(And%20can't%20be%20due%20to%20the%20how%20the%20array%20function%20works)%20an%20absolute%20value%20so%20it%20changes%20when%20you%20copy%20the%20cell%2C%20in%20this%20case%20it%20wants%20to%26nbsp%3Bchange%20to%20A-29%2C%20which%20can't%20happen%2C%20hence%26nbsp%3BROW(%23REF!).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20the%20attached%20by%20copying%20the%20formula%20(Ie.%20Double%20click%20into%20the%20cell%20and%20copy%20the%20formula)%20itself%20in%20C50%26nbsp%3Band%20pasting%20into%20C20%2C%20then%20changed%20the%203%20and%206%20to%200%20and%201%20as%20appropriate%2C%20hit%20CTRL%2BSHIFT%2BENTER%20to%20make%20it%20an%20array%20formula%20and%20then%20drag%20the%20cell%20handle%20down%20to%20C31%2C%20then%20rinse%20and%20repeat%20for%20D%2CE%2CF%2CG%20and%20H.%26nbsp%3B%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-313274%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313274%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20information.%20For%20the%20last%20section%20you%20input%20a%20formula%20between%20three%20and%20six.%20I%20now%20want%20the%20first%20section%20to%20be%20between%200%20and%201.%20So%20I%20tried%20to%20copy%20the%20last%20section%20formula%20and%20paste%20it%20unto%20the%20first%20section%2C%20but%20it%20did%20not%20work.%20Can%20you%20assist%3F%20Please%20see%20attachment%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310235%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310235%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20great%20as%20well.%20Thanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310234%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310234%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20this%20is%20awesome!%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309125%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309125%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20You%20can%20do%20this%20with%20array%20functions%2C%20have%20a%20look%20at%20the%20attached%2C%20let%20me%20know%20if%20this%20is%20what%20you%20were%20after.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309085%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20from%20one%20worksheet%20to%20the%20next%20available%20space%20in%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309085%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20propose%20a%20Power%20Query%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

So I have two worksheets, one labeled daily and the other Sig events. The sig events tab has multiple types from a-I. it also has a timeframe column with different numbers. If the timeframe is <=1, it will populate in the Daily tab/worksheet under significant events (last 24hrs) column C row 3. If the timeframe = 2, it will populate in the significant events (Next 48hrs) Column C row 18, and if the timeframe is greater than 48hrs it will populate in Column C row 33. My issue is I don't know what to use to automate the information into the first available space (Please see attachment). It is doing exactly how I am telling it to populate row by row. I want the information to be populated into the first available space. Can someone please assist? Thanks.

7 Replies
Highlighted

Hello

 

I would propose a Power Query solution.

 

Highlighted
Solution

Hi, You can do this with array functions, have a look at the attached, let me know if this is what you were after.

 

Thank you, 

Highlighted

Yes this is awesome! Thank you. 

Highlighted

This is great as well. Thanks! 

Highlighted
Thank you for the information. For the last section you input a formula between three and six. I now want the first section to be between 0 and 1. So I tried to copy the last section formula and paste it unto the first section, but it did not work. Can you assist? Please see attachment
Highlighted

How is this?

 

Copying and pasting the cell won't work as the last part of the formula is not (And can't be due to the how the array function works) an absolute value so it changes when you copy the cell, in this case it wants to change to A-29, which can't happen, hence ROW(#REF!).

 

I did the attached by copying the formula (Ie. Double click into the cell and copy the formula) itself in C50 and pasting into C20, then changed the 3 and 6 to 0 and 1 as appropriate, hit CTRL+SHIFT+ENTER to make it an array formula and then drag the cell handle down to C31, then rinse and repeat for D,E,F,G and H. 

 

 

Highlighted
Thank you for your assistance.