Solved

breaking down worksheet1 into following tabs based off of information

Posted on 2012-03-24
5
239 Views
Last Modified: 2012-03-24
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
Comment
Question by:mrospe0
  • 3
  • 2
5 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
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
 

Author Comment

by:mrospe0
Comment Utility
You said you have setup the credit sheet but I dont see it?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Oops...here it is
Copy-of-Credit-worksheet.xls
0
 

Author Comment

by:mrospe0
Comment Utility
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
 

Author Comment

by:mrospe0
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now