I am pulling data in from an external data source (sharepoint list) the data
is updated and new rows are added daily.

One of the columns is a milestone value.

Is it possible to take the raw data from the import and automatically have
the data broken into arrays based on the value in the milestone?

I have to report on this every week and I am looking for a way to automate
the process.

Thanks

Jeff


--
Jeff

On May 5, 7:24 pm, Jeff wrote:
I am pulling data in from an external data source (sharepoint list) the data
is updated and new rows are added daily.

One of the columns is a milestone value.

Is it possible to take the raw data from the import and automatically have
the data broken into arrays based on the value in the milestone?

I have to report on this every week and I am looking for a way to automate
the process.

Thanks

Jeff

--
Jeff

So...you want the array's length to be determined by the number in a
cell? Should be easy to do with an OFFSET function, unless that won't
work within an array function. Something like this, which will sum
column A for as many rows as the number in B1:
=SUM(A1:OFFSET(A1,B1-1,))

Don't think that is quite it.

I want to transfer the contents of a whole row to a row in a new spreadsheet
if it matches the criteria.

as I update the report I would expect things to move between milestones and
this would be reflected in the new array, one time it would have 10 rows the
next 12. is that a better explanation?
--
Jeff


"Spiky" wrote:

On May 5, 7:24 pm, Jeff wrote:
I am pulling data in from an external data source (sharepoint list) the data
is updated and new rows are added daily.

One of the columns is a milestone value.

Is it possible to take the raw data from the import and automatically have
the data broken into arrays based on the value in the milestone?

I have to report on this every week and I am looking for a way to automate
the process.

Thanks

Jeff

--
Jeff

So...you want the array's length to be determined by the number in a
cell? Should be easy to do with an OFFSET function, unless that won't
work within an array function. Something like this, which will sum
column A for as many rows as the number in B1:
=SUM(A1:OFFSET(A1,B1-1,))

On May 6, 12:13 pm, Jeff wrote:
Don't think that is quite it.

I want to transfer the contents of a whole row to a row in a new spreadsheet
if it matches the criteria.

as I update the report I would expect things to move between milestones and
this would be reflected in the new array, one time it would have 10 rows the
next 12. is that a better explanation?
--
Jeff

"Spiky" wrote:
On May 5, 7:24 pm, Jeff wrote:
I am pulling data in from an external data source (sharepoint list) the data
is updated and new rows are added daily.

One of the columns is a milestone value.

Is it possible to take the raw data from the import and automatically have
the data broken into arrays based on the value in the milestone?

I have to report on this every week and I am looking for a way to automate
the process.

Thanks

Jeff

--
Jeff

So...you want the array's length to be determined by the number in a
cell? Should be easy to do with an OFFSET function, unless that won't
work within an array function. Something like this, which will sum
column A for as many rows as the number in B1:
=SUM(A1:OFFSET(A1,B1-1,))

Ah.

When I had to do something like this, I used VBA to do an Advanced
Filter. Not sure if it works across workbooks this way, I would think
so. I couldn't find a way to make functions automatically transfer
varying amounts of data to another sheet. Sounds like you could Filter
on your milestone column, perhaps.

I set it to clear my destination first, then Filter the data based on
user input somewhere else. The worst part of using Filter is that you
have to set up Criteria. Code is something like this:

Sheets("Sheet 1").Range("A7:d75").ClearContents
Sheets("Sheet 2").Range("Account_db").AdvancedFilter
Action:=xlFilterCopy, CriteriaRange:=Sheets _ ("Sheet
3").Range("Aa1:aa2"), CopyToRange:=Worksheets("Sheet 1").Range("a7"),
_ Unique:=False


Then, in the destination sheet, I reference this area to do other
things. I simply made the area several rows longer than any result
would be so that I wouldn't have space issues. So if you are always
pulling 10-15 rows, for instance, leave space for 20.

You might be able to do this with functions, depends. Mine was a mix
of values and text, so some functions wouldn't work properly, anyway.
Filter always works, and it leaves no extra formulas laying around.