Jan 04 2019 07:36 AM
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.
Jan 04 2019 10:00 AM
Hello
I would propose a Power Query solution.
Jan 04 2019 11:31 AM
SolutionHi, 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,
Jan 08 2019 05:51 AM
Yes this is awesome! Thank you.
Jan 08 2019 05:53 AM
This is great as well. Thanks!
Jan 10 2019 12:15 PM
Jan 10 2019 01:00 PM
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.
Jan 11 2019 04:58 AM
Jan 04 2019 11:31 AM
SolutionHi, 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,