Sorting data within the same row.

[aequanimitas]

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.

 

[Franz Verga]

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