• 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.

copy dynamic range and add 2 more columns

mario rdz

New Member
hello all
I'm working with a reporting tool that creates a dynamic range when it writes data to excel. This tool does not allow to use formulas in the excel columns, and every time the query is refreshed the formulas are gone. Is there a way I can copy the reporting dynamic range and add 2 more columns in the new range?
Example:
Reporting range = REPTOOL from cells A1 to Z100 (this range changes every time when query is refreshed)
I need a new range = REPTOOL + columns AA, AB. This means my NEWRANGE will be from cell A1 to AB100

I appreciate your help
Mario
 
Your first problem is to identify the range as output by the tool. It might be given a defined name by the tool, but I wouldn't bank on it.
If it were a modern dynamic array anchored in cell A1 then you could define 'newRange' to refer to
= Sheet1!$A$1# : Sheet1!$AB$1

If the dynamic range is just a varying sized block of data, then you will need to count entries 'n' in a column or lookup the bottom cell to build the extended range. If your formula for the dynamic range involves the OFFSET function, then you can make it encompass as many columns as you choose by setting the range width
=OFFSET(Sheet1!$A$1, 0, 0, n, 28)

If the output were a table, then
= ROWS(Table1)
would provide the information you need,

Another thought is that you may be able to use a macro to insert formulas back into the range once the query has been refreshed or add columns to a table.
 
I would seriously look at using Power Query, this will leave what your reporting tool produces untouched, but will look at it and produce a new table with the added columns (which is what you're asking for); all you'd need to do is to refresh that new table. Depending what the reporting tool produces (plain range or a table) we may have to ascertain the new source range extents, but even this might not be needed if you get the reporting tool's output to be alone on its own sheet (which sheet doesn't even need to be visible).
Attach a workbook with sample source data and the formulae that you have in those 2 extra columns and I can put something together.
 
Your first problem is to identify the range as output by the tool. It might be given a defined name by the tool, but I wouldn't bank on it.
If it were a modern dynamic array anchored in cell A1 then you could define 'newRange' to refer to
= Sheet1!$A$1# : Sheet1!$AB$1

If the dynamic range is just a varying sized block of data, then you will need to count entries 'n' in a column or lookup the bottom cell to build the extended range. If your formula for the dynamic range involves the OFFSET function, then you can make it encompass as many columns as you choose by setting the range width
=OFFSET(Sheet1!$A$1, 0, 0, n, 28)

If the output were a table, then
= ROWS(Table1)
would provide the information you need,

Another thought is that you may be able to use a macro to insert formulas back into the range once the query has been refreshed or add columns to a table.

Hi Peter, I followed your advice and was able to get my new dynamic range...
Thanks much...
 
I would seriously look at using Power Query, this will leave what your reporting tool produces untouched, but will look at it and produce a new table with the added columns (which is what you're asking for); all you'd need to do is to refresh that new table. Depending what the reporting tool produces (plain range or a table) we may have to ascertain the new source range extents, but even this might not be needed if you get the reporting tool's output to be alone on its own sheet (which sheet doesn't even need to be visible).
Attach a workbook with sample source data and the formulae that you have in those 2 extra columns and I can put something together.

Hi P45cal..
I resolved the issue with Peter's advice. I appreciate your time in checking my issue.
Thanks
Mario
 
Back
Top