Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
I am trying to refference specific cells from one worksheet to another. The
information I need is not in consecutive cells, they are, for example, 4 cells apart. My problem is I have over 8000 cells that need filling and I am not about to do that by hand :) When I put a few entries in then try to have the pattern propogate it doesn't work. instead it backtracks. for example, if I entered 4 cells: salary!g4 salary!g8 salary!g12 salary!g16 when i try to propogate i get a patern like: salary!g8 salary!g12 salary!g16 salary!g20 salary!g12 salary!g16 salary!g20 salary!g24 How do i make the pattern consistant over the 8000 cells? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
One way ..
Put this in the starting cell, say, in B2: =INDIRECT("'salary'!G"&ROW(A1)*4) B2 returns the same as: =salary!G4 Just copy B2 down as far as required* to return the desired: salary!g4 salary!g8 salary!g12 salary!g16 .... *down to B2001 thereabouts, since you have 8000 cells .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Daiv" wrote: I am trying to refference specific cells from one worksheet to another. The information I need is not in consecutive cells, they are, for example, 4 cells apart. My problem is I have over 8000 cells that need filling and I am not about to do that by hand :) When I put a few entries in then try to have the pattern propogate it doesn't work. instead it backtracks. for example, if I entered 4 cells: salary!g4 salary!g8 salary!g12 salary!g16 when i try to propogate i get a patern like: salary!g8 salary!g12 salary!g16 salary!g20 salary!g12 salary!g16 salary!g20 salary!g24 How do i make the pattern consistant over the 8000 cells? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
Thanks a bunch. that worked well.
One more though, I can't seem tofigure it out from your last post. I have another worksheet that takes its first value from salary!H4, and the next one is at salary!H100, then salary!H196 (and every 196 thereafter) I can't seem to get that one to work. maybe It's because I don't fully understand the formula in your last post. Thanks! "Max" wrote: One way .. Put this in the starting cell, say, in B2: =INDIRECT("'salary'!G"&ROW(A1)*4) B2 returns the same as: =salary!G4 Just copy B2 down as far as required* to return the desired: salary!g4 salary!g8 salary!g12 salary!g16 ... *down to B2001 thereabouts, since you have 8000 cells .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Daiv" wrote: I am trying to refference specific cells from one worksheet to another. The information I need is not in consecutive cells, they are, for example, 4 cells apart. My problem is I have over 8000 cells that need filling and I am not about to do that by hand :) When I put a few entries in then try to have the pattern propogate it doesn't work. instead it backtracks. for example, if I entered 4 cells: salary!g4 salary!g8 salary!g12 salary!g16 when i try to propogate i get a patern like: salary!g8 salary!g12 salary!g16 salary!g20 salary!g12 salary!g16 salary!g20 salary!g24 How do i make the pattern consistant over the 8000 cells? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
"Daiv" wrote:
Thanks a bunch. that worked well. You're welcome ! I have another worksheet that takes its first value from salary!H4, and the next one is at salary!H100, then salary!H196 (and every 96 thereafter) [Interval typo slightly corrected to 96 <g] Put this in the starting cell, and copy down: =INDIRECT("'salary'!H"&ROW(A1)*96-92) The construct is similar to the previous except for the multiplier change from 4 to 96 and the need for a simple arithmetic adjustment since the starting cell is to point to salary!H4 (so we subtract 92 from 96). Tinker with this to see what's happening when we copy down .. Place this in any cell, say C3: =ROW(A1)*96-92 C1 will return as: = (1 x 96) - 92 = 4 ( ROW(A1) resolves to: 1 ) When we copy C1 down to C2, the formula will increment* to: =ROW(A2)*96-92 which now returns: = (2 x 96) - 92 = 100 ( ROW(A2) resolves to: 2 ) *ROW(A1) becomes ROW(A2), and so on as we copy down ... The above hence generates the required number series: 4, 100, 196, ... for concatenation as the row refs within the INDIRECT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
Oops, typo in line:
Place this in any cell, say C3: =ROW(A1)*96-92 should read as: Place this in any cell, say C1: =ROW(A1)*96-92 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
Your a life saver Max.
Thank you for the explanation, it was very helpfull! Daiv. "Max" wrote: "Daiv" wrote: Thanks a bunch. that worked well. You're welcome ! I have another worksheet that takes its first value from salary!H4, and the next one is at salary!H100, then salary!H196 (and every 96 thereafter) [Interval typo slightly corrected to 96 <g] Put this in the starting cell, and copy down: =INDIRECT("'salary'!H"&ROW(A1)*96-92) The construct is similar to the previous except for the multiplier change from 4 to 96 and the need for a simple arithmetic adjustment since the starting cell is to point to salary!H4 (so we subtract 92 from 96). Tinker with this to see what's happening when we copy down .. Place this in any cell, say C3: =ROW(A1)*96-92 C1 will return as: = (1 x 96) - 92 = 4 ( ROW(A1) resolves to: 1 ) When we copy C1 down to C2, the formula will increment* to: =ROW(A2)*96-92 which now returns: = (2 x 96) - 92 = 100 ( ROW(A2) resolves to: 2 ) *ROW(A1) becomes ROW(A2), and so on as we copy down ... The above hence generates the required number series: 4, 100, 196, ... for concatenation as the row refs within the INDIRECT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Propogation
"Daiv" wrote:
Your a life saver Max. Thank you for the explanation, it was very helpfull! Glad it was, Daiv ! You're welcome. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |