|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 13th, 2009, 05:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 
|
|
#2
April 13th, 2009, 06:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 news  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.
|
|
#3
April 13th, 2009, 06:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#4
April 13th, 2009, 06:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#5
April 13th, 2009, 06:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#6
April 13th, 2009, 06:52 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#7
April 13th, 2009, 07:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#8
April 14th, 2009, 01:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 news 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
