Copy each row consecutively into one single column preserving formulas

Hi

Racking my brains and getting nowhere fast...

I have tried adapting various formulas from web pages and none of the seem to do what
I need as they seem to only work on a set defined range...
#
Is it possible to have a formula that you enter into column I and it copies all data from each
row in columns A to G?

Each cell in Columns A to G have a formula in them that is getting data from other worksheets
and its important that the formulas are preserved as the data that they retrieve will change and
this should be reflected in Column I also.

I don't wish to ask for too much but also could the formula also return nothing if a cell in a Row
is blank?

I have attached a example of what I need but if you need any more information please ask

Thanks

In I3, try:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6))/(A$3:G$6<>0),ROWS(I$3:I3)),"R0000C00000"),0),"")

and copy down. It also excludes empty values, otherwise delete the blue highlighted part.

Best,
berlan

Hi Berlan

Wow, than you for the fast reply, really appreciate it.

Tried your formula and it returns empty cells, eg. No date from the rows.?

Not exactly sure if I've understood correctly.

If you want the exact formulas, if any in there, you could get those by (in I3 and copy down):

=IFERROR(FORMULATEXT(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6)),ROWS(I$3:I3)),"R0000C00000"),0)),"")

will be seen as text strings, which can then be converted to values and made to formulas.

Try it out making some of the values in A3:G6 to formulas (e.g. =A1, =IF( ... ) and see how it works.

Hope that helps.

Cheers

@Tony Valko, very true, missed that.

With that in mind, we need to use an array formula (confirm by Ctrl+Shift+Enter) in I3 and drag down.

=IFERROR(INDIRECT(TEXT(SMALL(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6),ROWS(I$3:I3)),"R00000C00000"),0),"")

for the output values in those cells only, incl empty cells.

and for formulatext,

=IFERROR(FORMULATEXT(INDIRECT(TEXT(SMALL(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6),ROWS(I$3:I3)),"R00000C00000"),0)),"")

Okay, how about just showing the results of the formulas in the rows in the columns?

Sort of a transpose type function but one that can cope with a variable number of rows?

Hang on, I think this has done it

=IFERROR(INDIRECT(TEXT(SMALL(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6),ROWS(I$3:I3)),"R00000C00000"),0),"")

Testing it now

Thanks for the kind suggestions so far everyone, really appreciate it.


Well it kind of works but it only copies the first four rows into the column...

If its possible I need a formula that can copy a infinite or unknown number of rows,
or putting it another way, copy how ever many rows are entered?

Originally Posted by FDibbins

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi FDibbins

I did just that in my original post

Okay, I think I've solved it

I changed the range reference (in Red)

=IFERROR(INDIRECT(TEXT(SMALL(ROW(A$3:G$6)*10^5+COLUMN(A$3:G$6),ROWS(I$3:I3)),"R00000C00000"),0),"")

To 4000 which I believe will be more than enough (in Blue)

=IFERROR(INDIRECT(TEXT(SMALL(ROW(A$3:G$4000)*10^5+COLUMN(A$3:G$4000),ROWS(I$3:I3)),"R00000C00000"),0),"")

Seems to have done the trick

Now this is interesting...

Both Berlan's and Tony Valko's formulas work perfectly - Thank you both, really good of you all to help.

Is there any benifit from using one formula instead of the other or are both just different ways of doing the same thing ?

Originally Posted by Tony Valko

Well, I'm naturally biased towards my suggestions...

My suggestion uses less functions and is not as complicated!

Just been testing with both formulas using 800 Rows of dummy formulas and yours seems to calculate a little faster so I'm going to go with yours for now.

Thanks again Tony

Just wanted to say thank you to everyone for your help.

Thanks guys