Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 17th 05, 07:17 AM
KRAMER
 
Posts: n/a
Default Auto Data Transfer

Hi Guy's,
I have created an intergrated system of tracking for my company. Basically I
have one Workbook for Daily Sales and another as a Master Record. I want to
be able to input information into the Master Worksheet and automatically
transfer some of the information to the Daily Worksheet. Columns
A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns
A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching dates
in Column S of the Master and Cell H1 of the Daily sheet. An added problem is
that cell information can regularly be the same and there can be up to 20
different transactions per day.
--
KRAMER

  #2   Report Post  
Old May 17th 05, 03:59 PM
Bernie Deitrick
 
Posts: n/a
Default

Kramer,

Don't do it that way: it's a poor design, one that increases your chance of
introducing errors. Use one workbook, and one database, and use data
filters or pivot tables to view summaries or subsets of your data.

You can add additional columns to your database to use as filter keys. When
you want to see a specific view of your data, filter on the key, or other
parameters, such as the 'matching dates' or the column that would match the
value in cell H1. Or use the pivot table to create the summary.

HTH,
Bernie
MS Excel MVP


"KRAMER" wrote in message
...
Hi Guy's,
I have created an intergrated system of tracking for my company. Basically

I
have one Workbook for Daily Sales and another as a Master Record. I want

to
be able to input information into the Master Worksheet and automatically
transfer some of the information to the Daily Worksheet. Columns
A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns
A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching

dates
in Column S of the Master and Cell H1 of the Daily sheet. An added problem

is
that cell information can regularly be the same and there can be up to 20
different transactions per day.
--
KRAMER



  #3   Report Post  
Old May 17th 05, 11:58 PM
KRAMER
 
Posts: n/a
Default

Thanks Bernie,
The problem there is that I have to e-mail the Daily section every day and
if I combine it with the Master section then I have over 8 MB even zipped
it's still 4 MB which will not happen on dial-up (we're still in the stone
age). I don't know what a pivot table is but if it can be formatted to match
what I have now and can be e-mailed with ease than I will certainly learn
what I can. Same goes with filtering. My only sources of training are the
help menu and Excell 2003 Fast & Easy, so if there is anything else that is
good for beginers, please tell.
Thanks again.
--
KRAMER


"Bernie Deitrick" wrote:

Kramer,

Don't do it that way: it's a poor design, one that increases your chance of
introducing errors. Use one workbook, and one database, and use data
filters or pivot tables to view summaries or subsets of your data.

You can add additional columns to your database to use as filter keys. When
you want to see a specific view of your data, filter on the key, or other
parameters, such as the 'matching dates' or the column that would match the
value in cell H1. Or use the pivot table to create the summary.

HTH,
Bernie
MS Excel MVP


"KRAMER" wrote in message
...
Hi Guy's,
I have created an intergrated system of tracking for my company. Basically

I
have one Workbook for Daily Sales and another as a Master Record. I want

to
be able to input information into the Master Worksheet and automatically
transfer some of the information to the Daily Worksheet. Columns
A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns
A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching

dates
in Column S of the Master and Cell H1 of the Daily sheet. An added problem

is
that cell information can regularly be the same and there can be up to 20
different transactions per day.
--
KRAMER




  #4   Report Post  
Old May 18th 05, 06:26 PM
Bernie Deitrick
 
Posts: n/a
Default

Kramer,

You can easily extract data from a database. Suppose your database has a
date column. Copy the macro below into a codemodule in your workbook. Then
select a single cell in your date column with the date that you want to
extract, and then run the macro. It will create a new sheet that contains
just the data for the that single day. Then emailing that single sheet would
be quicker. The assumption is that your database follows database rules - no
fully empry columns or rows.

HTH,
Bernie
MS Excel MVP

Sub ExtractJustOneDay()
Dim myCell As Range
Dim mySht As Worksheet

Set myCell = ActiveCell
Set mySht = Worksheets.Add
mySht.Name = Format(myCell.Value, "mmm dd, yyyy")
With myCell.CurrentRegion
Intersect(.Cells, myCell.EntireColumn).AutoFilter _
Field:=1, Criteria1:=myCell.Text
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
.AutoFilter
End With
End Sub

"KRAMER" wrote in message
...
Thanks Bernie,
The problem there is that I have to e-mail the Daily section every day and
if I combine it with the Master section then I have over 8 MB even zipped
it's still 4 MB which will not happen on dial-up (we're still in the stone
age). I don't know what a pivot table is but if it can be formatted to

match
what I have now and can be e-mailed with ease than I will certainly learn
what I can. Same goes with filtering. My only sources of training are the
help menu and Excell 2003 Fast & Easy, so if there is anything else that

is
good for beginers, please tell.
Thanks again.
--
KRAMER


"Bernie Deitrick" wrote:

Kramer,

Don't do it that way: it's a poor design, one that increases your chance

of
introducing errors. Use one workbook, and one database, and use data
filters or pivot tables to view summaries or subsets of your data.

You can add additional columns to your database to use as filter keys.

When
you want to see a specific view of your data, filter on the key, or

other
parameters, such as the 'matching dates' or the column that would match

the
value in cell H1. Or use the pivot table to create the summary.

HTH,
Bernie
MS Excel MVP


"KRAMER" wrote in message
...
Hi Guy's,
I have created an intergrated system of tracking for my company.

Basically
I
have one Workbook for Daily Sales and another as a Master Record. I

want
to
be able to input information into the Master Worksheet and

automatically
transfer some of the information to the Daily Worksheet. Columns
A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns
A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching

dates
in Column S of the Master and Cell H1 of the Daily sheet. An added

problem
is
that cell information can regularly be the same and there can be up to

20
different transactions per day.
--
KRAMER








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pull Current Month's Data Out of List - Repost Karl Burrows Excel Discussion (Misc queries) 4 May 3rd 05 01:06 AM
Help - now really stuck! File transfer problem ohboy! Excel Discussion (Misc queries) 10 May 2nd 05 09:07 PM
How do I transfer data between Excell sheets? Grafur Excel Worksheet Functions 2 February 19th 05 03:08 AM
Pivot Table Auto Update Data Source? Ket Excel Worksheet Functions 1 February 18th 05 11:14 PM
Is there a way to get an auto page break as data changes in Excel. george_visalia Excel Discussion (Misc queries) 2 December 16th 04 12:22 AM


All times are GMT +1. The time now is 06:06 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017