Excel brain teaser for this morning

J-Will

[H]ard|Gawd
Joined
Jan 10, 2009
Messages
1,728
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:
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.
 
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.
 
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?
 
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 :p
 
Thanks for the starting point, I'll play around with it some tomorrow.
 
Back
Top