| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents that meet date and list name criteria
I am trying to do a spreadsheetfor my accounts.I need to create a drop down
list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
What constitutes a match? Otto
"John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
I Have rows of input data as follows under
Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
"Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
John
You have to provide the logic behind what you want to happen for anyone to help you. You say "fuel that falls in the month". What month? Provide some logic of what you want by month. You said that you want to copy something to another sheet. What do you want to copy? Where do you want to place it? It would help if you provided a step-by-step procedure of what you would do if you were doing it all manually. HTH Otto "John cheshire lad" wrote in message ... I Have rows of input data as follows under Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
Otto
Thanks for that, sorry if it was not clear. Data is entered in this section I would like to choose the selection for pay method by drop down list. Pay Bus Date Method Fuel Stock Phone Stationary Travel 4/04/2008 Card £79.99 04/04/2008 Bank £11.55 07/04/2008 Cash £2.94 08/04/2008 Chq £6.07 02/04/2008 Card £15.78 02/05/2008 Bank £10.99 03/05/2008 Cash £3.88 ================================================== === Totals in the month are listed in another part on the sheet to show monthly totals by type of payment.If it makes it easier I could use one sheet for each month thus simplifying the date sorted. Any help would be greatly appreciated and thanks for guidance so far. John Card Cash Chq Bank Fuel £95.77 Bus Stock Phone £22.54 Stationary £2.94 Travel £6.07 ================================================== = "Otto Moehrbach" wrote: John You have to provide the logic behind what you want to happen for anyone to help you. You say "fuel that falls in the month". What month? Provide some logic of what you want by month. You said that you want to copy something to another sheet. What do you want to copy? Where do you want to place it? It would help if you provided a step-by-step procedure of what you would do if you were doing it all manually. HTH Otto "John cheshire lad" wrote in message ... I Have rows of input data as follows under Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
John
That's good. You have a bunch of rows of data with the date in, I'll assume, Column A and the method of payment in, I'll assume, Column B. I'll assume this data occupies 7 columns. I'll assume you have headers in row 1 and your data starts in row 2. Starting in Column J, I'll assume you have headers of Month, Fuel, Stock, Phone, Stationary, and Travel. Under the Month header, you have the numbers 1-12 running down the column. You want, say Fuel, all the payments for fuel in a given month to be summed and placed in the Fuel column in the corresponding row month. The same for all other purchase types and months. Is this correct? I'm assuming that you are not asking for help with the Data Validation (drop-down) setup. Is that right? HTH Otto "John cheshire lad" wrote in message ... Otto Thanks for that, sorry if it was not clear. Data is entered in this section I would like to choose the selection for pay method by drop down list. Pay Bus Date Method Fuel Stock Phone Stationary Travel 4/04/2008 Card £79.99 04/04/2008 Bank £11.55 07/04/2008 Cash £2.94 08/04/2008 Chq £6.07 02/04/2008 Card £15.78 02/05/2008 Bank £10.99 03/05/2008 Cash £3.88 ================================================== === Totals in the month are listed in another part on the sheet to show monthly totals by type of payment.If it makes it easier I could use one sheet for each month thus simplifying the date sorted. Any help would be greatly appreciated and thanks for guidance so far. John Card Cash Chq Bank Fuel £95.77 Bus Stock Phone £22.54 Stationary £2.94 Travel £6.07 ================================================== = "Otto Moehrbach" wrote: John You have to provide the logic behind what you want to happen for anyone to help you. You say "fuel that falls in the month". What month? Provide some logic of what you want by month. You said that you want to copy something to another sheet. What do you want to copy? Where do you want to place it? It would help if you provided a step-by-step procedure of what you would do if you were doing it all manually. HTH Otto "John cheshire lad" wrote in message ... I Have rows of input data as follows under Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
Otto
Its great to hear from you. I am a little confused with this discussion board.I tried to give a pictorial view of spreadsheet, but when the post is opened up it runs all the data together and loses positioning. If I just view John I can see the layout, but I do not know which view you have. Moving on. Data is entered in colums A ( Date) to G, with headers in row 1 with data being added in rows below, 1-40. Column B is for payment Type by drop down list. ( I am ok with validation) Somewhere on the sheet I need to record the amounts spent on each type of expense and by each payment method. i.e. I need to know for instance how much was spent on fuel by cash, card cheque or bank (bacs) BANK..............CARD.......CHEQUE,,,,,,CASH FUEL 12.5................23.44 PHONE i HOPE THIS HELPS AND THANKS FOR YOUR PATIENCE. jOHN "Otto Moehrbach" wrote: John That's good. You have a bunch of rows of data with the date in, I'll assume, Column A and the method of payment in, I'll assume, Column B. I'll assume this data occupies 7 columns. I'll assume you have headers in row 1 and your data starts in row 2. Starting in Column J, I'll assume you have headers of Month, Fuel, Stock, Phone, Stationary, and Travel. Under the Month header, you have the numbers 1-12 running down the column. You want, say Fuel, all the payments for fuel in a given month to be summed and placed in the Fuel column in the corresponding row month. The same for all other purchase types and months. Is this correct? I'm assuming that you are not asking for help with the Data Validation (drop-down) setup. Is that right? HTH Otto "John cheshire lad" wrote in message ... Otto Thanks for that, sorry if it was not clear. Data is entered in this section I would like to choose the selection for pay method by drop down list. Pay Bus Date Method Fuel Stock Phone Stationary Travel 4/04/2008 Card £79.99 04/04/2008 Bank £11.55 07/04/2008 Cash £2.94 08/04/2008 Chq £6.07 02/04/2008 Card £15.78 02/05/2008 Bank £10.99 03/05/2008 Cash £3.88 ================================================== === Totals in the month are listed in another part on the sheet to show monthly totals by type of payment.If it makes it easier I could use one sheet for each month thus simplifying the date sorted. Any help would be greatly appreciated and thanks for guidance so far. John Card Cash Chq Bank Fuel £95.77 Bus Stock Phone £22.54 Stationary £2.94 Travel £6.07 ================================================== = "Otto Moehrbach" wrote: John You have to provide the logic behind what you want to happen for anyone to help you. You say "fuel that falls in the month". What month? Provide some logic of what you want by month. You said that you want to copy something to another sheet. What do you want to copy? Where do you want to place it? It would help if you provided a step-by-step procedure of what you would do if you were doing it all manually. HTH Otto "John cheshire lad" wrote in message ... I Have rows of input data as follows under Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
John
I think I have a handle on this now but you didn't mention anything about the months. I understood from what you said before that you wanted this information by month. Is that still a requirement? One more thing though. Give me a complete list of ALL the payment types and another list of ALL the types of expenses. Like this: Bank Card, Cheque Cash Fuel Phone Etc. "John cheshire lad" wrote in message ... Otto Its great to hear from you. I am a little confused with this discussion board.I tried to give a pictorial view of spreadsheet, but when the post is opened up it runs all the data together and loses positioning. If I just view John I can see the layout, but I do not know which view you have. Moving on. Data is entered in colums A ( Date) to G, with headers in row 1 with data being added in rows below, 1-40. Column B is for payment Type by drop down list. ( I am ok with validation) Somewhere on the sheet I need to record the amounts spent on each type of expense and by each payment method. i.e. I need to know for instance how much was spent on fuel by cash, card cheque or bank (bacs) BANK..............CARD.......CHEQUE,,,,,,CASH FUEL 12.5................23.44 PHONE i HOPE THIS HELPS AND THANKS FOR YOUR PATIENCE. jOHN "Otto Moehrbach" wrote: John That's good. You have a bunch of rows of data with the date in, I'll assume, Column A and the method of payment in, I'll assume, Column B. I'll assume this data occupies 7 columns. I'll assume you have headers in row 1 and your data starts in row 2. Starting in Column J, I'll assume you have headers of Month, Fuel, Stock, Phone, Stationary, and Travel. Under the Month header, you have the numbers 1-12 running down the column. You want, say Fuel, all the payments for fuel in a given month to be summed and placed in the Fuel column in the corresponding row month. The same for all other purchase types and months. Is this correct? I'm assuming that you are not asking for help with the Data Validation (drop-down) setup. Is that right? HTH Otto "John cheshire lad" wrote in message ... Otto Thanks for that, sorry if it was not clear. Data is entered in this section I would like to choose the selection for pay method by drop down list. Pay Bus Date Method Fuel Stock Phone Stationary Travel 4/04/2008 Card £79.99 04/04/2008 Bank £11.55 07/04/2008 Cash £2.94 08/04/2008 Chq £6.07 02/04/2008 Card £15.78 02/05/2008 Bank £10.99 03/05/2008 Cash £3.88 ================================================== === Totals in the month are listed in another part on the sheet to show monthly totals by type of payment.If it makes it easier I could use one sheet for each month thus simplifying the date sorted. Any help would be greatly appreciated and thanks for guidance so far. John Card Cash Chq Bank Fuel £95.77 Bus Stock Phone £22.54 Stationary £2.94 Travel £6.07 ================================================== = "Otto Moehrbach" wrote: John You have to provide the logic behind what you want to happen for anyone to help you. You say "fuel that falls in the month". What month? Provide some logic of what you want by month. You said that you want to copy something to another sheet. What do you want to copy? Where do you want to place it? It would help if you provided a step-by-step procedure of what you would do if you were doing it all manually. HTH Otto "John cheshire lad" wrote in message ... I Have rows of input data as follows under Date Pay type, Tools, fuel, Hotel etc 2/5/08 card ( from Drop down) 10.52 6/5/08 cash 12.55 I need to extract data that meets the criteria of where the monet was spent for , i.e. fuel that falls in the month. I have created a drop down list to select type of payment and then I would enter the amount under the particular heading. Can I use sumproduct for the date but can it be used also with an array as in the drop down list. Any help would be much appreciated. "Otto Moehrbach" wrote: What constitutes a match? Otto "John cheshire lad" wrote in message ... I am trying to do a spreadsheetfor my accounts.I need to create a drop down list of payment type i.e card, cash, cheque. I need to enter a row of data as eg Date, supplier, drop down List,materials, legal fees, etc. if the date and drop down crieria match I want to copy the materials cell to another sheet. hope you can help |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell contents that meet date and list name criteria
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| get a list of sequence number that meet 3 criteria | Excel Worksheet Functions | |||
| get a list of sequence number that meet 3 criteria | Excel Worksheet Functions | |||
| Sumproduct - meet multiple criteria from a list | Excel Worksheet Functions | |||
| Count the number of values in a list that meet certain criteria | Excel Worksheet Functions | |||
| Copy rows that meet a certain criteria | Excel Programming | |||
![John cheshire lad[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
