Help with INDIRECT
 

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

Try this:

Names in Sheet1 A1:An

Enter this formula on another sheet in cell A1 and copy down as needed:

=INDEX(Sheet1!A:A,CEILING(ROWS(A$1:A1)/2,1))

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

Thank you!!!! It worked but one other ?. What if the list of names on Sheet1
begins on row 8 intead of row 2. How would I adjust the formulas to begin
looking on a specified row?

"Jacob Skaria" wrote:

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

=INDIRECT("Sheet1!A" & 7 + ROUNDUP(ROW()/2,0),TRUE)

If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

Thank you!!!! It worked but one other ?. What if the list of names on Sheet1
begins on row 8 intead of row 2. How would I adjust the formulas to begin
looking on a specified row?

"Jacob Skaria" wrote:

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

=INDEX(Sheet1!A$8:A$100,CEILING(ROWS(A$1:A1)/2,1))

Adjust for the correct end of range A100

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
Thank you!!!! It worked but one other ?. What if the list of names on
Sheet1
begins on row 8 intead of row 2. How would I adjust the formulas to begin
looking on a specified row?

"Jacob Skaria" wrote:

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to
list
the first name twice (Jones) and then go the next name in the list
(Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

Wow!! Awesome!!! Thank you!! Much Appreciated!

"Jacob Skaria" wrote:

=INDIRECT("Sheet1!A" & 7 + ROUNDUP(ROW()/2,0),TRUE)

If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

Thank you!!!! It worked but one other ?. What if the list of names on Sheet1
begins on row 8 intead of row 2. How would I adjust the formulas to begin
looking on a specified row?

"Jacob Skaria" wrote:

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes
---------------
Jacob Skaria


"GoBucks" wrote:

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

Help with INDIRECT
 

Hi,

You may also use this approach (no formulas)

In another sheet, link A2:A5 to the range of names in column A of sheet 1.
In B2:B5, enter 1,2,3,4. In B6:B9, enter 1,2,3,4. In A1 and B1 type Names
and Nos. Now highlight A1:B5 and sort on column B. This will insert a
blank row after each entry. Now highlight C5:C12 and press Ctrl+G Special
Blanks. Now press = up arrow key Ctrl+Enter

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"GoBucks" wrote in message
...
I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.