ExcelBanter - access or excel

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - access or excel (https://www.excelbanter.com/new-users-excel/196482-access-excel.html)

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

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 Applications
http://www.kan.org/tips

"Bassman" wrote:

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

On Jul 27, 7:18*pm, M Kan <tipsoftheweek at gmail dot com wrote:
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

"Bassman" wrote:
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- Hide quoted text -

- 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.

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

"Bassman" wrote in message
...
On Jul 27, 7:18 pm, M Kan <tipsoftheweek at gmail dot com wrote:
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

"Bassman" wrote:
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- Hide quoted text -

- 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.

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/ExcelTutori...9/Default.aspx

You might also download the 'Reporting' sample at
http://www.edferrero.com/ExcelCharts...2/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/...0/Default.aspx

Ed Ferrero
www.edferrero.com

On Jul 28, 5:38*am, "Ed Ferrero" wrote:
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? 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.

*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' 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. :)

On Jul 28, 8:07*am, Bassman wrote:
On Jul 28, 5:38*am, "Ed Ferrero" wrote:

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? 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.

*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' 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. :)- 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?

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