Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing formula
Hi When I write a formula for a cell and the click and drag to adjacent cells, excel advances the formula in a certain manner. I am using person's names. For example =Barker!Z11 becomes =Barker!Z12 in the next cell. Is there anyway of making excel advance the formula in a certain way? In this case I want it to advance by 7 so the formula for adjacent cells reads =Barker!Z18 and =Barker!Z25 and then =Barker!Z32 and so on? Also can you rename part of a formula across a range of cells so that Barker is replaced by Ball. In other words =Barker!Z11 becomes =Ball!Z11 etc... thanks Steve -- spankydata ------------------------------------------------------------------------ spankydata's Profile: http://www.excelforum.com/member.php...o&userid=37217 View this thread: http://www.excelforum.com/showthread...hreadid=569315 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing formula
spankydata wrote...
When I write a formula for a cell and the click and drag to adjacent cells, excel advances the formula in a certain manner. I am using person's names. For example =Barker!Z11 becomes =Barker!Z12 in the next cell. Is there anyway of making excel advance the formula in a certain way? In this case I want it to advance by 7 so the formula for adjacent cells reads =Barker!Z18 and =Barker!Z25 and then =Barker!Z32 and so on? Not using simple cell references. Excel provides only 1-to-1 correspondence, meaning fill formulas +/-1 row/column over, and cell addresses adjust +/-1 row/column. You could use INDEX. If the first formula were in cell X99, X99: =INDEX(Barker!Z:Z,11+7*ROWS(X$99:X99)) The 2nd argument evaluates to 18. Fill it down one row, and that INDEX call's 2nd argument evaluates to 25. Also can you rename part of a formula across a range of cells so that Barker is replaced by Ball. In other words =Barker!Z11 becomes =Ball!Z11 etc... No, but if you're in a cell from which you want to refer to Barker!Z11, you could define the name Ball referring to =Barker!Z:Z, then change the INDEX formula above to X99: =INDEX(Ball,11+7*ROWS(X$99:X99)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing formula
Hi!
To increment the formula reference: (I assume you're copying DOWN, not across) =INDEX(Barker!Z$11:Z$100,(ROWS($1:1)-1)*7+1) Adjust for the end of the range as needed. Also can you rename part of a formula across a range of cells so that Barker is replaced by Ball. In other words =Barker!Z11 becomes =Ball!Z11 etc... Use EditReplace for that. Biff "spankydata" wrote in message ... Hi When I write a formula for a cell and the click and drag to adjacent cells, excel advances the formula in a certain manner. I am using person's names. For example =Barker!Z11 becomes =Barker!Z12 in the next cell. Is there anyway of making excel advance the formula in a certain way? In this case I want it to advance by 7 so the formula for adjacent cells reads =Barker!Z18 and =Barker!Z25 and then =Barker!Z32 and so on? Also can you rename part of a formula across a range of cells so that Barker is replaced by Ball. In other words =Barker!Z11 becomes =Ball!Z11 etc... thanks Steve -- spankydata ------------------------------------------------------------------------ spankydata's Profile: http://www.excelforum.com/member.php...o&userid=37217 View this thread: http://www.excelforum.com/showthread...hreadid=569315 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing formula
thanks very much. What does index do in general terms? -- spankydata ------------------------------------------------------------------------ spankydata's Profile: http://www.excelforum.com/member.php...o&userid=37217 View this thread: http://www.excelforum.com/showthread...hreadid=569315 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |