Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row jumping formula?
Hi, reasonably simple one...I have some data in cells within one column but
separated by rows each time, so for example the figures are in BI5, BI8, BI11, BI12 etc On another worksheet I want to condense this data - so essentially get rid of the gaps in between so it would read BI5, BI6, BI7 etc - the sample size is so large removing rows would be extremely time consuming, all i want really is a formula that references the figure, and as I copy it down in the new column it will reference every 3rd cell in the range |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row jumping formula?
If i didn't miss anything
on another worksheet in A1 =OFFSET(Sheet1!$A$5,3*(ROW()-1),) then copy/drag down On 21 Maj, 14:26, GD wrote: Hi, reasonably simple one...I have some data in cells within one column but separated by rows each time, so for example the figures are in BI5, BI8, BI11, BI12 etc On another worksheet I want to condense this data - so essentially get rid of the gaps in between so it would read BI5, BI6, BI7 etc - the sample size is so large removing rows would be extremely time consuming, all i want really is a formula that references the figure, and as I copy it down in the new column it will reference every 3rd cell in the range |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row jumping formula?
Try the below and then you can work it out...(every 3rd cell)
Enter few values in A5, A8, A11, A14 etc; In cell B5 enter the formula =A5 In B6 enter the below formula and copy down = INDIRECT("A"&ROW($B$5)+(ROW()-ROW($B$5))*3) -- If this post helps click Yes --------------- Jacob Skaria "GD" wrote: Hi, reasonably simple one...I have some data in cells within one column but separated by rows each time, so for example the figures are in BI5, BI8, BI11, BI12 etc On another worksheet I want to condense this data - so essentially get rid of the gaps in between so it would read BI5, BI6, BI7 etc - the sample size is so large removing rows would be extremely time consuming, all i want really is a formula that references the figure, and as I copy it down in the new column it will reference every 3rd cell in the range |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row jumping formula?
Hi,
You can easily remove the blank cells with a macro Sub Macro1() ' ' Macro1 Macro ' Dim myrange, MyRange1 As Range lastRow = Cells(Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Sheet1").Range("A1:BI" & lastRow) If Range("A1") = "" Then myrange.Delete End If End Sub "GD" wrote: Hi, reasonably simple one...I have some data in cells within one column but separated by rows each time, so for example the figures are in BI5, BI8, BI11, BI12 etc On another worksheet I want to condense this data - so essentially get rid of the gaps in between so it would read BI5, BI6, BI7 etc - the sample size is so large removing rows would be extremely time consuming, all i want really is a formula that references the figure, and as I copy it down in the new column it will reference every 3rd cell in the range |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row jumping formula?
In Sheet1 we have data in B5,B8,B11,B14,B17,...
In Sheet2, pick a cell and enter: =INDIRECT("Sheet1!B" & 3*ROWS(A1:$A$1)+2) and copy down -- Gary''s Student - gsnu200854 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
JUMPING TO THE NEAREST FORMULA CONTAINING CELL | Excel Discussion (Misc queries) | |||
Jumping Scroll | Excel Discussion (Misc queries) | |||
Jumping reference cell in dependent cell formula | Excel Worksheet Functions | |||
jumping around | Excel Discussion (Misc queries) | |||
Jumping columns | Excel Discussion (Misc queries) |