PDA

View Full Version : EXCEL CHARTS



josephm
06-06-2008, 08:30 AM
I have an excel workbook that i am designing for some users
i have attached the file i am developing with sample data
here are the requirements.

The workbook will be updated by different users at any time.

the sheet named ''enter information here'' is where each user will enter their information.

The sheet name Summary will pull information from the ''enter information here'' sheet whenever a user adds their information.

the issue now is to remove any duplicate names from the summary sheet, show multiple start and end dates and the total number of days

i want to create a chart from the summary data. but the duplicate are an issue.

so for example:

''Information Systems'' wants to go on vacation three times, thus they are 3 start dates and 3 end dates respectively.

one the summary sheet i need to see
''information systems'' once
3 start and end dates
1 total days value for ''information systems''

how do i use vba to achieve this and still allow the summary sheet to collect the required data form the ''enter information here'' sheet?

i tried using the deletedups macro to delete the duplicates
but it also deletes the sheet references and then when new information is put-in it is not passed to the summary sheet.

i really need help

Bob Phillips
06-06-2008, 09:11 AM
I don't get it. Give me an example of a duplicate.

josephm
06-06-2008, 09:41 AM
ok example:

Information Systems is entered 3 times
Information System has 3 start dates with 3 corresponding end dates.
Information Systems has 3 # of days values

i need a macro to group that data to show

Information Systems
Start Date 1 End Date 1
Start Date 2 End Date 2
Start Date 3 End Date 3
Total number of days for information systems.

if u can access the attachment look at the summary sheet which is supposed to pull data from the 1st sheet as data is entered. then the data on the summary sheet is to be grouped by the employee name, the relevant number of start and end dates for each name and the total number of days for each name

Bob Phillips
06-06-2008, 10:56 AM
So you want to see all of those duplicates, you just want them grouped together?

josephm
06-06-2008, 11:11 AM
yea.
but i want the data automatically brought from the 1st sheet so i dont mess with the original data.

i wanted to try the following but i was unsure as to where to put it if on the workbook or ....

Private Sub Workbook_Open()

Sub CopyData(sourceSheet As Worksheet, targetSheet As Worksheet)

Dim inRange As Range, outRange As Range

Dim x As Integer, y As Integer

Set inRange = sourceSheet.Range("A1")

Set outRange = targetSheet.Range("A1")

For x = 0 To sourceSheet.UsedRange.Rows.Count

For y = 0 To sourceSheet.UsedRange.Columns.Count

outRange.Offset(x, y).Value = inRange.Offset(x, y).Value

Next

End Sub