|
Formula for copying data for every 8th cell
Hello,
I am looking for a formula that will allow me to copy either text or numerical data in sequence for every 8th cell. The problem that I have is that excel uses the relative/absolute reference and copies cells in sequence. Example: A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3 A1=1 B1=1 A2=2 B2=€€ A3=3€¦.. B3=€€€¦ A20=20 B8=2 B9=€€ B10=€€€¦ B16=3 Thank You, Judd |
Hi Judd
=IF(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) (Note that 1-8-16 is not every 8th) HTH. Best wishes Harald "jbsand1001" skrev i melding ... Hello, I am looking for a formula that will allow me to copy either text or numerical data in sequence for every 8th cell. The problem that I have is that excel uses the relative/absolute reference and copies cells in sequence. Example: A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3 A1=1 B1=1 A2=2 B2="" A3=3... B3="". A20=20 B8=2 B9="" B10="". B16=3 Thank You, Judd |
Harald,
Thank You very much for the formula...you are correct not every 8th but nontheless you answered my question. Thanks!!!! I would like to ask a couple of questions of how the formula works. I understand the "If" function of the formulas but I am unfamiluar with the following. =if(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) MOD-- What is this telling excel to do? ()--What does this tell excel to do? <--What does this tell excel to do? Indirect--What does this tell excel to do? 1+INT--What does this tell excel to do? ()/8--What does this tell excel to do? Thank You, Judd "Harald Staff" wrote: Hi Judd =IF(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) (Note that 1-8-16 is not every 8th) HTH. Best wishes Harald "jbsand1001" skrev i melding ... Hello, I am looking for a formula that will allow me to copy either text or numerical data in sequence for every 8th cell. The problem that I have is that excel uses the relative/absolute reference and copies cells in sequence. Example: A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3 A1=1 B1=1 A2=2 B2="" A3=3... B3="". A20=20 B8=2 B9="" B10="". B16=3 Thank You, Judd |
"jbsand1001" skrev i melding
... I would like to ask a couple of questions of how the formula works. I understand the "If" function of the formulas but I am unfamiluar with the following. =if(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) MOD-- What is this telling excel to do? Divide by n (here 8), throw the multiple of n away and return only the remainder. ()--What does this tell excel to do? It is an integrated part of the ROW() function <--What does this tell excel to do? If means "different from". a=b is the opposite of a<b Indirect--What does this tell excel to do? It creates a virtual cell address. See Help on that one. 1+INT--What does this tell excel to do? 1 and + do what they always do. INT removes decimals from a number. INT(2.5) = 2 ()/8--What does this tell excel to do? () is part of ROW. ROW()/8 divides the row number by 8. INT removes the decimal part of that division. Indirect uses "A" and that decimal-less number to return a cell address. This happens only if the remaninder of row number / 8 is 1. HTH. Best wishes Harald |
Is there a way to use this formula over a 4-5 cell area to copy
numerical/text data .....for emample: A1:A20 =1-20 B1:B20=21-40 C1:C20=41-60.... D5=1, D6=21, D7=41 D12=, D13=22, D14=42 D20=3 D21=23, D22=43....etc.... OR A1=1 B1=21 C1=41 A2=2.. B2=22.. C2=42€¦€¦ D5=1 A20=20 B20-40 C20=60 D6=21 D7=41 D8€€ D9€€ D10€€ D11€€ D12=12 D13=22 D14=42 D15€€€¦.Etc€¦. "jbsand1001" wrote: Harald, Thank You very much for the formula...you are correct not every 8th but nontheless you answered my question. Thanks!!!! I would like to ask a couple of questions of how the formula works. I understand the "If" function of the formulas but I am unfamiluar with the following. =if(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) MOD-- What is this telling excel to do? ()--What does this tell excel to do? <--What does this tell excel to do? Indirect--What does this tell excel to do? 1+INT--What does this tell excel to do? ()/8--What does this tell excel to do? Thank You, Judd "Harald Staff" wrote: Hi Judd =IF(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) (Note that 1-8-16 is not every 8th) HTH. Best wishes Harald "jbsand1001" skrev i melding ... Hello, I am looking for a formula that will allow me to copy either text or numerical data in sequence for every 8th cell. The problem that I have is that excel uses the relative/absolute reference and copies cells in sequence. Example: A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3 A1=1 B1=1 A2=2 B2="" A3=3... B3="". A20=20 B8=2 B9="" B10="". B16=3 Thank You, Judd |
It is possible to do, but pretty awkward, and also far too dependent on
cells' absolute position. Don't, it's an awful design. But if you insist or just want to learn, play around with ROW() COLUMN() INDIRECT INT MOD HTH. Best wishes Harald "jbsand1001" skrev i melding ... Is there a way to use this formula over a 4-5 cell area to copy numerical/text data .....for emample: A1:A20 =1-20 B1:B20=21-40 C1:C20=41-60.... D5=1, D6=21, D7=41 D12=, D13=22, D14=42 D20=3 D21=23, D22=43....etc.... OR A1=1 B1=21 C1=41 A2=2.. B2=22.. C2=42.. D5=1 A20=20 B20-40 C20=60 D6=21 D7=41 D8"" D9"" D10"" D11"" D12=12 D13=22 D14=42 D15""..Etc.. "jbsand1001" wrote: Harald, Thank You very much for the formula...you are correct not every 8th but nontheless you answered my question. Thanks!!!! I would like to ask a couple of questions of how the formula works. I understand the "If" function of the formulas but I am unfamiluar with the following. =if(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) MOD-- What is this telling excel to do? ()--What does this tell excel to do? <--What does this tell excel to do? Indirect--What does this tell excel to do? 1+INT--What does this tell excel to do? ()/8--What does this tell excel to do? Thank You, Judd "Harald Staff" wrote: Hi Judd =IF(MOD(ROW(),8)<1,"",INDIRECT("A"&(1+INT(ROW()/8)))) (Note that 1-8-16 is not every 8th) HTH. Best wishes Harald "jbsand1001" skrev i melding ... Hello, I am looking for a formula that will allow me to copy either text or numerical data in sequence for every 8th cell. The problem that I have is that excel uses the relative/absolute reference and copies cells in sequence. Example: A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3 A1=1 B1=1 A2=2 B2="" A3=3... B3="". A20=20 B8=2 B9="" B10="". B16=3 Thank You, Judd |
| All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com