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
kbbaker575
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
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, 

Yes this is awesome! Thank you. 

This is great as well. Thanks! 

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

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. 

 

 

Thank you for your assistance.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies