# Combine two dynamic range from different worksheet into a third worksheet

#### khyati_p

##### New Member
I have a dynamic range in sheet 1 and another dynamic range in sheet 2. In sheet 3 I want to combine them.

Now how I want to combine them in sheet 3 ?

I want the dynamic range from sheet one first and then immediately from the next row I want the dynamic range from sheet two to begin. How can I do this?

Help !

#### Hui

##### Excel Ninja
Staff member
Khyati_p

I have setup 2 dynamic ranges in Columns A & B of a sheet

They are Called A and B

Each has a named area

RngA=Offset(\$A\$1,0,0,Counta(\$A\$1:\$A\$100),1)

RngB=Offset(\$B\$1,0,0,Counta(\$B\$1:\$B\$100),1)

In column G, I have put the following and copied down

=+IF(ROW()&#62;COUNTA(RngA,RngB),"",IF(ROW()-ROW(\$G\$1)+1&#60;=COUNTA(RngA),INDEX(RngA,ROW()),INDEX(RngB,ROW()-COUNTA(RngA))))

The first part "IF(ROW()&#62;COUNTA(RngA,RngB)" tests whether the current row is past the end of the length of the 2 ranges and if it is it puts a blank ""

If not it then checks which Range to use ROW()-ROW(\$G\$1)+1&#60;=COUNTA(RngA)

and if it is the first range it puts the value from the Range based on the position as determined by the current row using

INDEX(RngA,ROW())

If it is the second range it has to allow for the length of the first range

INDEX(RngB,ROW()-COUNTA(RngA)

As you are using named ranges, the use of different sheets will be automagic

Good luck

#### khyati_p

##### New Member
Hui

Thanks a ton for the help. The formula works perfectly fine except a minor glitch.

RngA=Offset(\$A\$1,0,0,Counta(\$A\$2:\$A\$1000),1)

RngB=Offset(\$B\$1,0,0,Counta(\$B\$1:\$B\$1000),1)

These are my named ranges. I have taken 1000 rows.

Out of these 1000 rows there is data only in 640 rows( I am considering 1000 as in future it might be more than 640).

What the formula in G column is doing is that it picks up 1000 rows from column A and then 1000 rows from column B.

Even though the data ends in row 640 - From row 6410 to 1000 its all blank and then from 10001 row it starts pulling data from column B.

Its only for two column that this problem is occuring. For the rest of the columns even though the range is defined till 1000 data gets picked up till 640.

I dont know why two particular columns are giving me problem. Those columns has numbers stored as text. Is this reason?

#### Hui

##### Excel Ninja
Staff member
I have just remade this cut/pasting the formulas above and it works ok in both Excel 2003 & 2007/10

When you say the named ranges are RngA

Was RngA made in the Name Manager in

Excel 2003 - Insert Name, Define ?; or

Excel 2007 - Formula Tab, Define Name ?

or how else did you make the named range ?

In the Name Manager, select the RngA or RngB name and click on the formula at the Bottom of the Window, Does it highlight the area a1:a640 ?

It will/should work regardless of the Values, Text or Numbers

One small point is that

RngA=Offset(\$A\$1,0,0,Counta(\$A\$2:\$A\$1000),1) will miss the value in A640 as it is offset from 1 value above what you are counting

it should either be

RngA=Offset(\$A\$1,0,0,Counta(\$A\$1:\$A\$1000),1) or

RngA=Offset(\$A\$2,0,0,Counta(\$A\$2:\$A\$1000),1)

#### khyati_p

##### New Member
Its working everywhere except two columns...I dont know why !!

I am using Insert-Name-Define

This is my range K in column A of a sheet called 1st Part of JE

=OFFSET('Ist Part of JE'!\$A\$2,0,0,COUNTA('Ist Part of JE'!\$A\$2:\$A\$1000),1)

This is my range L in column A of a sheet called 2nd Part of JE

=OFFSET('2nd Part of JE'!\$A\$1,0,0,COUNTA('2nd Part of JE'!\$A\$1:\$A\$1000),1)

And In a third worksheet called JE Template I have this formula in column A

=+IF(ROW()&#62;COUNTA(K,L),"",IF(ROW()-ROW(\$A\$1)+1&#60;=COUNTA(K),INDEX(K,ROW()),INDEX(L,ROW()-COUNTA(K))))

Problem - Range K and L is till Row 1000 but the data is till row 340 in each. I mean Range K has 1000 rows but data is still 340 and range L has 100 rows but the data is till 340.

In my third worksheet what happens is that in column A Range K occupies row 1 to 1000 - till row 340 there is data and from 341 to 999 its blank. Range L starts from row 1000. It should start from row 341 !!

I have used this formula in other columns too it works perfectly fine. Only two rows are giving me problems ..just cannot figure out why !!

#### khyati_p

##### New Member
Is there any chances that there are hidden zeroes from row 341 to 999 ? Can something be incorporated in the formula which will ingore zeroes?

#### khyati_p

##### New Member
I mean I have used this formula in various other columns ...I have created many dynamic ranges. Its only these two columns that it is taking 1000 rows.

In other columns the formula is taking data till row 340 .

#### khyati_p

##### New Member
Hui I have an idea !

I want the area of dyanmic range to be dependent on a particular cell value

Expand Down Based on Another Cell Value

Put the number 10 in cell B1 first then:

In the Refers to box type: =OFFSET(\$A\$1,0,0,\$B\$1,1)

Now change the number in cell B1 and the range will change accordingly.

Can you incorporate your formula in the above?

So that I dont have to define an area of 1000 rows. There will be an cell in my sheet which will do a countif line count on my data. Say in sell Y1 My countif shows line count of 500 than my dynamic range will automatically pull up data till row 500.

If that countif line count is 450 our dyamic range will pull data till 450.

Any chances of such formula?

#### khyati_p

##### New Member
I used this OFFSET(\$A\$1,0,0,\$B\$1,1).... I guess its working !!!

#### khyati_p

##### New Member
Hui.....Thanks a ton for the formula. You dont know how much you have helped me !!! Very grateful to you. You are fabulous.

I have made the dynamic range dependent on a countif line count and its working fine.

Once again thanks so much.

#### Hui

##### Excel Ninja
Staff member
Khyati_p

This is great

Just a few cautions

Be careful if using countif as if the if value is within the list it will prematurely cutoff the end of the data

I would go back and check that A341:A1000 is actually clear and don't contain spaces or other invisible characters like a white font on a white background, select the cells and press delete

Also look for hidden rows, they can mess up things like this, with hidden values