| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Skipping cells in a formula
Howdy All,
I want to get data from another worksheet, but the data I want occurs in every 3rd row. So I want data from C2, C5, C8, etc. How can I do this in a formula so that when I drop the formula down, the sequence will replace correctly? Thanks, Brian |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Skipping cells in a formula
On Jun 6, 7:26 pm, "Brian" wrote:
Howdy All, I want to get data from another worksheet, but the data I want occurs in every 3rd row. So I want data from C2, C5, C8, etc. How can I do this in a formula so that when I drop the formula down, the sequence will replace correctly? Thanks, Brian In any cell (say B2): =OFFSET('[otherbook.xls]sheetname'!$C$2,(ROW()-ROW($B$2))*3,0) HTH Kostis Vezerides |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Skipping cells in a formula
Thanks Kostis,
That works great! I have used another formula to extract 2 piece of data from those specific cells which occur before and after this text "--" Those formulas a For data before -- =LEFT(Sheet3!C2,FIND("--",Sheet3!C2)-1) For data after -- =RIGHT(Sheet3!C2,(LEN(Sheet3!C2)-FIND("--",Sheet3!C2))-2) Can you help me integrate your formula into these formulas? Thanks again, Brian "vezerid" wrote in message ups.com... On Jun 6, 7:26 pm, "Brian" wrote: Howdy All, I want to get data from another worksheet, but the data I want occurs in every 3rd row. So I want data from C2, C5, C8, etc. How can I do this in a formula so that when I drop the formula down, the sequence will replace correctly? Thanks, Brian In any cell (say B2): =OFFSET('[otherbook.xls]sheetname'!$C$2,(ROW()-ROW($B$2))*3,0) HTH Kostis Vezerides |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Skipping cells in a formula
Basically you replace Sheet3!C2 with the construct I provided
OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0) Thus: =LEFT(OFFSET(Sheet3!$C$2,(ROW()-ROW($B $2))*3,0),FIND("--",OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0))-1) =RIGHT(OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0) ,(LEN(OFFSET(Sheet3!$C $2,(ROW()-ROW($B$2))*3,0) )-FIND("--",OFFSET(Sheet3!$C$2,(ROW()-ROW($B $2))*3,0) ))-2) Regards, Kostis On Jun 6, 7:48 pm, "Brian" wrote: Thanks Kostis, That works great! I have used another formula to extract 2 piece of data from those specific cells which occur before and after this text "--" Those formulas a For data before -- =LEFT(Sheet3!C2,FIND("--",Sheet3!C2)-1) For data after -- =RIGHT(Sheet3!C2,(LEN(Sheet3!C2)-FIND("--",Sheet3!C2))-2) Can you help me integrate your formula into these formulas? Thanks again, Brian "vezerid" wrote in message ups.com... On Jun 6, 7:26 pm, "Brian" wrote: Howdy All, I want to get data from another worksheet, but the data I want occurs in every 3rd row. So I want data from C2, C5, C8, etc. How can I do this in a formula so that when I drop the formula down, the sequence will replace correctly? Thanks, Brian In any cell (say B2): =OFFSET('[otherbook.xls]sheetname'!$C$2,(ROW()-ROW($B$2))*3,0) HTH Kostis Vezerides |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Skipping cells in a formula
Thanks again, Kostis!
"vezerid" wrote in message oups.com... Basically you replace Sheet3!C2 with the construct I provided OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0) Thus: =LEFT(OFFSET(Sheet3!$C$2,(ROW()-ROW($B $2))*3,0),FIND("--",OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0))-1) =RIGHT(OFFSET(Sheet3!$C$2,(ROW()-ROW($B$2))*3,0) ,(LEN(OFFSET(Sheet3!$C $2,(ROW()-ROW($B$2))*3,0) )-FIND("--",OFFSET(Sheet3!$C$2,(ROW()-ROW($B $2))*3,0) ))-2) Regards, Kostis On Jun 6, 7:48 pm, "Brian" wrote: Thanks Kostis, That works great! I have used another formula to extract 2 piece of data from those specific cells which occur before and after this text "--" Those formulas a For data before -- =LEFT(Sheet3!C2,FIND("--",Sheet3!C2)-1) For data after -- =RIGHT(Sheet3!C2,(LEN(Sheet3!C2)-FIND("--",Sheet3!C2))-2) Can you help me integrate your formula into these formulas? Thanks again, Brian "vezerid" wrote in message ups.com... On Jun 6, 7:26 pm, "Brian" wrote: Howdy All, I want to get data from another worksheet, but the data I want occurs in every 3rd row. So I want data from C2, C5, C8, etc. How can I do this in a formula so that when I drop the formula down, the sequence will replace correctly? Thanks, Brian In any cell (say B2): =OFFSET('[otherbook.xls]sheetname'!$C$2,(ROW()-ROW($B$2))*3,0) HTH Kostis Vezerides |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
| skipping cells | Excel Discussion (Misc queries) | |||
| repeat a formula, skipping cells | Excel Discussion (Misc queries) | |||
| Subtraction formula for consecutive cells in a column, skipping blanks | Excel Worksheet Functions | |||
| Autofill skipping cells | Excel Worksheet Functions | |||
Linear Mode
