Report like functionality in sheet

mquadeer

We have a requirement where the user is not willing to use the report but would like the following:

Sheet 1, Sheet 2 data should flow into sheet 3.

Example: If Sheet 1 has 0-10 rows they get to Sheet 3's 1-5 rows

Sheet 2 has 1-5 rows they will end up in sheet 3 rows 6-10

Next time when data is added to sheet 1 they would like it to end up just below sheet 1's data and the same for sheet 2 and so on.

FYI, the data is manually entered in sheet 1 & 2 and doesn't use data shuttle

Jgorsich

In sheet 3 add a column called "Row" per the accepted answer in this thread (or just add a column called Row and fill it with 1,2,3,etc out to more numbers than you'd ever expect to have to deal with):

Is there a way to reference the row above? - Smartsheet Community

Then, you can use an index() function to pull all the information necessary from sheet 1 and an index(min(collect())) function to pull all of sheet 2 with a helper column identifying sheet. Without writing a formal function, the overall formula might look something like this (comment references are in curly brackets):

Helper column formula (name it "sheet"):

=iferror(if(index(sheet1column,row@row)<>"","Sheet1","Sheet2"),"Sheet2")

{the idea here is that you are checking to see if there is a value in sheet 1 at that row reference - if there is, you identify it as Sheet 1. Otherwise you get a blank cell or an error and you call it Sheet 2.}

Main column formula:

iferror ( {1}

index(sheet1column, row@row), {2}

index(sheet2column,row@row - min(collect(row:row,sheet:sheet,"Sheet2"))+1 {3}

)

{1}: once the contents of sheet 1 are exhausted, your index should throw an error. If it gives you an empty cell instead (""), you might need to adjust this to an if() statement based on that.

{2}: here you are pulling the n-th row from sheet 1, with N being whatever is entered in your "Row" column

{3}: Here you are basically doing the same as for sheet 1 BUT you are subtracting from the value at row@row the SMALLEST value that was tied to Sheet2 (and then adding 1 to avoid an off-by-one error, I'm pretty sure this will be necessary). Presuming you have 14 entries for sheet 1, this makes 15 the lowest value in your Row column tied to a "Sheet2" entry in your "Sheet" column. So, you subtract 15 and add 1 to change that 15 to a 1 (and the 16 to a 2, etc) and then index off of it.