Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
collect data from one excel sheet to another
I have one main workbook with a whole lot of onfo in it and when i open the
otehr workbooks then they must gather certain information from the main workbook. I have this partially worked out in the following: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",'[MAIN TIMESHEET.xls]October'!$B5:$I5,$B$79) However, it is basically copying the info line by line and inserting it into the other workbook on the same row number as where it got the info from. How can i change this so that the workbook will add in the info starting from the first row and follow through to the second, third and fourth row instead of basically just doing a "copy and Paste" scenario. Please help I am Desperate for an answer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
collect data from one excel sheet to another
the "true" path for your function returns an array of cells, but you don't
say what you want to do with them. Perhaps you need to modify your formula to the following: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",SUM('[MAIN TIMESHEET.xls]October'!$B5:$I5),$B$79) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sarcalogus" wrote: I have one main workbook with a whole lot of onfo in it and when i open the otehr workbooks then they must gather certain information from the main workbook. I have this partially worked out in the following: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",'[MAIN TIMESHEET.xls]October'!$B5:$I5,$B$79) However, it is basically copying the info line by line and inserting it into the other workbook on the same row number as where it got the info from. How can i change this so that the workbook will add in the info starting from the first row and follow through to the second, third and fourth row instead of basically just doing a "copy and Paste" scenario. Please help I am Desperate for an answer. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
collect data from one excel sheet to another
Hi Luke,
I understand where you are coming from but what i have so far is correct, it must take everything in the rows where BWC stand in a particular column and then put all the info in that row into the other workbook. However, if the info on the main workbook is on rows 5, 20, 23, 27 on the main workbook, then it just places it within those same rows on the other worbook leaving me having to delete all the blank rows. What i want it to do is start filling up the rows on the alternate workbook from row one and not leave any rows blank. Does this make sense? -- Sincerely, Sarcalogus "Luke M" wrote: the "true" path for your function returns an array of cells, but you don't say what you want to do with them. Perhaps you need to modify your formula to the following: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",SUM('[MAIN TIMESHEET.xls]October'!$B5:$I5),$B$79) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sarcalogus" wrote: I have one main workbook with a whole lot of onfo in it and when i open the otehr workbooks then they must gather certain information from the main workbook. I have this partially worked out in the following: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",'[MAIN TIMESHEET.xls]October'!$B5:$I5,$B$79) However, it is basically copying the info line by line and inserting it into the other workbook on the same row number as where it got the info from. How can i change this so that the workbook will add in the info starting from the first row and follow through to the second, third and fourth row instead of basically just doing a "copy and Paste" scenario. Please help I am Desperate for an answer. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
collect data from one excel sheet to another
In a new sheet,
In A2: =IF('[MAIN TIMESHEET.xls]October'!$F5="BWC",ROWS($1:1),"") In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX('[MAIN TIMESHEET.xls]October'!B$5:B$100,SMALL($A:$A,ROWS($1:1)))) Copy B2 across to I2. Select A2:I2, fill down to cover the expected source data extent. Cols B to I will return the required results all neatly compacted at the top. Minimize/hide col A. Modify the source range ...B$5:B$100 to suit your actual data extents. Any good? Hit the YES below. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Sarcalogus" wrote: I understand where you are coming from but what i have so far is correct, it must take everything in the rows where BWC stand in a particular column and then put all the info in that row into the other workbook. However, if the info on the main workbook is on rows 5, 20, 23, 27 on the main workbook, then it just places it within those same rows on the other worbook leaving me having to delete all the blank rows. What i want it to do is start filling up the rows on the alternate workbook from row one and not leave any rows blank. Does this make sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to use the P.O. template and collect/store data. | Excel Discussion (Misc queries) | |||
use a worksheet as a form to collect data | Excel Worksheet Functions | |||
Collect data of several sheets on another book's singel sheet | Excel Discussion (Misc queries) | |||
How to collect data from every 60th row? | Excel Discussion (Misc queries) | |||
collect data from different worksheet | Excel Worksheet Functions |