access or excel

[Bassman]

I have another question. I am not a true newbie but most of what I do
is what I have been shown and I change it to handle what ever project
I am working on.
I need to report sales figures by sales type, by total sales and total
profit,monthly and compare year over, month over and trend 6 months. I
currently have a data input sheet, a parameters sheet for calulating
report month, and a report generating sheet.
Input sheet has sales type in rows and months are one column sales on
profit for total year.
I need to have input for more than one year so I am going to setup a
new input sheet for each year.
The report sheet has foumluas that pull data by month by setup from
parameters formula. The report sheet works fine now. I am trying to
expand to show trends and multi year and the formulas are getting too
big. Maybe there is a different way to go about this. I was thinking
a database would be better idea but I never made a database before nor
made a form to pull data with.
Any ideas would be great...I can post my workbook. (How do I do
that?)

Thanks in advance

 

[M Kan]

How much data (rows and columns) do you have every month? If you're doing a
lot of manipulate of and calcs off the data, then you're probably better off
in Excel. If it's a large data set that you simply need to query and pull
extracts, then you might be better off in Access.

What formulas are getting too big? Maybe there is a better way of getting
to the data you need.

 

[Bassman]

How much data (rows and columns) do you have every month?  If you're doing a
lot of manipulate of and calcs off the data, then you're probably better off
in Excel.  If it's a large data set that you simply need to query and pull
extracts, then you might be better off in Access.

What formulas are getting too big?  Maybe there is a better way of getting
to the data you need.
--
Tips for Excel, Word, PowerPoint and Other Applicationshttp://www.kan.org/tips






- Show quoted text -

here is a sample off the sheet I am copying

=IF(MONTH('Data Entry'!$W$6)=1,('Data Entry'!$G174+'Data Entry'!$G175),
0)+IF(MONTH('Data Entry'!$W$6)=2,('Data Entry'!$H174+'Data Entry'!
$H175),0)+IF(MONTH('Data Entry'!$W$6)=3,('Data Entry'!$I174+'Data
Entry'!$I175),0)+IF(MONTH('Data Entry'!$W$6)=4,('Data Entry'!
$J174+'Data Entry'!$J175),0)+IF(MONTH('Data Entry'!$W$6)=5,('Data
Entry'!$K174+'Data Entry'!$K175),0)+IF(MONTH('Data Entry'!$W$6)=6,
('Data Entry'!$L174+'Data Entry'!$L175),0)+IF(MONTH('Data Entry'!$W
$6)=7,('Data Entry'!$M174+'Data Entry'!$M175),0)+IF(MONTH('Data Entry'!
$W$6)=8,('Data Entry'!$N174+'Data Entry'!$N175),0)+IF(MONTH('Data
Entry'!$W$6)=9,('Data Entry'!$O174+'Data Entry'!$O175),
0)+IF(MONTH('Data Entry'!$W$6)=10,('Data Entry'!$P174+'Data Entry'!
$P175),0)+IF(MONTH('Data Entry'!$W$6)=11,('Data Entry'!$Q174+'Data
Entry'!$Q175),0)+IF(MONTH('Data Entry'!$W$6)=12,('Data Entry'!
$R174+'Data Entry'!$R175),0)

this is so it pulls information from two cell and adds for each month.

 

[Bob Phillips]

You can reduce that to

=IF(OR(MONTH('Data Entry'!$W$6)<0,MONTH('Data Entry'!$W$6)>12),0,
INDEX('Data Entry'!$G174:'Data Entry'!$R174,MONTH('Data
Entry'!$W$6)+INDEX('Data Entry'!$G175:'Data Entry'!$R175,MONTH('Data
Entry'!$W$6))

--
__________________________________
HTH

Bob

How much data (rows and columns) do you have every month? If you're doing
a
lot of manipulate of and calcs off the data, then you're probably better
off
in Excel. If it's a large data set that you simply need to query and pull
extracts, then you might be better off in Access.

What formulas are getting too big? Maybe there is a better way of getting
to the data you need.
--
Tips for Excel, Word, PowerPoint and Other
Applicationshttp://www.kan.org/tips






- Show quoted text -

here is a sample off the sheet I am copying

=IF(MONTH('Data Entry'!$W$6)=1,('Data Entry'!$G174+'Data Entry'!$G175),
0)+IF(MONTH('Data Entry'!$W$6)=2,('Data Entry'!$H174+'Data Entry'!
$H175),0)+IF(MONTH('Data Entry'!$W$6)=3,('Data Entry'!$I174+'Data
Entry'!$I175),0)+IF(MONTH('Data Entry'!$W$6)=4,('Data Entry'!
$J174+'Data Entry'!$J175),0)+IF(MONTH('Data Entry'!$W$6)=5,('Data
Entry'!$K174+'Data Entry'!$K175),0)+IF(MONTH('Data Entry'!$W$6)=6,
('Data Entry'!$L174+'Data Entry'!$L175),0)+IF(MONTH('Data Entry'!$W
$6)=7,('Data Entry'!$M174+'Data Entry'!$M175),0)+IF(MONTH('Data Entry'!
$W$6)=8,('Data Entry'!$N174+'Data Entry'!$N175),0)+IF(MONTH('Data
Entry'!$W$6)=9,('Data Entry'!$O174+'Data Entry'!$O175),
0)+IF(MONTH('Data Entry'!$W$6)=10,('Data Entry'!$P174+'Data Entry'!
$P175),0)+IF(MONTH('Data Entry'!$W$6)=11,('Data Entry'!$Q174+'Data
Entry'!$Q175),0)+IF(MONTH('Data Entry'!$W$6)=12,('Data Entry'!
$R174+'Data Entry'!$R175),0)

this is so it pulls information from two cell and adds for each month.

 

[Ed Ferrero]

Hi Bassman,

Free advice, judge its value carefully

I need to have input for more than one year so I am going to setup a
new input sheet for each year.

I would keep all data on one sheet. Have a year column and a month
column. That way you can easily summarise data for any month/year.

The report sheet has foumluas that pull data by month by setup from
parameters formula. The report sheet works fine now. I am trying to
expand to show trends and multi year and the formulas are getting too
big. Maybe there is a different way to go about this.

Have you looked at pivot tables to summarise data? See
http://www.edferrero.com/ExcelTutorials/PivotTableTutorial2003/tabid/89/Default.aspx

You might also download the 'Reporting' sample at
http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx
This is an Excel chart sample, but the referencing techniques can
also be used for most report types.

I was thinking
a database would be better idea but I never made a database before nor
made a form to pull data with.

Stick to Excel for now. See my tutorial on building Excel 'databases' at
http://edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx

Ed Ferrero
www.edferrero.com

 

[Bassman]

Hi Bassman,

Free advice, judge its value carefully


I would keep all data on one sheet. Have a year column and a month
column. That way you can easily summarise data for any month/year.


Have you looked at pivot tables to summarise data? Seehttp://www.edferrero.com/ExcelTutorials/PivotTableTutorial2003/tabid/...

You might also download the 'Reporting' sample athttp://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx
This is an Excel chart sample, but the referencing techniques can
also be used for most report types.


Stick to Excel for now. See my tutorial on building Excel 'databases' athttp://edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/...

Ed Ferrerowww.edferrero.com

Thank you for the reply.
One quick question (i am off to work), how do I have one column for
month and one for year if infomation is one entry, ie Jan 2007=
13,568.00 , Jan 2008=15,261.00? Maybe I need to learn how to setup a
database before I learn to report. I got the report part pretty good,
my formulas seem to work.

 

[Bassman]

Thank you for the reply.
One quick question (i am off to work), how do I have one column for
month and one for year if infomation is one entry, ie Jan 2007=
13,568.00 , Jan 2008=15,261.00?  Maybe I need to learn how to setup a
database before I learn to report.  I got the report part pretty good,
my formulas seem to work. - Hide quoted text -

- Show quoted text -

I need to show comaprison on more than one type like total sale/
profit for many items compared to last year and last month. Can I use
a pivot table for that?

 

[Ed Ferrero]

One quick question (i am off to work), how do I have one column for

month and one for year if infomation is one entry, ie Jan 2007=
13,568.00 , Jan 2008=15,261.00? Maybe I need to learn how to setup a
database before I learn to report. I got the report part pretty good,
my formulas seem to work.

Formulas will work. If A1 has the date,
=YEAR(A1)
=MONTH(A1)

If you use a pivot table, the date column can be grouped by Year, Month,
Quarter. All done for you by the pivot table.

I need to show comaprison on more than one type like total sale/
profit for many items compared to last year and last month. Can I use
a pivot table for that?

Yes it can. Take a look at the tutorial and have a play.

Ed Ferrero
www.edferrero.com