| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news  C667563-2C29-4793-979A-(E-Mail Removed)...> 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" <(E-Mail Removed)> wrote in >> message news:92CA6FEE-5214-483A-91F0-(E-Mail Removed)... >> >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" <(E-Mail Removed)> wrote >> >> in >> >> message news:7EF03688-9C7A-4BC5-94C3-(E-Mail Removed)... >> >> >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 >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in > message news C667563-2C29-4793-979A-(E-Mail Removed)...> > 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" <(E-Mail Removed)> wrote in > >> message news:92CA6FEE-5214-483A-91F0-(E-Mail Removed)... > >> >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" <(E-Mail Removed)> wrote > >> >> in > >> >> message news:7EF03688-9C7A-4BC5-94C3-(E-Mail Removed)... > >> >> >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 > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:0241E8BE-3CAC-4738-B113-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in >> message news C667563-2C29-4793-979A-(E-Mail Removed)...>> > 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" <(E-Mail Removed)> wrote >> >> in >> >> message news:92CA6FEE-5214-483A-91F0-(E-Mail Removed)... >> >> >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" <(E-Mail Removed)> >> >> >> wrote >> >> >> in >> >> >> message news:7EF03688-9C7A-4BC5-94C3-(E-Mail Removed)... >> >> >> >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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
John
This is getting too involved to keep this in the newsgroup. Send me an email and we'll do this by email. I want to send you a small file I just put together for you to look at the layout and we can go from there. My email is (E-Mail Removed). Remove the "extra" from this email address. Otto "John cheshire lad" <(E-Mail Removed)> wrote in message news:0241E8BE-3CAC-4738-B113-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in >> message news C667563-2C29-4793-979A-(E-Mail Removed)...>> > 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" <(E-Mail Removed)> wrote >> >> in >> >> message news:92CA6FEE-5214-483A-91F0-(E-Mail Removed)... >> >> >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" <(E-Mail Removed)> >> >> >> wrote >> >> >> in >> >> >> message news:7EF03688-9C7A-4BC5-94C3-(E-Mail Removed)... >> >> >> >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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Removal if cell contents does not meet vriteria | Jen_T | Microsoft Excel Programming | 0 | 9th Feb 2009 01:25 PM |
| How to copy rows that meet criteria to another sheet in Excel | =?Utf-8?B?QnJ1Y2U=?= | Microsoft Excel Worksheet Functions | 9 | 2nd Nov 2006 11:12 PM |
| Copy rows that meet a certain criteria | James | Microsoft Excel Programming | 1 | 5th May 2006 11:31 PM |
| macro to copy cells that meet criteria (mattour) | matthias | Microsoft Excel Programming | 1 | 4th Apr 2006 06:24 PM |
| macro to copy cells that meet criteria (mattour) | matthias | Microsoft Excel Programming | 0 | 4th Apr 2006 04:43 PM |
