| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for adding lines
I have 2 tabs. One spread sheet is a summury of the other.
Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
try
=indirect("Sheet2!A"&(row()-1)*5) in A2 and copy down "dpal" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
=INDIRECT("Takeoff!B34279,"&( ROW(Description)-1)*5)
Whats wrong with this? it comes up #Name? "bj" wrote: try =indirect("Sheet2!A"&(row()-1)*5) in A2 and copy down "dpal" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
the value in an indirect function has to look like a cell address
what cells are you trying to copy to where and where is the Description cell? Is Description a named cell? maybe =INDIRECT("Takeoff!B"&row(description)+(ROW()-1)*5) "dpal" wrote: =INDIRECT("Takeoff!B34279,"&( ROW(Description)-1)*5) Whats wrong with this? it comes up #Name? "bj" wrote: try =indirect("Sheet2!A"&(row()-1)*5) in A2 and copy down "dpal" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
TakeoffB is the sheet and colomn how does it know were to start if there
isn't a number after it. Yes Desription is in the first cell of row B. I am trying to get sheet1 B1 to equal sheet2 B1 , then sheet1 B2 to equal sheet2 B10. Doing this with the formula This goes on for 10000 lines on sheet 2 so I need 100 lines on sheet one. "bj" wrote: the value in an indirect function has to look like a cell address what cells are you trying to copy to where and where is the Description cell? Is Description a named cell? maybe =INDIRECT("Takeoff!B"&row(description)+(ROW()-1)*5) "dpal" wrote: =INDIRECT("Takeoff!B34279,"&( ROW(Description)-1)*5) Whats wrong with this? it comes up #Name? "bj" wrote: try =indirect("Sheet2!A"&(row()-1)*5) in A2 and copy down "dpal" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
... I am trying to get sheet1 B1 to equal sheet2 B1 , then sheet1
B2 to equal sheet2 B10. Doing this with the formula This goes on for 10000 lines on sheet 2 so I need 100 lines on sheet one. Sorry, but (at least to me) the problem statement seems to keep changing. If you look up OFFSET(), ROW(), and COLUMN() in Excel's built-in Help, maybe that'll spark a solution. From the description above, you want to copy each 9 rows of Sheet2 to Sheet1, for a total of 1112 lines (not 100), assuming equally-spaced rows. This works when I try it: Put this in Sheet1!B1: =OFFSET(Sheet2!$B$1,9*(ROW()-1),0) Copy down to B1112 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
I have 2 tabs. One spread sheet is a summury of the other.
Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. The OFFSET() function might help. For example, try putting this in Sheet1!A1 and copying down: =OFFSET(Sheet2!$A$1,5*(ROW()-1),0) (The cells you mention on Sheet2 aren't evenly spaced, so maybe this suggestion doesn't help. If there's something else about Sheet2 that hints at which cells to choose, please describe it.) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
Do I right Row() or Row(B)?
I have 48 lines that keep repeating. Each cell on sheet 1 is equal to a specific cell on Sheet 2. It always repeats the same amout of lines down So Sheet1 cell $AD$1 is egual to sheet2 $O$53 Sheet1 cell $AD$2 is equal to sheet2 $O$101 etc. This goes on for 40,000 lines on sheet 2 "MyVeryOwnSelf" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. The OFFSET() function might help. For example, try putting this in Sheet1!A1 and copying down: =OFFSET(Sheet2!$A$1,5*(ROW()-1),0) (The cells you mention on Sheet2 aren't evenly spaced, so maybe this suggestion doesn't help. If there's something else about Sheet2 that hints at which cells to choose, please describe it.) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function for adding lines
=OFFSET(Takeoff!$A$34279,5*Takeoff!(ROW(Descriptio n)-1,0)
Do you see any thing wrong with this? "MyVeryOwnSelf" wrote: I have 2 tabs. One spread sheet is a summury of the other. Each line item on one tab goes across. The other tab goes down.( every line on Tab 1 is equal to 10 lines on tab 2 ) Tab one : XX XXX XXXXX tab two:line one XX line five XXX line ten XXX Repeat: Right now I pick a cell on tab one A1 and = the cell A1 on tab 2 A2 and = the cell A5 on tab 2 A3 and = the cell A10 on tab 2 : repeat This takes a while. Is there a formula I can put on tab one EX: for A1 = cell A1 + 5 on tab 2. The OFFSET() function might help. For example, try putting this in Sheet1!A1 and copying down: =OFFSET(Sheet2!$A$1,5*(ROW()-1),0) (The cells you mention on Sheet2 aren't evenly spaced, so maybe this suggestion doesn't help. If there's something else about Sheet2 that hints at which cells to choose, please describe it.) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for Adding Lines to Linked Spreadsheets | Excel Worksheet Functions | |||
| List Function - Adding new lines w/ formulas | Excel Discussion (Misc queries) | |||
| 2 questions - one about inserting the date, other about adding lines. | New Users to Excel | |||
| Adding lines of data to an area chart | Excel Discussion (Misc queries) | |||
| Adding lines of best fit | Charts and Charting in Excel | |||
Hybrid Mode
