Pulling rows from other sheet, skipping over rows if specific column is blank

Oh, and let's say there are only three columns per sheet since my indenting failed to work.

So, Year = Column A
Cat = Column B
Name = Column C

Hi czajicek, since you've only 2 sheets for merging see if this manual workaround helps?

1. copy sheet 2 to sheet 1
2. copy sheet 3 (excluding header) to sheet 1
3. by now you shall have all the data but empty rows exist
4. select all the data in sheet 1 (ctrl+a)
5. press ctrl+g > click [special] button > check the radio button for [blanks] > click [OK]
6. Goto [Home] menu, click on the [Delete] icon under the [Cells] category
7. Select [Delete Sheet Rows] and all the empty rows will be removed!



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

Thank you for your response! I appreciate it!

I, however, need a formula as it must be clear to the user to know which cells link to which sheets. Anyone have any ideas? I've used an index, small formula, but I'm getting error messages now

You're welcome.

Could you explain more as in "it must be clear to the user to know which cells link to which sheets"? It seems obvious to me that if the year is 2013 it'll be from Sheet2 and if the year is 2014 it'll be from Sheet3

Also whether the data in Sheet2 and Sheet3 are dynamic (add/remove periodically) or static (no change)? Might need vba for dynamic ones...

It might also be helpful if you could share more on the objective of Sheet1, so that any member could have come out with brilliant ideas for you



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

I edited my original posting to include the real spreadsheet. Here is my message to you below:

Oh, of course! Happy to share any more details so others may be able to help

So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).

I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.

Here is my failed formula:

=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))

Thanks, it's much clearer now
I saw that you've manually input the years for 'Summary Page'!A:A, but how would you know which row change to 2012, 2011, 2010 etc?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Yeah, that's one of the tricky pieces. I'd be fine if I inputted the years manually in column A, inserting the # of rows for each year based on counts of total rows in each sheet. But I suppose it'd be cool to see if there's a way to insert the correct number of rows for each year without having to do that manually

Does macro will do.

A macro could be another solution, too. I'd be fine with this solution as well as there would be code to document how the data was pulled. Anyone have any suggestions on what should be used?

Macro will definitely be more efficient and probably faster in your case as you could just use vba to copy rest of the sheets to Summary and delete all the empty rows thereafter, but there won't be any links... Would your industry accept that?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

A macro would work perfectly as the code would provide documentation for how data was pulled into the Summary Page sheet. Would anyone please be able to assist me with this? I'd be so appreciative! I'll try to look up some solutions as well and try to give them a shot, though any input from someone with more VBA experience would be helpful

And apologies for not originally posting in the VBA boards, but it was unclear whether a formula or macro could solve this issue

Anyone have any ideas?

You can try this vba in a smaller test file (ie. only sheets Summary, 2013, 2012 and 2011).
Please remove everything from your Summary sheet except the header line before running the code

Please Login or Register  to view this content.

ps: this code will copy all sheets (except Summary) to the Summary sheet as long as the Year is not empty



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin