Formula help to copy from multiple columns into one

[ExcelExceller]

Hi,

I've gotten pretty good with Excel formulas, but this one has me stumped. I have a Workbook with 6 worksheets, one of which is blank. I want to copy the data from all the column As into column A of the blank worksheet (creating a summary page), without including any blank cells. So, when Sheet1 A:A is blank, move on to Sheet2, etc. I tried the formula below, and I get the values filled in for Sheet1 and anything in Sheet2 that starts after the row I left off on in Sheet 1. I need it to start back at the top though. I think I'm close, just if someone can give that extra push...The formula I have tried so far is:

=IFERROR(
INDEX('Sheet1'!B$3:B$998,
SMALL(
IF('Sheet1'!$AA$3:$AA$998=3,
ROW('Sheet1'!$B$3:$B$998)-ROW('Sheet1'!$B$3)+1),
ROWS($K$7:K8))),
IFERROR(
INDEX('Sheet2'!B$3:B$999,
SMALL(
IF('Sheet2'!$AA$3:$AA$999=3,
ROW('Sheet2'!$B$3:$B$999)-ROW('Sheet2'!$B$3)+1),
ROWS($K$7:K8))),
""))

 

[tybaltlives]

I was wondering if something like this might help you. Im not quite certain what you are trying to do.

I created 6 work sheets. Sheet1 is the summary. There are numbers in sheets A1 and A2 of every sheet but sheet1. The first formula combinea the values in A and separates them with a Pipe symbol. The second formula just adds up the values in A.

-R

Excel 2010
	A	B
1	2|3|4||6	15
2	3|7|7|7|8	32
3	||||	0
Sheet1

Cell Formulas
Range	Formula
A1	=Sheet2!A1&"|"&Sheet3!A1&"|"&Sheet4!A1&"|"&Sheet5!A1&"|"&Sheet6!A1
B1	=SUM(Sheet2:Sheet6!A1)

 

[ExcelExceller]

Thanks, but I don't have anything I need to sum up. I have words in column A on all the different sheets, which are separated out based on different categories. The summary page is supposed to bring over all the names into one column. Is that even possible in Excel?

 

[tybaltlives]

The formula in cell A1 brings over the WORDS in all the A1 cells on the other sheets. You would just copy this formula downwards.

 

[ExcelExceller]

The formula in cell A1 brings over all the A1s and combines them into one cell. I'm interested in copying over all the values from A:A Sheet1, then moving on to Sheet2 and doing the same thing. So, the column might look like:
Sheet 1 A1
Sheet 1 A2
Sheet2 A1
Sheet3 A1
Sheet3 A2
Sheet3 A3
Sheet3 A4, etc.

 

[tybaltlives]

OK. I see what you are trying to do now. Do you have an objection to using VB code? I think that would be the simplest way to accomplish what you want.

 

[tybaltlives]

This is pretty basic code. It assumes that you run the macro from sheet 1. The remaining sheets are sheet2, sheet3, sheet4, sheet5, and sheet6. There can be words in any cell in column A of sheets 2 through 6. But no other column can have anything in it if the column extends beyond the last cell of column A. This is because of how the code is determining the last line in column A of any of the sheets. THere are other ways to do this if needed however. ANyway, if you dont mind using VB code then something like this might work for you.

Everything is written downwards from cell A1 on sheet1

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> copyall()<br><SPAN style="color:#00007F">Dim</SPAN> sheetname <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> copyrange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> 6<br>    Sheets(i).Select<br>    lastrow = ActiveSheet.UsedRange.Rows.Count<br>    copyrange = "A1:A" & lastrow<br>    Range(copyrange).Select<br>    Selection.Copy<br>    <br>        Sheets("Sheet1").Select<br>        <SPAN style="color:#00007F">If</SPAN> i = 2 <SPAN style="color:#00007F">Then</SPAN><br>          lastrow = 1<br>        <SPAN style="color:#00007F">Else</SPAN><br>          lastrow = ActiveSheet.UsedRange.Rows.Count + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    Range("A" & lastrow).Select<br>    ActiveSheet.Paste<br>    Range("A1").Select<br><br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>