M
Mark Tutt
I'm pretty sure I know the answer to this one, but I thought it best
to ask...
I have a set of import files that contains sales data for multiple
stores. These are imported into a spreadsheet that has multiple
analysis sheets based on the data. This is done automatically via a
VB app nightly, and the results are emailed out to a list of
recipients. Standard stuff, daily revenue, MTD, YTD, LY/CY, labor
costs,etc.
Now the problem. For some of the newly requested reports, some of the
detail from the import files needs to be pulled into individual sheets
for each store. They don't want to talk about another format, they
just want to use the spreadsheet to replace some printed reports, and
keep everything in one file.
This would be simple enough with a VBA macro by running through the
data and moving it to separate sheets based on the criteria cell
contents, but the customer for this has all of their desktops locked
down in a restricted environment where they cannot run macros.
Is there a way, with forumulas only, to pull specific rows from a list
based on some defined criteria? Or am I going to have to ask for
individual files for data that must be separated?
For a basic example:
[import data]
Store ID, Employee Number, Check Number,ItemNumber, Item Name,
Amount,Return Reason
1, 123, 100, 12345,'Golf Balls',14.99,'Balls go into water'
2, 927, 200, 56789,'Expensive Titanium Driver',799.00,'Wife Said No'
1, 124, 110, 12365,'Golf Shirt',44.99,'Received as gift/not his style'
And from this data show a report showing return transactions,
seperated by Store ID
to ask...
I have a set of import files that contains sales data for multiple
stores. These are imported into a spreadsheet that has multiple
analysis sheets based on the data. This is done automatically via a
VB app nightly, and the results are emailed out to a list of
recipients. Standard stuff, daily revenue, MTD, YTD, LY/CY, labor
costs,etc.
Now the problem. For some of the newly requested reports, some of the
detail from the import files needs to be pulled into individual sheets
for each store. They don't want to talk about another format, they
just want to use the spreadsheet to replace some printed reports, and
keep everything in one file.
This would be simple enough with a VBA macro by running through the
data and moving it to separate sheets based on the criteria cell
contents, but the customer for this has all of their desktops locked
down in a restricted environment where they cannot run macros.
Is there a way, with forumulas only, to pull specific rows from a list
based on some defined criteria? Or am I going to have to ask for
individual files for data that must be separated?
For a basic example:
[import data]
Store ID, Employee Number, Check Number,ItemNumber, Item Name,
Amount,Return Reason
1, 123, 100, 12345,'Golf Balls',14.99,'Balls go into water'
2, 927, 200, 56789,'Expensive Titanium Driver',799.00,'Wife Said No'
1, 124, 110, 12365,'Golf Shirt',44.99,'Received as gift/not his style'
And from this data show a report showing return transactions,
seperated by Store ID