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




skip every other row when copying formula
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. 
Ads 
#2




skip every other row when copying formula
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" > wrote in message ... >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




skip every other row when copying formula
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. 
#4




skip every other row when copying formula
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" > wrote in message ... >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




skip every other row when copying formula
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" > wrote in message > ... > >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




skip every other row when copying formula
Try:
=IF(LEN(B1)>0,"",INDIRECT("A"&INT(ROW(B2)/2)+1))  Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy  "RHD3" > wrote in message ... > 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" > wrote in message >> ... >> >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




skip every other row when copying formula
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" > wrote in message > ... > > 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" > wrote in message > >> ... > >> >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. > >> > >> > >> > > > 
#8




skip every other row when copying formula
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" > wrote in message > ... > > 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" > wrote in message > >> ... > >> >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. > >> > >> > >> > > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Copying a formula to multiple worksheets  RobHan  Excel Worksheet Functions  1  February 16th 06 05:21 PM 
Returned: Copying a formula horizontally, the source data is verti  Shannon  Excel Discussion (Misc queries)  5  December 21st 05 10:27 PM 
Formula checking multiple worksheets  sonicthemouse  Excel Worksheet Functions  2  June 5th 05 07:48 PM 
Copying result of formula into another worksheet???  Amanda  Excel Discussion (Misc queries)  3  April 15th 05 09:40 PM 
Copying a formula and changing ONE value...  JSA  Excel Worksheet Functions  2  February 18th 05 05:13 AM 