# Excel brain teaser for this morning

#### J-Will

##### [H]ard|Gawd
Hope everyone enjoyed the long weekend. My brain must be mush...

I know this is a little ridiculous, esp since I cannot figure it out. Here is the situation: I have a workbook with 4 sheets. The first three contain info for three different things. The number of rows change on each of these sheets. This is important to note. The fourth sheet pulls (well not yet, but thats the question) only specific columns from each row on the three sheets.

Question: How do I pull the rows containing only the specific columns from all three sheets on to the fourth... without whitesapce or blank rows. I basically want to combine the rows on to the fourth. The columns selected are the common data, so it matches up when combined on the fourth sheet. The issue is that I cannot figure out an automated way to do so without knowing how many rows from each sheet it is going to pull into the fourth.

Here is what I have been using:
IF('Sheet1'!A3="",IF('Sheet2'!A3="",IF('Sheet3'!A3="",,'Sheet3'!A3:E3),'Sheet2'!A3:E3),'Sheet1'!A3:E3)

Problem is, when it goes to Sheet 2, if picks up on the numbered row where sheet 1 left off. Ex: Sheet 1 has 20 rows and then a blank, it imports the 20 rows then switches to sheet 2 at the 21st row, not starting over on the 1st row of Sheet 2.
If the number of rows is static, this is easy, but Sheet 1 might get 34 rows today, then tomorrow 15. The number of rows on each sheet is dynamic.

Last edited:

#### Pwyl_The_Destroyer

##### Limp Gawd
Are you filling that forumula down the target column with incrementing row indexes?

Anyway, I would probably take a different approach and use VBA to loop the source columns to copy data until it reaches the end of the data.

#### Snowknight26

##### Supreme [H]ardness
Have an example file?

#### J-Will

##### [H]ard|Gawd
Are you filling that forumula down the target column with incrementing row indexes?

Anyway, I would probably take a different approach and use VBA to loop the source columns to copy data until it reaches the end of the data.

Yes, and there in lies the problem. It is auto incrementing for all the sheets. Problem is, I will never know when it needs to start the second or thrid sheet.

I thought about VBA, but this is for a report print out and I dont want a VBA button on the page.

#### J-Will

##### [H]ard|Gawd
Have an example file?

Not at the moment. I could create something if need be. Is the info in my post not enough? I can provide more if I need to, just ask.

##### [H]ard|Gawd
Couldn't you use ISLOGICAL(value) to determine whitepaces/blank cells?

#### J-Will

##### [H]ard|Gawd
Couldn't you use ISLOGICAL(value) to determine whitepaces/blank cells?

I suppose, but how do I use that in a loop to extract certain rows from three different sheets?

#### J-Will

##### [H]ard|Gawd
Anyone have examples of VBA that pulls rows from specific columns from specific sheets and loops into a continuous paste onto a fourth sheet by chance?

#### Pwyl_The_Destroyer

##### Limp Gawd
Ok this will do it without vba:

edit- on sheet5:
Output column: (B2 refers to the current cell here, this was copied out of cell B2)
Code:
``=IF(ROW(B2)<=Sheet4!\$E\$1,Sheet1!A2,IF(ROW(B2)<=Sheet4!\$F\$1+Sheet4!\$E\$1-1,INDIRECT(ADDRESS(ROW(B2)-Sheet4!\$E\$1+1,1,,,"Sheet2")),INDIRECT(ADDRESS(ROW(B2)-Sheet4!\$E\$1-Sheet4!\$F\$1+2,1,,,"Sheet3"))))``

edit-
(on sheet 4):
offset cells:

Code:
``````=MIN(A:A)
=MIN(B:B)
=MIN(C:C)``````

"find the end" columns:
Code:
``````=IF(ISBLANK(Sheet1!A3:A107),ROW(Sheet1!A2:A200),"")
=IF(ISBLANK(Sheet2!A3:A145),ROW(Sheet2!A2:A200),"")
=IF(ISBLANK(Sheet3!A3:A214),ROW(Sheet3!A2:A200),"")``````

You'll have to monkey with the additions and subtractions to make things line up if you're not starting your data in row 2. For what it's worth I still recommend vba

#### J-Will

##### [H]ard|Gawd
Thanks for the starting point, I'll play around with it some tomorrow.