Point of Sale

[Louis111]

Hi,

I run a retail store and use excel to keep a list of all products sold by
having a source page where all the info is stored about all products and then
i have a page linked to the source page that when i enter the product number
the description and selling price of the product appears. This has been a
good way of just keeping a list/database of sales.

What i would like to do is to create a form where i could link the form to
the source and enter the product number on the form and then the product and
the description would appear on the form, i would then have an cell where to
enter the amount tendered and the change that must be given to the customer.
After the sale is complete i would like that item that was sold to be added
automatically to a database which i could use to see our daily or weekly
sales.

Any help would be appreciated.

Louis.

 

[Martin Fishlock]

Dear Louis:

You need to use vlookup to get the information from the database.

Vlookup takes a key (product code) and them finds the item in the database
and get the specific cell.

This will propulate the form with the required data (desc, price, qty in stk
[stock out] etc.)

You then need to write some code to get the form details entered into the
database. I suggest that one way is to have a button that it is for confirmed
sale and this prints the invoice and updates your records as required. That
is quite a bit of work and a little difficult to explain.

But what you do is:

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Note you also need to deal with returns and losses.

Good luck

 

[Louis111]

Hi Martin,

Thanks for the reply,

You understand exactly what i am trying to do

what you suggest is correct the problem is how to write the code to

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Is this written by using macros - or is there some easier way to do it.

Thanks,

Louis

Dear Louis:

You need to use vlookup to get the information from the database.

Vlookup takes a key (product code) and them finds the item in the database
and get the specific cell.

This will propulate the form with the required data (desc, price, qty in stk
[stock out] etc.)

You then need to write some code to get the form details entered into the
database. I suggest that one way is to have a button that it is for confirmed
sale and this prints the invoice and updates your records as required. That
is quite a bit of work and a little difficult to explain.

But what you do is:

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Note you also need to deal with returns and losses.

Good luck

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Hi,

I run a retail store and use excel to keep a list of all products sold by
having a source page where all the info is stored about all products and then
i have a page linked to the source page that when i enter the product number
the description and selling price of the product appears. This has been a
good way of just keeping a list/database of sales.

What i would like to do is to create a form where i could link the form to
the source and enter the product number on the form and then the product and
the description would appear on the form, i would then have an cell where to
enter the amount tendered and the change that must be given to the customer.
After the sale is complete i would like that item that was sold to be added
automatically to a database which i could use to see our daily or weekly
sales.

Any help would be appreciated.

Louis.

 

[Martin Fishlock]

Louis:

It may get a bit technical the following but if you play around you may get
somewhere.

To find the last row in the database use a function like this, where column
a contains data and there are on subtotals or other items below the last
record and DB is the name of the sheet:

Function getlastrow(ByVal sheetname As String) As Long
Dim a As Long
With Sheets(sheetname)
a = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row + 1
End With
getlastrow = a
End Function

To get the data from the form use a macro if it is a vba dialog form of if
it is a spreadsheet form use the following

' spreadsheet....
a= getlastrow("DB")
sheet("DB").cells( a,1) = sheet("form").range("B6") ' say product id
sheet("DB").cells( a,2) = sheet("form").range("D6") ' say quantity....
'.....repeat for each data entry in the form.

' form...
a= getlastrow("DB") ' get last row needs to be included in
' the form code or in the modules and
made public.
sheet("DB").cells( a, 1) = Me.TextBox1.Value
'.....repeat for each data entry in the form.

To get the items in the database use the match function in a macro using

a = worksheetfunction.match(key, db, 0) ' gives the offset from the start
of the db

therefore you use the cells function to add it to the db sheet.
--

You can email me the sheet for me to look at my name below with _ in the
middle @yahoo.co.uk and it may help a little.


Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Hi Martin,

Thanks for the reply,

You understand exactly what i am trying to do

what you suggest is correct the problem is how to write the code to

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Is this written by using macros - or is there some easier way to do it.

Thanks,

Louis

Dear Louis:

You need to use vlookup to get the information from the database.

Vlookup takes a key (product code) and them finds the item in the database
and get the specific cell.

This will propulate the form with the required data (desc, price, qty in stk
[stock out] etc.)

You then need to write some code to get the form details entered into the
database. I suggest that one way is to have a button that it is for confirmed
sale and this prints the invoice and updates your records as required. That
is quite a bit of work and a little difficult to explain.

But what you do is:

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Note you also need to deal with returns and losses.

Good luck

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Hi,

I run a retail store and use excel to keep a list of all products sold by
having a source page where all the info is stored about all products and then
i have a page linked to the source page that when i enter the product number
the description and selling price of the product appears. This has been a
good way of just keeping a list/database of sales.

What i would like to do is to create a form where i could link the form to
the source and enter the product number on the form and then the product and
the description would appear on the form, i would then have an cell where to
enter the amount tendered and the change that must be given to the customer.
After the sale is complete i would like that item that was sold to be added
automatically to a database which i could use to see our daily or weekly
sales.

Any help would be appreciated.

Louis.

 

[Louis111]

Hi Martin,

It looks real complicated i will need a bit of time to play around with it,
i'll get back to you as soon as i've tried it out, thanks for the help.

Louis

Louis:

It may get a bit technical the following but if you play around you may get
somewhere.

To find the last row in the database use a function like this, where column
a contains data and there are on subtotals or other items below the last
record and DB is the name of the sheet:

Function getlastrow(ByVal sheetname As String) As Long
Dim a As Long
With Sheets(sheetname)
a = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row + 1
End With
getlastrow = a
End Function

To get the data from the form use a macro if it is a vba dialog form of if
it is a spreadsheet form use the following

' spreadsheet....
a= getlastrow("DB")
sheet("DB").cells( a,1) = sheet("form").range("B6") ' say product id
sheet("DB").cells( a,2) = sheet("form").range("D6") ' say quantity....
'.....repeat for each data entry in the form.

' form...
a= getlastrow("DB") ' get last row needs to be included in
' the form code or in the modules and
made public.
sheet("DB").cells( a, 1) = Me.TextBox1.Value
'.....repeat for each data entry in the form.

To get the items in the database use the match function in a macro using

a = worksheetfunction.match(key, db, 0) ' gives the offset from the start
of the db

therefore you use the cells function to add it to the db sheet.
--

You can email me the sheet for me to look at my name below with _ in the
middle @yahoo.co.uk and it may help a little.


Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Hi Martin,

Thanks for the reply,

You understand exactly what i am trying to do

what you suggest is correct the problem is how to write the code to

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Is this written by using macros - or is there some easier way to do it.

Thanks,

Louis

Dear Louis:

You need to use vlookup to get the information from the database.

Vlookup takes a key (product code) and them finds the item in the database
and get the specific cell.

This will propulate the form with the required data (desc, price, qty in stk
[stock out] etc.)

You then need to write some code to get the form details entered into the
database. I suggest that one way is to have a button that it is for confirmed
sale and this prints the invoice and updates your records as required. That
is quite a bit of work and a little difficult to explain.

But what you do is:

1. Get the data from the form
2. Find last row in sales database and add it
3. Update the mast record

Note you also need to deal with returns and losses.

Good luck

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


:

Hi,

I run a retail store and use excel to keep a list of all products sold by
having a source page where all the info is stored about all products and then
i have a page linked to the source page that when i enter the product number
the description and selling price of the product appears. This has been a
good way of just keeping a list/database of sales.

What i would like to do is to create a form where i could link the form to
the source and enter the product number on the form and then the product and
the description would appear on the form, i would then have an cell where to
enter the amount tendered and the change that must be given to the customer.
After the sale is complete i would like that item that was sold to be added
automatically to a database which i could use to see our daily or weekly
sales.

Any help would be appreciated.

Louis.