Hi everyone,

How do I propogate data from one worksheet to another in Excel without
having to cut and paste? Is there an easier and accurate way?

My Problem in more Detail:
Right now I have an Excel spreadsheet for listing all my Training Courses.
It's the main worksheet where I enter all my courses that I've taken, plan to
take, and ones I put on hold. Then I have 3 sub-worksheets that each are
named COURSES TAKEN, COURSES NEEDED, and COURSES PLACED ON HOLD.
So when I enter new information in the main worksheet, I also have to go to
a sub-worksheet and enter the same info into this sheet. Is there an easier
way?

Thanks,
Kessie

Hi

Sure it is! (Links, lookups, indexing, ODBC queries etc. are keywords) But
how to do it, depends on design your main worksheet. And having all your
data on main sheet in a single table (same type of data columnwise, a row
for every course) would be a big help.

So, when you want more detailed help, give some data for us before!


Arvi Laanemets


"Kessie" wrote in message
...
Hi everyone,

How do I propogate data from one worksheet to another in Excel without
having to cut and paste? Is there an easier and accurate way?

My Problem in more Detail:
Right now I have an Excel spreadsheet for listing all my Training Courses.
It's the main worksheet where I enter all my courses that I've taken, plan
to
take, and ones I put on hold. Then I have 3 sub-worksheets that each are
named COURSES TAKEN, COURSES NEEDED, and COURSES PLACED ON HOLD.
So when I enter new information in the main worksheet, I also have to go
to
a sub-worksheet and enter the same info into this sheet. Is there an
easier
way?

Thanks,
Kessie

Hello Arvi,

Thanks for your response. Yes, all my data is on one main sheet on a single
table. (The same type of data is columnwise, and a row for every course -
exactly like you described).

So, how do I propogate specific data from this table if for example I just
want to propogate data about a course called "Effective Presentations." How
do I propogate the data about this course to a new empty table on a new
worksheet?

Thank you,
Kessie


"Arvi Laanemets" wrote:

Hi

Sure it is! (Links, lookups, indexing, ODBC queries etc. are keywords) But
how to do it, depends on design your main worksheet. And having all your
data on main sheet in a single table (same type of data columnwise, a row
for every course) would be a big help.

So, when you want more detailed help, give some data for us before!


Arvi Laanemets


"Kessie" wrote in message
...
Hi everyone,

How do I propogate data from one worksheet to another in Excel without
having to cut and paste? Is there an easier and accurate way?

My Problem in more Detail:
Right now I have an Excel spreadsheet for listing all my Training Courses.
It's the main worksheet where I enter all my courses that I've taken, plan
to
take, and ones I put on hold. Then I have 3 sub-worksheets that each are
named COURSES TAKEN, COURSES NEEDED, and COURSES PLACED ON HOLD.
So when I enter new information in the main worksheet, I also have to go
to
a sub-worksheet and enter the same info into this sheet. Is there an
easier
way?

Thanks,
Kessie

Hi

When there is only one entry with course name "Effective Presentations", and
the column with course name is leftmost in table, and on another sheet you
entered the same course into some cell, then you can use VLOOKUP to populate
cells with rest of data for this course from main sheet - like this:
=VLOOKUP(A2,Main!$A$2:$X$100,4,0)
(the example formula returns the value from 4th column in search range,
where the value in first column matches exactly with value in cell A2. NB!
The first occurrence is found!).
When the key column (course name in your example) isn't leftmost, then with
rest of conditions same as above you have to combine index and match
functions - like this:
=INDEX(Main!$A$2:$A$100,MATCH(A2,Main!$D$2:$D$100, 0),)


When there are many rows which match same condition (probably your case),
then you have 2 options:

a) On other sheets, you get data from main table using ODBC query [you must
have your main table, header row included, defined as non-dynamic named
range (p.e. CourseTable=Main!$A$1:$X$200) before]. And my advice is to use
single-word headers in main table. So on sheet CoursesTaken you create a
query (Data.GetExternalData from menu) with SQL string:
SELECT CourseTable.CourseName, CourseTable.Course.Time, ... FROM ´C:\My
Documents\CourseWorkbook´.CourseTable CourseTable WHERE
CourseTable.CourseName < '' ORDER BY CourseTable.CourseTime,
CourseTable.CourseName;

You can set the query to be refreshed when workbook is opened, or refresh it
manually, or design an Activate event for worksheet, which refreshes the
query whenever you activate the sheet (but always the query returns data
saved last - any changes made on main sheet after last save are left out).

b) On main sheet, you add an additional column, where an identifier for
every row is calculated. P.e. in your example you add an empty column A
(other columns are shifted to left), and into cell A2 enter the formula:
=IF ($B2="";"";MATCH($D2,{"Courses taken";"Courses needed";"Courses placed
on hold";},0)+COUNTIF($D$2:$D2,$D2)/100)
which returns 1.01 for first "Courses taken" in table, 1.02 for second one,
...., 2.01 for first "Courses needed" and so on. Copy the formula down - you
can have it copied for more rows you have actual data in your table - for
future entries. I assumed that you had a CourseGroup column in your main
table on 3rd place.

P.e. on sheet CoursesTaken, when you want the data displayed starting from
row 2 (row 1 is header row), into A2 you enter the formula
=IF(ISERROR(VLOOKUP(1+(ROW()-1)/100,Main!$A$2:$Y$100,2,0)),"",VLOOKUP(1+(ROW
()-1)/100,Main!$A$2:$Y$100,2,0))
into B2 you enter the formula
=IF($A2="","",VLOOKUP(1+(ROW()-1)/100,Main!$A$2:$Y$100,3,0))
etc. (The CourseGroup column you drop of-course)
And then you copy all formulas from row 2 down for number of rows you think
reasonable.

A variation of this solution (I advice this, when there are many groups you
want your main table to divide), is, where you create a report sheet with
selectable group name (you can use validation list for this). On main sheet,
in additional column the value is calculated only for rows, where
CourseGroup matches with one selected on report sheet, i.e. the formula p.e.
for A2 will be something like this:
=IF($D2=Report!$B$1,,"COUNTIF($D$2:$D2,$D2)")
and on Report sheet, when you want data start from row 4 (row 3 is header),
the formula in A4 will be:
=IF(ISERROR(VLOOKUP(ROW()-3,Main!$A$2:$Y$100,2,0)),"",VLOOKUP(ROW()-3,Main!$
A$2:$Y$100,2,0))
etc.
Now, when you select different group in B1, all entries for selected group
are displayed on report sheet.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Kessie" wrote in message
...
Hello Arvi,

Thanks for your response. Yes, all my data is on one main sheet on a
single
table. (The same type of data is columnwise, and a row for every course -
exactly like you described).

So, how do I propogate specific data from this table if for example I just
want to propogate data about a course called "Effective Presentations."
How
do I propogate the data about this course to a new empty table on a new
worksheet?

Thank you,
Kessie


"Arvi Laanemets" wrote:

Hi

Sure it is! (Links, lookups, indexing, ODBC queries etc. are keywords)
But
how to do it, depends on design your main worksheet. And having all your
data on main sheet in a single table (same type of data columnwise, a
row
for every course) would be a big help.

So, when you want more detailed help, give some data for us before!


Arvi Laanemets


"Kessie" wrote in message
...
Hi everyone,

How do I propogate data from one worksheet to another in Excel without
having to cut and paste? Is there an easier and accurate way?

My Problem in more Detail:
Right now I have an Excel spreadsheet for listing all my Training
Courses.
It's the main worksheet where I enter all my courses that I've taken,
plan
to
take, and ones I put on hold. Then I have 3 sub-worksheets that each
are
named COURSES TAKEN, COURSES NEEDED, and COURSES PLACED ON HOLD.
So when I enter new information in the main worksheet, I also have to
go
to
a sub-worksheet and enter the same info into this sheet. Is there an
easier
way?

Thanks,
Kessie

Hi

Sorry, I forget something in SQL string example
SELECT CourseTable.CourseName, CourseTable.Course.Time, ... FROM ´C:\My
Documents\CourseWorkbook´.CourseTable CourseTable WHERE
CourseTable.CourseName < '' AND CourseTable.CourseGroup='Courses taken'
ORDER BY CourseTable.CourseTime, CourseTable.CourseName;

And there was an abundant semicolon in array parametrer of formula
containing MATCH


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Hello Arvil,

Thank you so much for your help. I really appreciate it. I'm glad there are
experts like you on Microsoft website to help dummies like me (laugh).

I will try the examples you gave me. If I get stuck, I'm going to study SQL
and Excel programming more, since I really don't know them that well.

Thank you again!

Kessie:)


"Arvi Laanemets" wrote:

Hi

Sorry, I forget something in SQL string example
SELECT CourseTable.CourseName, CourseTable.Course.Time, ... FROM ´C:\My
Documents\CourseWorkbook´.CourseTable CourseTable WHERE
CourseTable.CourseName < '' AND CourseTable.CourseGroup='Courses taken'
ORDER BY CourseTable.CourseTime, CourseTable.CourseName;

And there was an abundant semicolon in array parametrer of formula
containing MATCH


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Some corrections again for a couple of my example formulas.

I left a couple of semicolons (my Windows is set to use semicolons as
delimiters) not replaced with commas in one formula. The right formula is
below. NB! Semicolons in array parameter (the one wrapped within {}) must
remain ast they are!
=IF($B2="","",MATCH($D2,{"Courses taken";"Courses needed";"Courses placed on
hold"},0)+COUNTIF($D$2:$D2,$D2)/100)

And in another formula I simply typed the function into wrong position. The
right formula is
=IF($D2=Report!$B$1,COUNTIF($D$2:$D2,$D2),"")

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Kessie" wrote in message
...
Hello Arvil,

Thank you so much for your help. I really appreciate it. I'm glad there
are
experts like you on Microsoft website to help dummies like me (laugh).

I will try the examples you gave me. If I get stuck, I'm going to study
SQL
and Excel programming more, since I really don't know them that well.

Thank you again!

Kessie:)


"Arvi Laanemets" wrote:

Hi

Sorry, I forget something in SQL string example
SELECT CourseTable.CourseName, CourseTable.Course.Time, ... FROM ´C:\My
Documents\CourseWorkbook´.CourseTable CourseTable WHERE
CourseTable.CourseName < '' AND CourseTable.CourseGroup='Courses taken'
ORDER BY CourseTable.CourseTime, CourseTable.CourseName;

And there was an abundant semicolon in array parametrer of formula
containing MATCH


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets