Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling.

I have an order form for supplies that is updated monthly for various
facilities. I want to store the information from each order onto
separate sheets, according to facility and month.

The order form is set up like this:
Facilty Name
Date of Order

Product ID Product Name Quanity Wanted Unit Price
Total


I want the Quantity Wanted, Unit Price, and Total transferred to a
worksheet for the corresponding facility.

Each facility sheet has a list of the products. I was planning on
having the months across the top row.

I have spent many hours trying to do this using conditional
information about facility and date and then using lookup tables to
get the information regarding quanity and price. It worked for
January - then as soon as I change the date, the information in
January disappeared.

I hope this makes sense. Any help would be appreciated. I also tried
writing code for it using a command button to update the month. I
didn't have much luck with that.

Thank you in advance for any help.

Carolyn

Tom,

As always, thank you for your help. I am using an excel sheet to
enter the order information on. I am going to use a pivot table and
store the information on one sheet. I am not sure how to do it using
excel, but I am going to try to figure it out. I would like to write
a macro moving information to the database sheet, but am clueless.
Could you point me in a direction?

Thank you again for your help.

Carolyn

"Tom Ogilvy" wrote in message ...
Why not make a single worksheet as a database of all your orders. Then you
can easily make your individual report using a pivot table under the data
menu.

when you say form, is this a spread sheet where the user enters data in the
cells?

If so, you would probably need a macro to move a copy of the data to your
master database.

Using formulas isn't really practical - as you state, the formulas
recalculate each time the data changes.


--
Regards,
Tom Ogilvy

"Carolyn" wrote in message
om...
Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling.

I have an order form for supplies that is updated monthly for various
facilities. I want to store the information from each order onto
separate sheets, according to facility and month.

The order form is set up like this:
Facilty Name
Date of Order

Product ID Product Name Quanity Wanted Unit Price
Total


I want the Quantity Wanted, Unit Price, and Total transferred to a
worksheet for the corresponding facility.

Each facility sheet has a list of the products. I was planning on
having the months across the top row.

I have spent many hours trying to do this using conditional
information about facility and date and then using lookup tables to
get the information regarding quanity and price. It worked for
January - then as soon as I change the date, the information in
January disappeared.

I hope this makes sense. Any help would be appreciated. I also tried
writing code for it using a command button to update the month. I
didn't have much luck with that.

Thank you in advance for any help.

Carolyn

I guess I am more clueless than I thought about the pivot tables. How
would I incorporate the month and the location in the table? I am so
lost and am probably brain dead after looking at this spreadsheet for
so long.

Thank you for any help you can provide.

"Tom Ogilvy" wrote in message ...
Why not make a single worksheet as a database of all your orders. Then you
can easily make your individual report using a pivot table under the data
menu.

when you say form, is this a spread sheet where the user enters data in the
cells?

If so, you would probably need a macro to move a copy of the data to your
master database.

Using formulas isn't really practical - as you state, the formulas
recalculate each time the data changes.


--
Regards,
Tom Ogilvy

"Carolyn" wrote in message
om...
Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling.

I have an order form for supplies that is updated monthly for various
facilities. I want to store the information from each order onto
separate sheets, according to facility and month.

The order form is set up like this:
Facilty Name
Date of Order

Product ID Product Name Quanity Wanted Unit Price
Total


I want the Quantity Wanted, Unit Price, and Total transferred to a
worksheet for the corresponding facility.

Each facility sheet has a list of the products. I was planning on
having the months across the top row.

I have spent many hours trying to do this using conditional
information about facility and date and then using lookup tables to
get the information regarding quanity and price. It worked for
January - then as soon as I change the date, the information in
January disappeared.

I hope this makes sense. Any help would be appreciated. I also tried
writing code for it using a command button to update the month. I
didn't have much luck with that.

Thank you in advance for any help.

Carolyn

Sub MoveDate()
' find the next available row on the sheet Data
set rng = Worksheets("Data").Cells(rows.count,1).End(xlup)(2 )
With Worksheets("Input")
rng.Value = .Range("B1").Value
rng.offset(0,1).Value = .Range("B2").Value
rng.offset(0,2).Value = .Range("A4").Value
rng.offset(0,3).Value = .Range("B4").Value
rng.offset(0,4).Value = .range("C4").Value
End Sub

if you will have multiple lines of data on shee Input starting in A4

Sub MoveData()

With Worksheets("Input")
for each cell in .Range(.Cells(4,1),.Cells(rows.count,1).End(xlup)
set rng = Worksheets("Data").Cells(rows.count,1).End(xlup)(2 )
rng.Value = .Range("B1")
rng.offset(0,1).Value = .Range("B2")
rng.offset(0,2).Value = .cell.Value
rng.offset(0,3).Value = .cell.offset(0,1).Value
rng.offset(0,4).Value = .cell.offset(0,1).Value
Next
End With
End Sub

--
Regards,
Tom Ogilvy



"Carolyn" wrote in message
om...
Tom,

As always, thank you for your help. I am using an excel sheet to
enter the order information on. I am going to use a pivot table and
store the information on one sheet. I am not sure how to do it using
excel, but I am going to try to figure it out. I would like to write
a macro moving information to the database sheet, but am clueless.
Could you point me in a direction?

Thank you again for your help.

Carolyn

"Tom Ogilvy" wrote in message
...
Why not make a single worksheet as a database of all your orders. Then
you
can easily make your individual report using a pivot table under the
data
menu.

when you say form, is this a spread sheet where the user enters data in
the
cells?

If so, you would probably need a macro to move a copy of the data to
your
master database.

Using formulas isn't really practical - as you state, the formulas
recalculate each time the data changes.


--
Regards,
Tom Ogilvy

"Carolyn" wrote in message
om...
Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling.

I have an order form for supplies that is updated monthly for various
facilities. I want to store the information from each order onto
separate sheets, according to facility and month.

The order form is set up like this:
Facilty Name
Date of Order

Product ID Product Name Quanity Wanted Unit Price
Total


I want the Quantity Wanted, Unit Price, and Total transferred to a
worksheet for the corresponding facility.

Each facility sheet has a list of the products. I was planning on
having the months across the top row.

I have spent many hours trying to do this using conditional
information about facility and date and then using lookup tables to
get the information regarding quanity and price. It worked for
January - then as soon as I change the date, the information in
January disappeared.

I hope this makes sense. Any help would be appreciated. I also tried
writing code for it using a command button to update the month. I
didn't have much luck with that.

Thank you in advance for any help.

Carolyn

your data table would look like this

Facilty Name,Date of Order,Product ID,Product Name,Quanity Wanted,Unit
Price,Total

where the commas indicate each separate column.

The macro I provided would need code added to write unit price and total as
well, but you should see the pattern.

In the pivot table, you would group on date (group at the month level

--
Regards,
Tom Ogilvy


"Carolyn" wrote in message
m...
I guess I am more clueless than I thought about the pivot tables. How
would I incorporate the month and the location in the table? I am so
lost and am probably brain dead after looking at this spreadsheet for
so long.

Thank you for any help you can provide.

"Tom Ogilvy" wrote in message
...
Why not make a single worksheet as a database of all your orders. Then
you
can easily make your individual report using a pivot table under the
data
menu.

when you say form, is this a spread sheet where the user enters data in
the
cells?

If so, you would probably need a macro to move a copy of the data to
your
master database.

Using formulas isn't really practical - as you state, the formulas
recalculate each time the data changes.


--
Regards,
Tom Ogilvy

"Carolyn" wrote in message
om...
Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling.

I have an order form for supplies that is updated monthly for various
facilities. I want to store the information from each order onto
separate sheets, according to facility and month.

The order form is set up like this:
Facilty Name
Date of Order

Product ID Product Name Quanity Wanted Unit Price
Total


I want the Quantity Wanted, Unit Price, and Total transferred to a
worksheet for the corresponding facility.

Each facility sheet has a list of the products. I was planning on
having the months across the top row.

I have spent many hours trying to do this using conditional
information about facility and date and then using lookup tables to
get the information regarding quanity and price. It worked for
January - then as soon as I change the date, the information in
January disappeared.

I hope this makes sense. Any help would be appreciated. I also tried
writing code for it using a command button to update the month. I
didn't have much luck with that.

Thank you in advance for any help.

Carolyn