How to reference Data from a query in a different sheet within same workbook.

[Chris Slater]

Hi Guys,
I am working on a report that pulls in data from another system via a query. The problem is that the data pulls in in the wrong order and cannot be changed. I will call the sheet with this data "DATA".
So what I need to do is to use another sheet for the report which references each column from the "DATA" sheet but in the order required for the report. Also I will need to add cells with formulas at the base of the report for summing the totals.
All this is simple if the content of the "DATA" sheet doesn't change but, as you can imagine, in the real world it's constantly updating which means the number of rows in the "DATA" sheet is increasing/decreasing on a regular basis.
What I need to do is create a sheet that dynamically references the "DATA" sheet in such a way that the content expands or collapses depending on what is happening to the content of the "DATA" sheet. ie it adds or deletes rows automatically without overwriting the formulas at the foot of the report.
Can it be done? And if so I would really appreciate some help from you guys to make it work.
Thanks.
Chris

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your DATA sheet and of the Report sheet showing the order required for the report. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Chris Slater]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your DATA sheet and of the Report sheet showing the order required for the report. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I've tried to set this up but it doesn't work

 

[mumps]

If the XL2BB didn't work, did you try to upload the file to www.box.com or www.dropbox.com?

 

[Chris Slater]

If the XL2BB didn't work, did you try to upload the file to www.box.com or www.dropbox.com?

I don't think that would be relevant because I can't replicate the query that pulls in the data. The problem I'm having is that the data is fluid so is changing all the time.
I need to find a way of referencing it in a way that expands and contracts with the source data and doesn't simple overwrite any summing formulas I may have at the foot of the report.

 

[mumps]

I can't replicate the query that pulls in the data

I wouldn't need the query. I would need one example Data sheet so I can see how the data is organized and also what the corresponding report would look like based on the Data sheet.

 

[Chris Slater]

I wouldn't need the query. I would need one example Data sheet so I can see how the data is organized and also what the corresponding report would look like based on the Data sheet.

I've uploaded an example to Dropbox. How do I share it with you?

 

[mumps]

Mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Chris Slater]

Mark it for 'Sharing' and you will be given a link to the file that you can post here.

Is this OK

Okappy Report Example.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

Will the number of columns in the Report always be 25? Will there always be 5 rates?