Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

breaking down worksheet1 into following tabs based off of information

Hello,

I am fairly new to Excel and have only basic, limited knowledge on how to set up worksheets. I need some more help.

I have set up a workbook designed for tracking debt and finances. I use it monthly but some of my friends and family want to start using it as well. The way I have it organized is by lowest debt to highest. I then have the following pages to have the corresponding cells copy (or =) the box from the first tab. However, the categories or order of mine is not the same as theirs.

What I need to do is set up a way for the following tabs to be catagories. The information on these tabs will be predetermined from the first worksheet regardless of what row it becomes. I want to be able to resort the information on the first worksheet based in numerical order from lowest to highest once all information is entered. I then want the following tabs to also be completed with no more information needed.

Attached is a sample version of what I currently use. I entered basic information to show how it is copied throughout with only entering the information on the fist tab.

My hypothical is this: from the way I entered the information on tab one "Debt Overview" my smallest debts are my "School Loan 1" with a balance of $0 followed by my "Other" with a balance of $65.      Now to arrange the list of debts lowest to highest I want to highlight the data in column D which is titled "Current Balance" ( would be cells D6 to D13) then hit sort 'smallest to largest' (a to z).

When I do the above it now changes what following tabs and mixes everything up so it is no longer in the right categories. For example, doing the above steps would put my "School loan 1" and "other" as debt on my "credit" tab and my "home loan" on my "misc." tab.

How can I set this up so that way no matter what order the information on Tab1 is entered my following category tabs are still correct?

Attached is the sample of the original workbook.
Credit-worksheet.xls
0
mrospe0
Asked:
mrospe0
  • 3
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
I have setup the Credit sheet for you. You can try applying it to the other sheets.

You have to fix the creditor name - No formula.
Then there is one formula which picks values from column B of Overview sheet. The other picks values from column D. Both search for the creditor name in overview sheet and return values from corresponding column 2 and 4.
0
 
mrospe0Author Commented:
You said you have setup the credit sheet but I dont see it?
0
 
Saqib Husain, SyedEngineerCommented:
Oops...here it is
Copy-of-Credit-worksheet.xls
0
 
mrospe0Author Commented:
Thank you, that definitely seems to do what I am looking for.

I checked the formulas that you set up. They do exactly as I need. Took me a minute to figure out the breakdown to recompute them do duplicate to work on the other tabs.

If you dont mind I have a follow up hypothetical. What if I was to change the "creditor" column to just a generic and insert into each row a unique identifier, such as a different number or letter. Then insert a new "creditor" column where I could place the company names, or in the sample, what the account is. Do you think then I could duplicate this formula yet again, or even set up a different type of formula, to copy the names over into the supplemental tabs, too?
0
 
mrospe0Author Commented:
It would work using the same equation obviously (I wasnt thinking lol). That route wouldnt really help me with the name though but I have figured out the last part. Thanks for the help; saved me a huge headache.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now