Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading formulas down the workbook while pulling from 2nd workbo
I have 2 workbooks that I want to pass data through. One workbook has a data
entry sheet for 16 different operation lines. Data is entered into that workbook for the day and then totalled. There are 365 duplicates of this chart in the workbook each cooresponding to a date. The 2nd workbook pulls the totals from the 1st workbook and stores the totals by date. I am using the Vlookup function to pull the data according to date. My problem is that I have all my formulas in the 2nd workbook to pull the data from the 1st workbook, but the next set of totaled data is 35rows down. Is there a way to change the formula in the 2nd workbook so I can just drag the formula down and it only pull data every 35th row? If that will not work then the whole processes will have to be manual as I am not going to type in the formula (365*16*72*2) times... Thanks in advance for the help guys! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading formulas down the workbook while pulling from 2nd workbo
Could you show us the formula?
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "EA4Life" wrote in message ... I have 2 workbooks that I want to pass data through. One workbook has a data entry sheet for 16 different operation lines. Data is entered into that workbook for the day and then totalled. There are 365 duplicates of this chart in the workbook each cooresponding to a date. The 2nd workbook pulls the totals from the 1st workbook and stores the totals by date. I am using the Vlookup function to pull the data according to date. My problem is that I have all my formulas in the 2nd workbook to pull the data from the 1st workbook, but the next set of totaled data is 35rows down. Is there a way to change the formula in the 2nd workbook so I can just drag the formula down and it only pull data every 35th row? If that will not work then the whole processes will have to be manual as I am not going to type in the formula (365*16*72*2) times... Thanks in advance for the help guys! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading formulas down the workbook while pulling from 2nd wo
The forumla I am using is:
='C:\Documents and Settings\ase0505\My Documents\to be\KPI data processing\[Data Collection Shift 1.xls]Line 1'!$E35 The data Collection Shift 1.xls is the 1st workbook that is storing all of my data. I tried removing the absolute vale on the E35 in hopes I could have it update. But basically, to go to the next day, I need ot have E35 change to E70, and then the 3rd entry would need to be E105 etc...365 times. Thanks "Bernard Liengme" wrote: Could you show us the formula? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "EA4Life" wrote in message ... I have 2 workbooks that I want to pass data through. One workbook has a data entry sheet for 16 different operation lines. Data is entered into that workbook for the day and then totalled. There are 365 duplicates of this chart in the workbook each cooresponding to a date. The 2nd workbook pulls the totals from the 1st workbook and stores the totals by date. I am using the Vlookup function to pull the data according to date. My problem is that I have all my formulas in the 2nd workbook to pull the data from the 1st workbook, but the next set of totaled data is 35rows down. Is there a way to change the formula in the 2nd workbook so I can just drag the formula down and it only pull data every 35th row? If that will not work then the whole processes will have to be manual as I am not going to type in the formula (365*16*72*2) times... Thanks in advance for the help guys! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading formulas down the workbook while pulling from 2nd wo
It can be done but it would require some serious coding. I have code that
duplicates it'self (22 lines) 1000 times. You have to find all variables and make them constants then provide some counters, etc. It took me several days but now I can generate this type of code in seconds. I don't have several days so I leave the studying to you. Good luck. "EA4Life" wrote: The forumla I am using is: ='C:\Documents and Settings\ase0505\My Documents\to be\KPI data processing\[Data Collection Shift 1.xls]Line 1'!$E35 The data Collection Shift 1.xls is the 1st workbook that is storing all of my data. I tried removing the absolute vale on the E35 in hopes I could have it update. But basically, to go to the next day, I need ot have E35 change to E70, and then the 3rd entry would need to be E105 etc...365 times. Thanks "Bernard Liengme" wrote: Could you show us the formula? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "EA4Life" wrote in message ... I have 2 workbooks that I want to pass data through. One workbook has a data entry sheet for 16 different operation lines. Data is entered into that workbook for the day and then totalled. There are 365 duplicates of this chart in the workbook each cooresponding to a date. The 2nd workbook pulls the totals from the 1st workbook and stores the totals by date. I am using the Vlookup function to pull the data according to date. My problem is that I have all my formulas in the 2nd workbook to pull the data from the 1st workbook, but the next set of totaled data is 35rows down. Is there a way to change the formula in the 2nd workbook so I can just drag the formula down and it only pull data every 35th row? If that will not work then the whole processes will have to be manual as I am not going to type in the formula (365*16*72*2) times... Thanks in advance for the help guys! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreading Formulas across the whole document | Excel Discussion (Misc queries) | |||
Formulas pulling from other tables | Excel Worksheet Functions | |||
pulling through data from another workbook | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
When opening an existing workbook, Excel opens but not the workbo. | Excel Discussion (Misc queries) |