Excel 2007 : Need help with summing up the data in sheet

[shaheedfaiz]

I have an Excel workbook that has the following data in 30 different sheets(each day of the month). The format of the data is like below on each sheet that I receive on daily basis:

HOUSTON $1,190.00
SHOES $500.00
SHIRTS $300.00
PANTS $250.00
SOCKS $140.00
DALLAS $601.00
SHOES $300.00
SHIRTS $125.00
PANTS $141.00
SOCKS $35.00
AUSTIN $1,110.00
SHOES $650.00
SHIRTS $120.00
PANTS $285.00
SOCKS $55.00

At the end of the month, I want to have a summary sheet which shows me the Monthly Sales Report (sum of daily sales for each item, for each city) in the format below:

----------------Shoes Shirts Pants Socks
Houston:
Dallas:
Austin:

Let me know please if it can be done in Excel. Would really appreciate your help.

Regards!

Shaheed Faiz

[Paul]

Re-organize your data so that the city name is in each row. (Three columns instead of two)

City           Item                Sales
HOUSTON        SHOES               500.00
HOUSTON        SHIRTS              300.00
HOUSTON        PANTS               250.00
HOUSTON        SOCKS               140.00
DALLAS         SHOES               300.00
DALLAS         SHIRTS              125.00

Summing all of your data that way will be much easier.

[Chainsaw_019]

Pls. check the attachment..Hope it will help..

[shaheedfaiz]

Thanks for the suggestions but I am trying to find an automated way to calculate the monthly sales. This way, if I understood the solution right, I will have to manually change the format and then transfer it to the Summary Sheet.

The sales system pulls the report in the format above in different tabs for each day (I have attached the sheet for your review) therefore for me to change the format on each sheet will require lot of manual work and that’s what I am trying to avoid.

Is there any way, formula or macro to look for a value, say 'Houston' and then pick the value right below that cell. It may help obtain the value(s) I am looking for. Or any other possible way to achieve what I am looking for.

Please also let me know if ya'll think this kind of solution doesnt exist in Excel so I may try to use other applications for the desired result.

Thanks!

[ansardoha]

Check the attachment you can have what you want

reply me if u need any correction

Ansar.

[shaheedfaiz]

Thank you very much. It helps.