need help with sorting

[chris hanners]

I have made an expense report template which I use, but the information
in this report varies week from week. What I want to do is create a
formula that will automatically search the data entered and skip over
areas that may not have data in them and enter that information in
another area without skipping spaces. I guess I need a formula that
will automatically retrieve data and sort it and put it in another area
of the report.

example:
A B C D
1 DAY Breakfast Lunch Dinner
2 MON 12.00 15.00 45.00
3 TUE 17.50
4 WED 18.65
5 THU 10.00
6 FRI 56.00
7 SAT 12.00

I want to create a formula that will look at the information as
follows:
box A15 = if B2>0,B2, else go to C2, if C2>0, C2, else go to D2, etc

This formula works for a line or 2, but I am unable to take in account
once a valid formula is reached, to keep it from duplication later on -
There must be a solution to this problem - If you can help - please
contact me - Thanks in advance - Chris

 

[Debra Dalgleish]

I can't follow what you're trying to do, but if you want to record meal
expenses, and display them in a table, you could use a list and a
PivotTable.

Enter the expenses in a list, e.g.:

Date Expense Amt
1/1/04 Breakfast 3
1/1/04 Lunch 5
1/2/04 Breakfast 2
1/2/04 Dinner 6

To summarize the expenses, create a PivotTable:

Select a cell in the list
Choose Data>PivotTable and PivotChart report
Click Next, select the cells with data, click Next
Click Layout
Drag the Date button to the Row area
Drag the Expense button to the Column area
Drag the Amt button to the Data area
Click OK, choose a location for the Pivot Table, click Finish

 

[chris hanners]

The post I made originally did not appear as I posted it - I hav
attached the expense report to hopefully spell out what I need hel
on.

the cells from M9 to T9, to M22 to T22 are all cells that might contai
an amount that must be itemized in the itemized meal section below.
What I want is the excel template to search through these cells, and i
any are >0, then put them in order(while skipping blank cells) in cell
O29 through O38.

right now, I can not find a way that works (without duplicatio
occuring) that will allow me to skip over blank cells and place them i
the itemized section. Hope you can help -

I will confess that I do not fully understand pivot tables - but do yo
think that is what will fix my problem? Thanks, Chri

Attachment filename: hanners expense report template rev 12-13-03.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=39608