• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can you automatically add rows from a source report data to a linked report?

S

smc001

Member
I have a consolidated report that has links to many source reports, which provide the data. When the source reports have additional information added, which must be manually added with there new rows shouldn't the new data and rows automatically populate on the consolidated report it rolls into, once the report is updated via the Data, edit connection...etc...


The consolidated report is set-up as such:


Section 1: Travel

row 1-8, (4, Columns, Purchaser, Description, Cost, discount)

row 9 (absolute ref): (Totals each of the columns).


Section 2: General Office Supplies

row 1-8, (4, Columns, Purchaser, Description, Cost, discount)

row 9 (absolute ref): (Totals each of the columns).


Section 3: Misc.

row 1-8, (4, Columns, Purchaser, Description, Cost, discount)

row 9 (absolute ref): (Totals each of the columns).


If the source report data expands beyond rows 1-8, say 9, 10, 11, 12, 13, etc.... the information does not automatically show up on the consolidation report. I need the consolidation report to be able to expand and reflect the new rows data from the source report. Any Ideas on how this can be accomplished.... Is there a formula that can be added just before the TOTALS row on the consolidation report???
 
Hi,


This can be achieved efficiently using VBA coding.


If you are using linking then try the following formula:

for first column (A) of each consolidated sheet sections

=IFERROR(IF(COUNTA([source_file]Sheet2!$A:$A)=(ROWS($A$1:a2)-1),"Total",INDEX([Source_file]Sheet2!$A$1:$A$7,ROWS($a$1:a2),1)),"")


for column (B) with text (description etc)

=IFERROR(IF(COUNTA([source_file]Sheet2!$A:$A)=(ROWS($b$1:b2)-1),"",INDEX([Source_file]Sheet2!$B$1:$B$7,ROWS($b$1:b2),1)),"")


for col C with numeric (Cost)

=IFERROR(IF(COUNTA([source_file]Sheet2!$A:$A)=(ROWS($c$1:c2)-1),SUM($C$1:C1),INDEX([Source_file]Sheet2!$c$1:$c$7,ROWS($c$1:c2),1)),"")


same for col D, just change the "SUM()" piece to blank if you need blank,


You need to drag this formula in consolidated sheet for maximum expected rows in source file.

OR

on any day you dont see "Total" showing in the consolidated file, drag formulas down till you see it :)


Regards,

Prasad
 
sorry, kindly ignore my suggestion.


I did not see that you have multiple source and using data connection wiz..


Regards,

Prasad :(
 
smc,


You can do this by using Named Ranges...


Assuming your raw data is in a sheet called as RawData,


Define names using Offset taking into account the entire data range.


=OFFSET(RawData!$A$1,0,,COUNTA(RawData!$A:$A)-1,COUNTA(RawData!$1:$1))


Now on your consolidation sheet, you can query this data using VLOOKUP.


Because we have used a dynamic named range using Offset, when you data increases the offset function will take that into account.


Not sure what formulas you are using on the consolidation sheet as of now, hence given the example of VLOOKUP.


Hope this help...


~VijaySharma
 
My raw data are reports which are linked to the master reports with the sections I have noted above...I do not have a rawdata list or spreadsheet. Its literally being pulled from a report that generated by each department.
 
What column or report would I put this formula? in the report generated by dept. or the master report and where exactly? last line of each section 1, Travel 2, Office Supplies 3, Misc.? Please advise...
 
Are you able to upload your work in progress somewhere for us to look at... it will be a lot easier having a look and suggesting the best course of action...


~VijaySharma
 
Here's the link for the source report.....


https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!111&parid=8C4598BED3EF132A!108&authkey=!AFU_dk6bjOz_4hs


Here is the link for the CSR report which is where I need the rows to expand of course based upon the source reports reportable items expanding its rows...


https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!112&parid=8C4598BED3EF132A!108&authkey=!AAtk_dgMj19WgnQ


THANK YOU!!!!!
 
smc,


I have checked your files and this is not so straight forward as it seems. We would need to write VBA code to accomplish your base objective of auto expansion of rows on the reporting sheet.


I will try to do this over the weekend, and will let you know once done.


~VijaySharma
 
Any such luck? I know it's shorter days due to the holiday months... Any assistance is greatly appreciated!
 
Question can this be edited to be used to auto populate rows in a report?

I was looking around for an option that would allow me to auto populate a new row each time it is needed. I have a report that is feed by other sources and as the other sources data expands the report must expand to accommodate. It will not since there are sections in the report that have other source feeds. At the end of each section I wanted to place a code that would automatically add a new row as need, which would just shift all the other section down when new row is added.

I found this code for excel 2000 (tested)


This code inserts a row into all worksheets of a workbook at the same location in each worksheet. The user inputs the row position to insert.


Option Explicit


Sub InsertRowAllSheets()


Dim cs As String

cs = ActiveSheet.Name

Dim y As Integer

y = Application.InputBox("Enter the row number you wish to add", _

Type:=1) 'enter 16 to insert a new row 16, the old row _

will become 17 And all other rows push down 1 row As well.

If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _

vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub

Application.ScreenUpdating = False

Dim r As Range

Dim ws As Worksheet

' On Error Resume Next 'Error handler

For Each ws In ThisWorkbook.Worksheets

ws.Activate

Set r = ActiveSheet.Range("A" & y)

If y < 7 Then Goto circumv 'Not to insert in Headers

Range("A" & y).EntireRow.Insert


' code can be inserted here to copy formulas for some or all sheets in the workbook


circumv:

Next ws

Sheets(cs).Activate

Application.ScreenUpdating = True


End Sub


Please advise if this can be altered to accomadate the auto row solution and how... Thank You!
 
Hi, smc001!

Answer is yes, it works, and here you have it adapted as asked. Please check if it does the job.

https://skydrive.live.com/?cid=8c4598bed3ef132a&id=8C4598BED3EF132A%21117#cid=3A8BDC8CDF4D772C&id=3A8BDC8CDF4D772C%21172

Regards!
 
I need the code to only insert row when required in specific worksheets...


My report is setup as such...I have multiple catogories that have 8 rows (5 columns) to hold it's data. These rows and columns are all linked to the source report..


Category: Column1 | Column2 | Column3 | Column4 | column5

Row1

Row2

Row3

Row4

Row5

Row6

Row7

Row8


The data feed by the source report can either reduce/add rows; clarify it wont populate data in my report when there's no data in the source row linked to my report row. But if the data is added beyond 8 rows from the source report, then I need more rows... If there arent more rows added and the other category sections pushed down it will overwrite the entire new categroy section not even pertaining to the current category being worked on


So these 8 rows may be filled once a week and the next week there may be 20 rows total needed for that category. Thats' 12 additional rows that need to autogenerate based upon the data feed of the source report. The example code I supplied I thought would be a good starting point. The example apply's rows to a specific designated row (row 16) that I state to be added to all worksheets in the workbook. I dont' need it like that...


I only require adding rows when driven by the additional data rows from the source report. Is that possible? Or should I just build in additional hidden rows by grouping in each section and as needed the data will be added and the rows will just need to be ungrouped from being group. That was my work around but it's not as pretty as what I think can happen to automate this issue. Any and all suggestions are welcome ;) Once again Thank you!
 
Hi, smc001!

It it's only a situation about passing from 8 to 20 rows and with that you won't have any further problem, I wouldn't doubt it: change to 20 rows, hidden or blank, and forget all the stuff around implementing it in VBA.

What you're asking is possible, but the deployment is harder as the button for the previous value in the Report workbook. You decide.

Regards!
 
Well I have within each report (15 or so Sections of categories)


So each category holds 8-10 rows or in some cases I had knowledge of a category needed 40 rows so accomadated... I had did a grouped additional 50 rows per category on each ans started running in to memory issues... The speadsheet started to malfuntion...


It would freeze and not allow me to add rows... Crazy stuff... So I only grouped additional 10 rows pe category.... It would be better to just have it automated so thne there wouldn't be all these empty rows the system is constancly running through...


Let me know your thoughts...
 
Okay... Heres the link... THANK YOU!!!


https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!118&parid=8C4598BED3EF132A!108&authkey=!APbwTT7JCa7_nJc


I have given instruction in the report as too how the master report is feed by the field reports... Keep in mind I have other report just like this for other Operstions: segments and they have (8) projects with separate categories... Let me know... THANK YOU!
 
You must log in or register to reply here.
Back
Top