If I was doing this all in Access, I prmoise I wouldn't be bugging you so
much! (unfortunately I'm the only one who has access, and this has to be
shared with others)

Okay, I have a sheet with two columns. One lists the product name and the
other lists the product price. (That's easy)

On another sheet, I'd like to be able to create a lookup for this list. This
is how I'd like it to happen. Someone tell me if I'm off my nut and it can't
be done this way!

Sheet 1: Contains the product list described above.

Sheet 2: Contains a generic form that, say, a salesperson is filling out. It
contains a checkbox selection so that the person can just check off all the
products that a customer would be buying.

Sheet 3: Here's the tricky part. I want all of the selections to list on
this sheet, along with their prices. Now, I know how to use Vlookup to get
the price showing up once the product is there. My two problems a

(A) How do I get the products that are checked off to list on Sheet 3.
(B) Is it possible to confine X amount of products per sheet? I mean, if a
salesperson checks off 52 products, and I know only 20 will print per page,
can I ensure that only 20 checked off products will go to Sheet 3, 20 to
Sheet 4, and 12 to Sheet 5?

This may not work for you.

First, I'd try to let excel figure out the paging. If you have descriptions
that vary in length (even wrapped text in a single cell), it could be pretty
ugly (and for sure not much fun).

If you have to put headers on a separate worksheet and print that separately,
that would be ok in my book.

I think I'd put checkboxes from the Forms toolbar in column A.
Link each text box to the cell that it's in.
Give that cell a custom format of ;;; (3 semicolons) to hide the true/false

Then give the user a little macro that would delete/hide all the rows that have
a false in column A.

This hides those rows.
Option Explicit
Sub HideRows()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With ActiveSheet
FirstRow = 3 ' a few header rows
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = False Then
'choose one of the following 2 lines
.Rows(iRow).Hidden = True
'.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

'this shows them so that they could make changes, too:
Sub ShowRows()
ActiveSheet.Rows.AutoFit
End Sub


In fact, I think I'd keep a master worksheet (hidden) and give the user a macro
that would copy that sheet -- just so they could start another project:

Option Explicit
Sub MakeNewSheet()
Application.ScreenUpdating = False
With Worksheets("Master")
.Visible = xlSheetVisible
.Copy _
befo=Worksheets(1)
.Visible = xlSheetVeryHidden
End With

With Worksheets(1)
.Name = Format(Now, "yyyymmdd_hhmmss")
End With
Application.ScreenUpdating = True
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Abi wrote:

If I was doing this all in Access, I prmoise I wouldn't be bugging you so
much! (unfortunately I'm the only one who has access, and this has to be
shared with others)

Okay, I have a sheet with two columns. One lists the product name and the
other lists the product price. (That's easy)

On another sheet, I'd like to be able to create a lookup for this list. This
is how I'd like it to happen. Someone tell me if I'm off my nut and it can't
be done this way!

Sheet 1: Contains the product list described above.

Sheet 2: Contains a generic form that, say, a salesperson is filling out. It
contains a checkbox selection so that the person can just check off all the
products that a customer would be buying.

Sheet 3: Here's the tricky part. I want all of the selections to list on
this sheet, along with their prices. Now, I know how to use Vlookup to get
the price showing up once the product is there. My two problems a

(A) How do I get the products that are checked off to list on Sheet 3.
(B) Is it possible to confine X amount of products per sheet? I mean, if a
salesperson checks off 52 products, and I know only 20 will print per page,
can I ensure that only 20 checked off products will go to Sheet 3, 20 to
Sheet 4, and 12 to Sheet 5?

--

Dave Peterson