| Home |
| Search |
| Today's Posts |
|
#1
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
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 ... 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
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
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
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
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
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
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 ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| Indirect | Excel Worksheet Functions | |||
| Using INDIRECT | Excel Worksheet Functions | |||
| INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
| Indirect.ext #value! | Excel Discussion (Misc queries) | |||
![GoBucks[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
