Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a fill down that increments based on the workbook name
I would like to know if it is possible to fill down a formula, which
includes a workbook name. I have many job workbook files (all closed) in same folder as masterworkbook file. I only use sheet1 in each book. I'd like to link or copy certain cells - but the same cells in each job workbook - to the masterworkbook, with the data from each job workbook taking up a row in the masterbook. I have in cell A2 of the masterbook the following link: =[job1.xls]Sheet1!$B$2 I would like to have: =[job2.xls]Sheet1!$B$2 in cell A3, and =[job3.xls]Sheet1!$B$2 in cell A4 and so on. Over time, as new job files get created (job4.xls, job5.xls ....), I would see data appreaing in the next row in the masterbook; ideally nothing would be displayed when the next job file has not been created yet (no errors). I hope someone could assist with a simple solution. I greatly appreciate your help! Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a fill down that increments based on the workbook na
Create an index on a new sheet. TechRepublic.com has the code for an index
already set up for that you can use. Once you copy the code, name the firs sheet index, right clck the tab, select "view code" and paste into the VB window. From here, as you add sheets and hyperlinks, the index will update dynamically. If, in the event, the idex doesn't update dynamically, go back to "view code" and hit the F5 button. This will force the index code to run and update itself. Don't forget to add the hyperlinks manually. Hope this helps. " wrote: I would like to know if it is possible to fill down a formula, which includes a workbook name. I have many job workbook files (all closed) in same folder as masterworkbook file. I only use sheet1 in each book. I'd like to link or copy certain cells - but the same cells in each job workbook - to the masterworkbook, with the data from each job workbook taking up a row in the masterbook. I have in cell A2 of the masterbook the following link: =[job1.xls]Sheet1!$B$2 I would like to have: =[job2.xls]Sheet1!$B$2 in cell A3, and =[job3.xls]Sheet1!$B$2 in cell A4 and so on. Over time, as new job files get created (job4.xls, job5.xls ....), I would see data appreaing in the next row in the masterbook; ideally nothing would be displayed when the next job file has not been created yet (no errors). I hope someone could assist with a simple solution. I greatly appreciate your help! Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a fill down that increments based on the workbook name
Forgot the "blanking" part.
=IF(INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2")="","",INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2")) All on one line Gord On Sat, 01 Jul 2006 10:02:30 -0700, Gord Dibben <gorddibbATshawDOTca wrote: In A3 enter this formula. =INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2") Gord Dibben MS Excel MVP On 1 Jul 2006 08:57:07 -0700, wrote: I would like to know if it is possible to fill down a formula, which includes a workbook name. I have many job workbook files (all closed) in same folder as masterworkbook file. I only use sheet1 in each book. I'd like to link or copy certain cells - but the same cells in each job workbook - to the masterworkbook, with the data from each job workbook taking up a row in the masterbook. I have in cell A2 of the masterbook the following link: =[job1.xls]Sheet1!$B$2 I would like to have: =[job2.xls]Sheet1!$B$2 in cell A3, and =[job3.xls]Sheet1!$B$2 in cell A4 and so on. Over time, as new job files get created (job4.xls, job5.xls ....), I would see data appreaing in the next row in the masterbook; ideally nothing would be displayed when the next job file has not been created yet (no errors). I hope someone could assist with a simple solution. I greatly appreciate your help! Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a fill down that increments based on the workbook name
Thank you very much!
It works as long as I have the job sheets open, but get a REF! error when closed. Most of them will be closed though in my scenario......... Any other thoughts? Gord Dibben wrote: Forgot the "blanking" part. =IF(INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2")="","",INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2")) All on one line Gord On Sat, 01 Jul 2006 10:02:30 -0700, Gord Dibben <gorddibbATshawDOTca wrote: In A3 enter this formula. =INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2") Gord Dibben MS Excel MVP On 1 Jul 2006 08:57:07 -0700, wrote: I would like to know if it is possible to fill down a formula, which includes a workbook name. I have many job workbook files (all closed) in same folder as masterworkbook file. I only use sheet1 in each book. I'd like to link or copy certain cells - but the same cells in each job workbook - to the masterworkbook, with the data from each job workbook taking up a row in the masterbook. I have in cell A2 of the masterbook the following link: =[job1.xls]Sheet1!$B$2 I would like to have: =[job2.xls]Sheet1!$B$2 in cell A3, and =[job3.xls]Sheet1!$B$2 in cell A4 and so on. Over time, as new job files get created (job4.xls, job5.xls ....), I would see data appreaing in the next row in the masterbook; ideally nothing would be displayed when the next job file has not been created yet (no errors). I hope someone could assist with a simple solution. I greatly appreciate your help! Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I create a fill that increments based on worksheet numbers | Excel Worksheet Functions | |||
Fill in contents based on another column | Excel Worksheet Functions | |||
How do I create a commission chart based on dates worked? | Excel Worksheet Functions | |||
How to create a fill down that increments based on the worksheet n | Excel Worksheet Functions | |||
Fill automatically from one worksheet to another based on cel valu | Excel Discussion (Misc queries) |