Tags: column, copying, example, excel, formula, microsoft, msdn, row, skip, software

skip every other row when copying formula

On Microsoft » Microsoft Excel

10,685 words with 7 Comments; publish: Thu, 22 May 2008 18:01:00 GMT; (30693.75, « »)

I would like to be able to skip every other row when copying a formula. If I

have data in column A for example, and I want to use a simple formula in

column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell b5)

and so on. I have tried using the drag and fill but I end up missing cells.

All Comments

Leave a comment...

  • 7 Comments
    • Assume your data in column A is:

      11

      22

      33

      44

      55

      66

      77

      88

      99

      11 is in cell A1 and 99 in in cell A11

      In B1 put =A1

      In cell B2 put:

      =IF(ISNUMBER(B1),"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      Then drag it down.

      In cell B1 you get 11 (obviously)

      B2, B4, B6 and B8 are blank

      B3 shows 22

      B5 shows 33

      B7 shows 44 and

      B9 shows 55

      --

      Andy Wiggins FCCA

      www.BygSoftware.com

      Excel, Access and VBA Consultancy

      -

      "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      >I would like to be able to skip every other row when copying a formula. If

      >I

      > have data in column A for example, and I want to use a simple formula in

      > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      > b5)

      > and so on. I have tried using the drag and fill but I end up missing

      > cells.

      #1; Thu, 22 May 2008 18:02:00 GMT
    • I think you want ...

      A B

      1 =A1

      2

      3 =A2

      4

      5 =A4

      if that is the problem,

      You just copy B3, and paste it to remaining cells in B

      That's it.

      *** Please do rate ***

      "RHD3" wrote:

      > I would like to be able to skip every other row when copying a formula. If I

      > have data in column A for example, and I want to use a simple formula in

      > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell b5)

      > and so on. I have tried using the drag and fill but I end up missing cells.

      #2; Thu, 22 May 2008 18:03:00 GMT
    • Type formula in the first cell and nothing in the cell below.

      Select these two cells; use copy

      Move to Third cell and select to end of range; use Paste

      best wishes

      --

      Bernard V Liengme

      www.stfx.ca/people/bliengme

      remove caps from email

      "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      >I would like to be able to skip every other row when copying a formula. If

      >I

      > have data in column A for example, and I want to use a simple formula in

      > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      > b5)

      > and so on. I have tried using the drag and fill but I end up missing

      > cells.

      #3; Thu, 22 May 2008 18:04:00 GMT
    • Thanks for the reply. It works for numbers, but my cell "entries" are text.

      I can't get it to work with text.

      My column A is similar to:

      \\test\ie\v03\0123

      \\test\ie\v03\0456

      etc.

      "Andy Wiggins" wrote:

      > Assume your data in column A is:

      > 11

      > 22

      > 33

      > 44

      > 55

      > 66

      > 77

      > 88

      > 99

      >

      > 11 is in cell A1 and 99 in in cell A11

      > In B1 put =A1

      > In cell B2 put:

      > =IF(ISNUMBER(B1),"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      > Then drag it down.

      > In cell B1 you get 11 (obviously)

      > B2, B4, B6 and B8 are blank

      > B3 shows 22

      > B5 shows 33

      > B7 shows 44 and

      > B9 shows 55

      > --

      > Andy Wiggins FCCA

      > www.BygSoftware.com

      > Excel, Access and VBA Consultancy

      > -

      > "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      > >I would like to be able to skip every other row when copying a formula. If

      > >I

      > > have data in column A for example, and I want to use a simple formula in

      > > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      > > b5)

      > > and so on. I have tried using the drag and fill but I end up missing

      > > cells.

      >

      >

      #4; Thu, 22 May 2008 18:05:00 GMT
    • Try:

      =IF(LEN(B1)>0,"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      --

      Andy Wiggins FCCA

      www.BygSoftware.com

      Excel, Access and VBA Consultancy

      -

      "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:7F2B242C-2F15-49CE-9B9B-2BEB8269E5A1.excel.todaysummary.com.microsoft.com...

      > Thanks for the reply. It works for numbers, but my cell "entries" are

      > text.

      > I can't get it to work with text.

      > My column A is similar to:

      > \\test\ie\v03\0123

      > \\test\ie\v03\0456

      > etc.

      > "Andy Wiggins" wrote:

      >> Assume your data in column A is:

      >> 11

      >> 22

      >> 33

      >> 44

      >> 55

      >> 66

      >> 77

      >> 88

      >> 99

      >>

      >> 11 is in cell A1 and 99 in in cell A11

      >> In B1 put =A1

      >> In cell B2 put:

      >> =IF(ISNUMBER(B1),"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      >> Then drag it down.

      >> In cell B1 you get 11 (obviously)

      >> B2, B4, B6 and B8 are blank

      >> B3 shows 22

      >> B5 shows 33

      >> B7 shows 44 and

      >> B9 shows 55

      >> --

      >> Andy Wiggins FCCA

      >> www.BygSoftware.com

      >> Excel, Access and VBA Consultancy

      >> -

      >> "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      >> >I would like to be able to skip every other row when copying a formula.

      >> >If

      >> >I

      >> > have data in column A for example, and I want to use a simple formula

      >> > in

      >> > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      >> > b5)

      >> > and so on. I have tried using the drag and fill but I end up missing

      >> > cells.

      >>

      #5; Thu, 22 May 2008 18:06:00 GMT
    • YES!!!!!! Thanks. It works great.

      "Andy Wiggins" wrote:

      > Try:

      > =IF(LEN(B1)>0,"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      > --

      > Andy Wiggins FCCA

      > www.BygSoftware.com

      > Excel, Access and VBA Consultancy

      > -

      > "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:7F2B242C-2F15-49CE-9B9B-2BEB8269E5A1.excel.todaysummary.com.microsoft.com...

      > > Thanks for the reply. It works for numbers, but my cell "entries" are

      > > text.

      > > I can't get it to work with text.

      > >

      > > My column A is similar to:

      > >

      > > \\test\ie\v03\0123

      > > \\test\ie\v03\0456

      > > etc.

      > >

      > > "Andy Wiggins" wrote:

      > >

      > >> Assume your data in column A is:

      > >> 11

      > >> 22

      > >> 33

      > >> 44

      > >> 55

      > >> 66

      > >> 77

      > >> 88

      > >> 99

      > >>

      > >>

      > >> 11 is in cell A1 and 99 in in cell A11

      > >>

      > >> In B1 put =A1

      > >> In cell B2 put:

      > >> =IF(ISNUMBER(B1),"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      > >> Then drag it down.

      > >>

      > >> In cell B1 you get 11 (obviously)

      > >> B2, B4, B6 and B8 are blank

      > >> B3 shows 22

      > >> B5 shows 33

      > >> B7 shows 44 and

      > >> B9 shows 55

      > >>

      > >> --

      > >> Andy Wiggins FCCA

      > >> www.BygSoftware.com

      > >> Excel, Access and VBA Consultancy

      > >> -

      > >>

      > >> "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      > >> >I would like to be able to skip every other row when copying a formula.

      > >> >If

      > >> >I

      > >> > have data in column A for example, and I want to use a simple formula

      > >> > in

      > >> > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      > >> > b5)

      > >> > and so on. I have tried using the drag and fill but I end up missing

      > >> > cells.

      > >>

      > >>

      > >>

      >

      >

      #6; Thu, 22 May 2008 18:08:00 GMT
    • Yes!!!!!!!!!!!! Thanks.

      "Andy Wiggins" wrote:

      > Try:

      > =IF(LEN(B1)>0,"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      > --

      > Andy Wiggins FCCA

      > www.BygSoftware.com

      > Excel, Access and VBA Consultancy

      > -

      > "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:7F2B242C-2F15-49CE-9B9B-2BEB8269E5A1.excel.todaysummary.com.microsoft.com...

      > > Thanks for the reply. It works for numbers, but my cell "entries" are

      > > text.

      > > I can't get it to work with text.

      > >

      > > My column A is similar to:

      > >

      > > \\test\ie\v03\0123

      > > \\test\ie\v03\0456

      > > etc.

      > >

      > > "Andy Wiggins" wrote:

      > >

      > >> Assume your data in column A is:

      > >> 11

      > >> 22

      > >> 33

      > >> 44

      > >> 55

      > >> 66

      > >> 77

      > >> 88

      > >> 99

      > >>

      > >>

      > >> 11 is in cell A1 and 99 in in cell A11

      > >>

      > >> In B1 put =A1

      > >> In cell B2 put:

      > >> =IF(ISNUMBER(B1),"",INDIRECT("A"&INT(ROW(B2)/2)+1))

      > >> Then drag it down.

      > >>

      > >> In cell B1 you get 11 (obviously)

      > >> B2, B4, B6 and B8 are blank

      > >> B3 shows 22

      > >> B5 shows 33

      > >> B7 shows 44 and

      > >> B9 shows 55

      > >>

      > >> --

      > >> Andy Wiggins FCCA

      > >> www.BygSoftware.com

      > >> Excel, Access and VBA Consultancy

      > >> -

      > >>

      > >> "RHD3" <RHD3.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:CA7EE1AC-9227-4948-8557-D7B0CA017A66.excel.todaysummary.com.microsoft.com...

      > >> >I would like to be able to skip every other row when copying a formula.

      > >> >If

      > >> >I

      > >> > have data in column A for example, and I want to use a simple formula

      > >> > in

      > >> > column B like "=a1"(in cell b1) and "=a2"(in cell b3) and "=a3"(in cell

      > >> > b5)

      > >> > and so on. I have tried using the drag and fill but I end up missing

      > >> > cells.

      > >>

      > >>

      > >>

      >

      >

      #7; Thu, 22 May 2008 18:08:00 GMT