[David]

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[Gary''s Student]

You can use autofilter:

In A1 thru C7:

item value author
Concert 1 $0.00
Concert 2 $0.00
Concert 3 $75.00 3333
Concert 4 $0.00
Concert 5 $25.00 5555
Concert 6 $0.00

click on C1 and pull-down:
Data Filter AutoFilter

then using the C1 pull-down, select NonBlanks

You should see:


item value author
Concert 3 $75.00 3333
Concert 5 $25.00 5555


Then just copy and paste.
--
Gary''s Student - gsnu200735

[David]

gsnu200735:

Thanks for the reply. Yes, I could use autofilter... never crossed my mind.

I was hoping for a formula that would automatically post the numbers on the
summery sheet when the data was entered on the worksheet. That way I can
avoid the extra steps involved with autofilter and cut and paste.

At the end of the day, I am 1) tired and may forget and 2) the office can be
NUTS with last minute activities and I may forget. Read FORGET! lol at myself.

I was hoping to enter everything on the worksheet and go to the summery
sheet and print.

This is a new job for me.... 3rd day was Friday... right now the office
manually inputs all the data on to a word processing document they have used
for years by way of using a hand calculator and pencil and paper.

I was hoping to bring the office up to the end of the 20th century or at
least to the millennium! lol
--
David


"Gary''s Student" wrote:

You can use autofilter:

In A1 thru C7:

item value author
Concert 1 $0.00
Concert 2 $0.00
Concert 3 $75.00 3333
Concert 4 $0.00
Concert 5 $25.00 5555
Concert 6 $0.00

click on C1 and pull-down:
Data Filter AutoFilter

then using the C1 pull-down, select NonBlanks

You should see:


item value author
Concert 3 $75.00 3333
Concert 5 $25.00 5555


Then just copy and paste.
--
Gary''s Student - gsnu200735

[BriSwy]

"David" wrote:

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[BriSwy]

In addition to the last solution, you could embed that AutoFilter into a
macro. Additionally, the macro would also include a select all/copy/paste
into the second sheet, then after the paste was completed, turn off the
AutoFilter. Lastly, you could then create a button on the sheet you were
working in and hitting it as much as you want wouldn't hurt anything.

Hope this was useful.

"David" wrote:

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[David]

How do I do all that?
--
David


"BriSwy" wrote:

In addition to the last solution, you could embed that AutoFilter into a
macro. Additionally, the macro would also include a select all/copy/paste
into the second sheet, then after the paste was completed, turn off the
AutoFilter. Lastly, you could then create a button on the sheet you were
working in and hitting it as much as you want wouldn't hurt anything.

Hope this was useful.

"David" wrote:

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[BriSwy]

Assuming that you have the following data in a sheet at A1:

Event Ticket Price Tickets Sold Total Auth
Concert1 25 3 75 333
Concert2 35 6 210 444
Concert3 60 2 120 555
Concert4 75 0 0
Concert5 40 7 280 666
Concert6 40 0 0
Concert7 10 2 20 777
Concert8 60 7 420 888
Concert9 35 8 280 999
Concert10 40 0 0
Concert11 55 0 0

You could then go into the TOOLSMACRORECORD NEW MACRO. Once you name your
macro (I called this example DailyDump), then it will automatically turn on
the record and trace all your steps. Here you'll want to:
-highlight the first row in your sheet
-turn on AutoFilter
-specify your filter criteria
-run the filter
-select all in the sheet and copy
-paste into desired sheet
-return to original sheet
-turn off AutoFilter
-go to cell A1

Now you can turn off the recorder, and this is what you will see when you go
to your Macros and edit the one we just created.

Sub DailyDump()
'
' Macro1 Macro
'

'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="0", Operator:=xlAnd
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub


Let me know when you get this far, then I can lead you through creating a
button (although you can just run it without a button as well.

"David" wrote:

How do I do all that?
--
David


"BriSwy" wrote:

In addition to the last solution, you could embed that AutoFilter into a
macro. Additionally, the macro would also include a select all/copy/paste
into the second sheet, then after the paste was completed, turn off the
AutoFilter. Lastly, you could then create a button on the sheet you were
working in and hitting it as much as you want wouldn't hurt anything.

Hope this was useful.

"David" wrote:

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[David]

BriSwy,

THANK YOU! THANK YOU! I will try tis out on the "dumby" sheet I have been
working on at home and then transfer what I learn to the office.

I am not at the office this afternoon so I have printed your reply and will
take this into the office on Monday morning and follow your instructions.

If I have trouble I will post a "HELP BriSwy" notice for you to see.

--
David


"BriSwy" wrote:

Assuming that you have the following data in a sheet at A1:

Event Ticket Price Tickets Sold Total Auth
Concert1 25 3 75 333
Concert2 35 6 210 444
Concert3 60 2 120 555
Concert4 75 0 0
Concert5 40 7 280 666
Concert6 40 0 0
Concert7 10 2 20 777
Concert8 60 7 420 888
Concert9 35 8 280 999
Concert10 40 0 0
Concert11 55 0 0

You could then go into the TOOLSMACRORECORD NEW MACRO. Once you name your
macro (I called this example DailyDump), then it will automatically turn on
the record and trace all your steps. Here you'll want to:
-highlight the first row in your sheet
-turn on AutoFilter
-specify your filter criteria
-run the filter
-select all in the sheet and copy
-paste into desired sheet
-return to original sheet
-turn off AutoFilter
-go to cell A1

Now you can turn off the recorder, and this is what you will see when you go
to your Macros and edit the one we just created.

Sub DailyDump()
'
' Macro1 Macro
'

'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="0", Operator:=xlAnd
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub


Let me know when you get this far, then I can lead you through creating a
button (although you can just run it without a button as well.

"David" wrote:

How do I do all that?
--
David


"BriSwy" wrote:

In addition to the last solution, you could embed that AutoFilter into a
macro. Additionally, the macro would also include a select all/copy/paste
into the second sheet, then after the paste was completed, turn off the
AutoFilter. Lastly, you could then create a button on the sheet you were
working in and hitting it as much as you want wouldn't hurt anything.

Hope this was useful.

"David" wrote:

Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David

[Max]

One way using formulas to get the required results ..

Illustrated in this sample:
http://www.flypicture.com/download/ODE0NA==
Copy only lines with data in key col over.xls

Source data as posted assumed in sheet: receipts entry worksheet,
in cols A to C from row2 down, with key col = col C, Auth#

In sheet: Cash and charge summary,

In A2:
=IF('receipts entry worksheet'!C2="","",ROW())
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX('receipts entry
worksheet'!A:A,SMALL($A:$A,ROWS($1:1))))

Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
source data in "receipts entry worksheet".

Hide away col A. Cols B to D will return the required results, all neatly
bunched at the top, ie only the lines from "receipts entry worksheet" with
charge authorization numbers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David" wrote:
Greetings:
A B C

Concert 1 $ 00.00
Concert 2 $ 00.00
Concert 3 $ 75.00 3333
Concert 4 $ 00.00
Concert 5 $ 25.00 5555
Concert 6 $ 00.00

I am working between two worksheets in the same workbook.

In one worksheet called €œreceipts entry worksheet€� I have 3 columns, the
first column is concert date, the second column is the total amount of
individual tickets sold and the third column is the charge authorization
number. I have to record the individual tickets sold per concert date on a
daily basis.

I can copy cell contents from one worksheet to another: ='Receipts Entry
WorkSheet'!E19 with no problem. I dont know how to write a formula if the
cell is empty. I think this is an IF type formula but I could be mistaken

I want to transfer the amount of individual tickets sold for each concert
AND the authorization number to another worksheet called €œCash and charge
summery€�. If there is no authorization number there is nothing to transfer.

So using the above example I would only transfer the amounts and
authorization numbers for concerts 3 and 5. This could and will change
everyday. Some days all the concerts have individual ticket sales and some
days only one concert would have a ticket sale and then again some days no
individual ticket are sold.

Question: If there a way to transfer amounts and authorization numbers to
another worksheet only if there is a charge authorization number?


--
David