to populate a template.
I am attempting to creating a spreadsheet where an assistant in my group can enter an invoice on one worksheet in excel and it populates information from that page into a different worksheet, but in an organized mannor. I need this to compare budgeted vs. actual totals for construction projects.
Quick Example (Data Entry Page):
Company Category Subcategory Date Cost
ABC Towing 1 1 1/1/2000 70.00
James Mech. 1 2 1/4/2000 100.00
Newspapers 2 1 1/7/2000 50.00
Joe's Parts 1 2 1/18/2000 200.00
On a separate worksheet within the file (Sheet 1 has entries, Sheet 2 has summaries) I would have an area labeled for Category 1, Sub Category 2 entries (There would be a summary section for each category and sub-category). The sheet would populate James Mech and Joe's Parts for Category 1-2 as follows:
I. Mecanical
2 - Parts and Accessories
Company Category Subcategory Date Cost Budget
James Mech. 1 2 1/4/2000 100.00 110.00
Joe's Parts 1 2 1/18/2000 200.00 175.00
TOTAL 300.00 285.00
I have tried to populate such a summary on the second sheet to no avail. I tired using pivot tables and it left me with limited flexibility. Tried researching lists and filters and such but nothing that could summarize items entered for a specific category on a separate page.
Is this beyond the basic scope of Excel? To do what I want it to do, would I need to build a program or use Macros some how (no experience in doing that). Can someone point me to a template that performs an action similar to one I have outlined? Do I have to write a complex "if" function? Can I scan columns for a number for catergory and sub category similar to vlookup but for multiple rows? Or is there a way to filter the information from the database page immediately in the column headings for each summary?
Hope this isnt too confusing. I will clarify if questions are asked.
Bookmarks