Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting data within the same row.
I have devised a Cash flow template which I now need to be able to sort date
in the same row for so it can automatically calc brough t fwd invoices paid and invoices raised in the month which are paid. Eg. In Row 1: Cell A1 - Formattedd as number: #,##0.00;[Black](#,##0.00), and Cell has colour fill - Light Tursoise; Cell B1, same number format, colour fill - Yellow; The above cells are for format reference only. All in Row 3, Column A, Header - 'Brought fwd total' Column B, Header - 'Invoices raised in the month' Column C, Header - 'Paid: Yes/No' (Check Ref Column): 1, 2, 3, 4 1 - B/Fwd invoices paid 2 - Invoices raised in the month paid 3 - Invoices B/fwd and rasied in the month paid 4 - B/fwd and raised in month invoices unpaid Columns D - AH, Header - 'Days of the month' Column AI, Header 'Invoices Brought Fwd Paid' Column AJ, Header 'Invoices raised in the month paid' Column AK, Header ' Invoice Totals Carried Forward' Row 4 In this row between columns D - AH will go amounts of monies recieved for a particular Debtor. For reciepts which are for Brought forward invoices I will colour fill the cell Light Turqouise, for reciepts which are for invoices rasied in the month, I will colour fill Yellow, the numer formats will all be the same. This next part is what I need the help on... I would like the cells in columns AI and AJ to do the following: Referencing Cell C3: if there is a '1' or '3' then cell AI will sum all cells with the colour format Light Turqouise and place the total in the cell (0, 2, or 4 will return a zero value) if there is a '2' or '4' then cell AJ will sum all the cells with the colour format Yellow and place the total in the cell (0, 1 or 3 will return zero value) I know how tho use the IF function to place a sum of the row in the cells AI and AJ by referncing to cell C3, but I don't know how to also include the choosing of which cells to sum in the row when referencing to cell A1 and B1 (format cells): In principle if a cell has the same format as the reference cell then it should sum all cells which meet this condition and return the value. Please, please, please could someone help on this: it would help a great deal. It may require a macro program but, as I do not know how to do this... I look forward to a resolution. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting data within the same row.
aequanimitas wrote:
I have devised a Cash flow template which I now need to be able to sort date in the same row for so it can automatically calc brough t fwd invoices paid and invoices raised in the month which are paid. Eg. In Row 1: Cell A1 - Formattedd as number: #,##0.00;[Black](#,##0.00), and Cell has colour fill - Light Tursoise; Cell B1, same number format, colour fill - Yellow; The above cells are for format reference only. All in Row 3, Column A, Header - 'Brought fwd total' Column B, Header - 'Invoices raised in the month' Column C, Header - 'Paid: Yes/No' (Check Ref Column): 1, 2, 3, 4 1 - B/Fwd invoices paid 2 - Invoices raised in the month paid 3 - Invoices B/fwd and rasied in the month paid 4 - B/fwd and raised in month invoices unpaid Columns D - AH, Header - 'Days of the month' Column AI, Header 'Invoices Brought Fwd Paid' Column AJ, Header 'Invoices raised in the month paid' Column AK, Header ' Invoice Totals Carried Forward' Row 4 In this row between columns D - AH will go amounts of monies recieved for a particular Debtor. For reciepts which are for Brought forward invoices I will colour fill the cell Light Turqouise, for reciepts which are for invoices rasied in the month, I will colour fill Yellow, the numer formats will all be the same. This next part is what I need the help on... I would like the cells in columns AI and AJ to do the following: Referencing Cell C3: if there is a '1' or '3' then cell AI will sum all cells with the colour format Light Turqouise and place the total in the cell (0, 2, or 4 will return a zero value) if there is a '2' or '4' then cell AJ will sum all the cells with the colour format Yellow and place the total in the cell (0, 1 or 3 will return zero value) I know how tho use the IF function to place a sum of the row in the cells AI and AJ by referncing to cell C3, but I don't know how to also include the choosing of which cells to sum in the row when referencing to cell A1 and B1 (format cells): In principle if a cell has the same format as the reference cell then it should sum all cells which meet this condition and return the value. Please, please, please could someone help on this: it would help a great deal. It may require a macro program but, as I do not know how to do this... I look forward to a resolution. I think it would be better if you could upload an example file to www.rapidshare.de... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
sorting data and automatic graphs | Charts and Charting in Excel | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Automatically sorting data | Excel Discussion (Misc queries) | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) |