combining data from two sources
 

I am trying to compile data into a single table (5 columns
wide) from two worksheets. The problem I am having is
that the number of rows on both lists is going to vary
over time and I need to pull it into a single list
(without blank rows). I know there is an easy way to do
this without using a macro, but I can't seem to figure it
out. I have tried using something like this, but without
success:

If(isblank(Sheet1!a2),(sheet2!a2),(sheet1!a2))

What I really need is a formula that will recognize when I
have reached the last row of the first sheet and need to
swtich over to the second sheet.

Any ideas?

combining data from two sources
 

=CountA(Sheet1!A:A)

will tell you how many rows in Column A contain data.

Row()

will tell you want row your formula is in. So you can use an if statement
that compares the row with the formula to the count of the rows in the first
table. If less or equal to, look at the first table. If more, then look at
the second table.

--
Regards,
Tom Ogilvy

"M" wrote in message
...
I am trying to compile data into a single table (5 columns
wide) from two worksheets. The problem I am having is
that the number of rows on both lists is going to vary
over time and I need to pull it into a single list
(without blank rows). I know there is an easy way to do
this without using a macro, but I can't seem to figure it
out. I have tried using something like this, but without
success:

If(isblank(Sheet1!a2),(sheet2!a2),(sheet1!a2))

What I really need is a formula that will recognize when I
have reached the last row of the first sheet and need to
swtich over to the second sheet.

Any ideas?

combining data from two sources
 

I have done something similar but am still unsure of how
to complete the formula. I have created a column that
identifies the rows in table one as either containing a
value or blank. I then created an if statement in my
desired table that says, If(M2="Value",Table1!a2,Table2!
$a$2) and pasted this all the way down. This pulls in all
of the values for the first table and the first value of
the second table, but I am not sure how to get the rest of
the rows from the second table into the sheet. Do you
follow?


-----Original Message-----
=CountA(Sheet1!A:A)

will tell you how many rows in Column A contain data.

Row()

will tell you want row your formula is in. So you can
use an if statement
that compares the row with the formula to the count of
the rows in the first
table. If less or equal to, look at the first table. If
more, then look at
the second table.

--
Regards,
Tom Ogilvy

"M" wrote in message
...
I am trying to compile data into a single table (5
columns
wide) from two worksheets. The problem I am having is
that the number of rows on both lists is going to vary
over time and I need to pull it into a single list
(without blank rows). I know there is an easy way to do
this without using a macro, but I can't seem to figure
it
out. I have tried using something like this, but
without
success:

If(isblank(Sheet1!a2),(sheet2!a2),(sheet1!a2))

What I really need is a formula that will recognize
when I
have reached the last row of the first sheet and need to
swtich over to the second sheet.

Any ideas?



.

combining data from two sources
 

Contact me directly and I will see if I can send you a sample workbook.

You are now saying that you want to skip rows in your tables that are blank?



--
Regards,
Tom Ogilvy

"M" wrote in message
...
I have done something similar but am still unsure of how
to complete the formula. I have created a column that
identifies the rows in table one as either containing a
value or blank. I then created an if statement in my
desired table that says, If(M2="Value",Table1!a2,Table2!
$a$2) and pasted this all the way down. This pulls in all
of the values for the first table and the first value of
the second table, but I am not sure how to get the rest of
the rows from the second table into the sheet. Do you
follow?


-----Original Message-----
=CountA(Sheet1!A:A)

will tell you how many rows in Column A contain data.

Row()

will tell you want row your formula is in. So you can
use an if statement
that compares the row with the formula to the count of
the rows in the first
table. If less or equal to, look at the first table. If
more, then look at
the second table.

--
Regards,
Tom Ogilvy

"M" wrote in message
...
I am trying to compile data into a single table (5
columns
wide) from two worksheets. The problem I am having is
that the number of rows on both lists is going to vary
over time and I need to pull it into a single list
(without blank rows). I know there is an easy way to do
this without using a macro, but I can't seem to figure
it
out. I have tried using something like this, but
without
success:

If(isblank(Sheet1!a2),(sheet2!a2),(sheet1!a2))

What I really need is a formula that will recognize
when I
have reached the last row of the first sheet and need to
swtich over to the second sheet.

Any ideas?



.