Lookup then consolidate data from an Excel database

[Tony Ucelli]

I am struggling to find formulae for a worksheet.

I have many line items from a Chart of Accounts on which I want to be
able to see what costs are incurred on a daily basis. (For example,
glassware, subscriptions, gas/electricity.) Some items we buy from a
single vendor (i.e., gas/electricity), other items we buy from
multiple vendors (i.e., subscriptions) which need to be consolidated
into a single line item. Other items would have different actual items
from different vendors and need to be consolidated into a single line
item (i.e., cups, glasses and plates would be consolidated into
glassware). We buy some items once/month, others perhaps 4-6
times/month. I want to have a simple data input worksheet (vendor
name, date, Chart of Account Line Item number, amount, comment).

The Report Sheet will have the days in the month on one axis and Chart
of Accounts line items on the other. I want the data from the input
sheet consolidated so each cell in the Report Sheet that covers the
cross point between date and line item shows the total costs for that
day for that line item. I.e., it will look up for that date all costs
for that line item, add them together and place that total in the
relevant cell.

I assume Excel can do this and I guess there may be a combination of
formulae to do it but I cannot work it out. Any help very much
appreciated.

 

[JE McGimpsey]

I am struggling to find formulae for a worksheet.

I have many line items from a Chart of Accounts on which I want to be
able to see what costs are incurred on a daily basis. (For example,
glassware, subscriptions, gas/electricity.) Some items we buy from a
single vendor (i.e., gas/electricity), other items we buy from
multiple vendors (i.e., subscriptions) which need to be consolidated
into a single line item. Other items would have different actual items
from different vendors and need to be consolidated into a single line
item (i.e., cups, glasses and plates would be consolidated into
glassware). We buy some items once/month, others perhaps 4-6
times/month. I want to have a simple data input worksheet (vendor
name, date, Chart of Account Line Item number, amount, comment).

The Report Sheet will have the days in the month on one axis and Chart
of Accounts line items on the other. I want the data from the input
sheet consolidated so each cell in the Report Sheet that covers the
cross point between date and line item shows the total costs for that
day for that line item. I.e., it will look up for that date all costs
for that line item, add them together and place that total in the
relevant cell.

I assume Excel can do this and I guess there may be a combination of
formulae to do it but I cannot work it out. Any help very much
appreciated.

Your input worksheet sounds like it would be taken care of with List
Manager or the Data/Form command. See those topics in Help. You could
also build a UserForm that provides a custom interface.

I'll also assume that the report can be done, but I'm afraid that I
can't picture what you're asking for. A simple Pivot Table would allow
you to consolidate items.