Automatically updating worksheets from a master worksheet
 

Hello,

I have been using a master worksheet which stores all my data and been using
a filter to show certain types of data and copy and paste into a new
worksheet. Is there a way to update the data in the master worksheet and
have that information trickle down into the other worksheets without applying
the filters and copy/paste?
--
TS

Automatically updating worksheets from a master worksheet
 

Hi

Use ODBC queries to get filtered data into partial sheets. You can set
queries to be refreshed on open and/or after some time interval, or you can
refresh them manually.

Another option is to create (some) report sheet(s). You determine there
filter conditions, and according data are read from master sheet using
formulas or ODBC query(es). The exact solution depends heavily on your data,
and what you want to get.


Arvi Laanemets


"Nlevans" wrote in message
...
Hello,

I have been using a master worksheet which stores all my data and been
using
a filter to show certain types of data and copy and paste into a new
worksheet. Is there a way to update the data in the master worksheet and
have that information trickle down into the other worksheets without
applying
the filters and copy/paste?
--
TS

Automatically updating worksheets from a master worksheet
 

Thank you for responding. How would I set up these queries and/or worksheets?
"Arvi Laanemets" wrote:

Hi

Use ODBC queries to get filtered data into partial sheets. You can set
queries to be refreshed on open and/or after some time interval, or you can
refresh them manually.

Another option is to create (some) report sheet(s). You determine there
filter conditions, and according data are read from master sheet using
formulas or ODBC query(es). The exact solution depends heavily on your data,
and what you want to get.


Arvi Laanemets


"Nlevans" wrote in message
...
Hello,

I have been using a master worksheet which stores all my data and been
using
a filter to show certain types of data and copy and paste into a new
worksheet. Is there a way to update the data in the master worksheet and
have that information trickle down into the other worksheets without
applying
the filters and copy/paste?
--
TS

Automatically updating worksheets from a master worksheet
 

Hi

A couple of examples.

You have a table on sheet MyTable in range A1:C100, where the first row
contains column headers (having headers is obligatory). Columns are Col1,
Col2 and Col3. You want to filter by Col1. You may have to add new records
to this table later, but you are sure the number of rows doesn't exceed
1000.

1. Using ODBC
Define a non-dynamic named range (InsertNameDefine) MyTable as
=MyTable!$A$1:$C$1000
Save your workbook!!!
Activate the sheet, where you want to get filtered table to appear, and
select cell A1.
From Data menu, select Get External DataNew Database Query (when such
option is missing, then you have to install MS Query or whatewer it was
named from CD)
Excel FilesOK
In Select Workbook window, select your workbook as data source. OK
Select the range MyTable as table and transfer all fields to Columns in Your
Query list. Next
Set filter to Col1 Is Not Null and Col1 equals your value. Next
You can set sort order. Next
Select View Data ... . Finish
Remove Col1 from table (select Col1 column and press Del key).
Close Microsoft Query window.
Click on properties button in Return ... window. Check 'Refresch data on
file open' and 'Overwrite existing cells...'. You also can set automatic
refreshing frequency in minutes for opened file.OK
OK

Sorry, but I have to leave now. Maybe I have some time to explain other
possibilities later today.


Arvi Laanemets



"Nlevans" wrote in message
...
Thank you for responding. How would I set up these queries and/or
worksheets?
"Arvi Laanemets" wrote:

Hi

Use ODBC queries to get filtered data into partial sheets. You can set
queries to be refreshed on open and/or after some time interval, or you
can
refresh them manually.

Another option is to create (some) report sheet(s). You determine there
filter conditions, and according data are read from master sheet using
formulas or ODBC query(es). The exact solution depends heavily on your
data,
and what you want to get.


Arvi Laanemets


"Nlevans" wrote in message
...
Hello,

I have been using a master worksheet which stores all my data and been
using
a filter to show certain types of data and copy and paste into a new
worksheet. Is there a way to update the data in the master worksheet
and
have that information trickle down into the other worksheets without
applying
the filters and copy/paste?
--
TS

Automatically updating worksheets from a master worksheet
 

Hi again. Let's continue!

(You have a table on sheet MyTable in range A1:C100, where the first row
contains column headers (having headers is obligatory). Columns are Col1,
Col2 and Col3. You want to filter by Col1. You may have to add new records
to this table later, but you are sure the number of rows doesn't exceed
1000.)

2. Creating report sheet through formulas.

You have to add a new leftmost column into MyTable, where an unique
identificator is calculated for every row of table. And you craete a new
sheet, p.e. MyReport.

On sheet MyReport, you apply data validation list(s) to some cell(s), to
allow you to select filter condition(s). In our example, the list must
contain all possible values on column Col1 in MyTable. The best way is to
have such list on separate sheet, p.e. MyList. When you have header in
MyList!A1, and the list itself starts from cell MyList!A2, then define a
dynamic named range p.e. MyList
MyList=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)
On sheet MyTable, select range A2:A1000, and apply to selected range data
validation list as
=MyList
(Applying data validation list to column Col1 prevents user entering
non-valid entries, and allows him to select entries from drop-down list)
On sheet MyReport, apply data validation list p.e. to cell MyReport!B1.
(When you want to filter by several conditions, p.e. by values for Col1 and
Col2, you have to create lists like this for every filtered column)
On sheet MyTable, add a new column to left of existing table - the new
column will be A:A, and your table is now in range B1:D1000. You can add a
header into cell A1, but later you hide this column anyway.
There are 2 possible ways to define an identificator. It may depend on
selected condition(s) on sheet MyReport, or not. An example for both:
A2=IF(OR(B2="";B2<MyReport!$B$1),"",B2 & COUNTIF($B$2:$B2,MyReport!$B$1))
, or
A2=IF(B2="","",B2 & COUNTIF($B$2:$B2,$B2))
(When you have several conditions, then when using first formula, you
replace COUNTIF with SUMPRODUCT, and in OR you check for all conditions.
When you use second formula, you have to compose an unique string form all
conditions, and again to use SUMPRODUCT to calculate an added counter - it
will be too complex to explain it here in detail)
Copy entered formula to range A2:A1000

On sheet MyReport, Into some row (p.e. row 3) enter column headings.
A3="Col2", B3="Col3"
When you used 1st formula as identificator, then:
A4=IF(ISERROR(VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP(ROW()-2,
MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,4,0))
When you used 2nd formula as identificator, then (with several conditions
you have to correct following formula):
A4=IF(ISERROR(VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP($B$1 & ROW()-2,MyTable!$A$2:$D$1000,4,0))

Copy A4:B4 down for as much rows as you need.


Arvi Laanemets

Automatically updating worksheets from a master worksheet
 

Thank you for your help. I will try this and get back to you. Thanks again
--
TS


"Arvi Laanemets" wrote:

Hi again. Let's continue!

(You have a table on sheet MyTable in range A1:C100, where the first row
contains column headers (having headers is obligatory). Columns are Col1,
Col2 and Col3. You want to filter by Col1. You may have to add new records
to this table later, but you are sure the number of rows doesn't exceed
1000.)

2. Creating report sheet through formulas.

You have to add a new leftmost column into MyTable, where an unique
identificator is calculated for every row of table. And you craete a new
sheet, p.e. MyReport.

On sheet MyReport, you apply data validation list(s) to some cell(s), to
allow you to select filter condition(s). In our example, the list must
contain all possible values on column Col1 in MyTable. The best way is to
have such list on separate sheet, p.e. MyList. When you have header in
MyList!A1, and the list itself starts from cell MyList!A2, then define a
dynamic named range p.e. MyList
MyList=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)
On sheet MyTable, select range A2:A1000, and apply to selected range data
validation list as
=MyList
(Applying data validation list to column Col1 prevents user entering
non-valid entries, and allows him to select entries from drop-down list)
On sheet MyReport, apply data validation list p.e. to cell MyReport!B1.
(When you want to filter by several conditions, p.e. by values for Col1 and
Col2, you have to create lists like this for every filtered column)
On sheet MyTable, add a new column to left of existing table - the new
column will be A:A, and your table is now in range B1:D1000. You can add a
header into cell A1, but later you hide this column anyway.
There are 2 possible ways to define an identificator. It may depend on
selected condition(s) on sheet MyReport, or not. An example for both:
A2=IF(OR(B2="";B2<MyReport!$B$1),"",B2 & COUNTIF($B$2:$B2,MyReport!$B$1))
, or
A2=IF(B2="","",B2 & COUNTIF($B$2:$B2,$B2))
(When you have several conditions, then when using first formula, you
replace COUNTIF with SUMPRODUCT, and in OR you check for all conditions.
When you use second formula, you have to compose an unique string form all
conditions, and again to use SUMPRODUCT to calculate an added counter - it
will be too complex to explain it here in detail)
Copy entered formula to range A2:A1000

On sheet MyReport, Into some row (p.e. row 3) enter column headings.
A3="Col2", B3="Col3"
When you used 1st formula as identificator, then:
A4=IF(ISERROR(VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP(ROW()-2,
MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,4,0))
When you used 2nd formula as identificator, then (with several conditions
you have to correct following formula):
A4=IF(ISERROR(VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP($B$1 & ROW()-2,MyTable!$A$2:$D$1000,4,0))

Copy A4:B4 down for as much rows as you need.


Arvi Laanemets