EXCEL question
 

I have a spreadsheet project I'm working on. lemme splain it to you

ok, so the main sheet, which I want the sales manglers to enter data into has a row of fields for them to fill in details about each deal: salesman, team, new/used, etc...

Easy enough to build an if/then formula and separate stats based on the answer in a specific field. Like, if I want new car gross only, or if I want all of team 1's deals on this sheet... I can do that. maybe it's not the most efficient way though... here's what I do:

I made the 'master sheet' where all deals are logged. this has a column for team #
then I made 4 identical copies of that sheet
I named those copy team 1-4
say the team gets logged in column B of the master sheet

so on the team1 sheet I set a formula that goes like this:
(in cell A4)

=IF(Master!B4=1,Master!a4,"")

and stretched that over the whole sheet.

that gives me rows copied from the master sheet ONLY if the answer to TEAM = 1.

the problem is that there are lots of empty lines. I'd like for all of the team one deals to stack up at the top.

anyone know how to do that?

OK, the filter function does it, but it has to be reapplied each time an entry is made. I want it to do it as info is entered.

is that possible?

You probably have to use a Visual Basic script. I'm not sure how, though.

Doesn't have to be V B. I think you can record a macro to do it.

Sent by thought transference.

I thought about a macro.... But then the mooks would have to hit a button.....

I know diddly about VB, though, so..... Maybe I'll get some bananas and a fire hose and spend some time training them

I think this is the site I went to once when I had a question. I was very specific about my needs and some dude answered right away with the way to do it.

http://www.excelforum.com/

oh, cool! thanks shaw!

second thanks , shaw. not only did my question get answered, but Pete UK actually took the time to DO what I was describing. Now I just have to decipher it, and employ the same steps on the subsequent sheets I need.

awesome program, excel.

That's great! :)

That's a nifty solution. I've wanted to do similar things before, and never found an elegant way.

did you go there and find the thread?

I need to get 20 minutes in a row with nothing interrupting me to read that post and try to understand the formulas component by component... which has been impossible so far today. working solo as my counterpart is on vacation this week.

I did. I even registered so I could see his solution.

I'd have to spend some time to understand all of the nuances, but I get the gist, I think.

First, he makes a column on the main page that says which page and row each entry on the main page should go to, then on each other page he has each row looking for an entry on the main page that wants to go there.

it helps to think in 3D, like a person in flatland being introduced to the concepts of depth and height.

think in 3D? I don't know ..... This shit is SO linear. incremental.

this is building a birdhouse when you don't know what a hammer is. or nails. I'm learning what the functions do. that's one small part of it. the art part... or elegance, as HM says, is the ways you apply these tools. maybe that part is a little 3D...if by that you mean BIG PICTURE.... but the formulas... not so much.

his solution, as I understand it presently does 3 things. first, he counts occurances of unique team numbers. so this formula:

located away from where data is entered, looks at cells in the C column, where I have the team number being entered. if the cell is blank, it returns a [ - ], if it has an entry, it gives me that entry and _the count of those same entries in column C that fall between C8 and C8.... if C8 is filled with a '1', this cell will look like this: [1_1] where the first 1 is the entry in C8, and the 2nd is how many are in that column so far. the $ before the 8 is to lock that 8 when the formula is replicated into cells below. the cell beneath this one will say C$8:C9.... then next C$8:C10, etc... and if there are two 1's in the range the next 1_* will count 2 and return 1_2 .

this is done in order to give each row a unique, index-able identifier. the deals can then be sorted by team, using the first part of the 1_2 to get them onto the right sheet...and then within each sheet, sorted by the 2 part of it, in order, with no gaps. slick. that accomplishes what I'm after. Now I'm just trying to understand the WAY he went about it, and what all the little parts of the other 2 formulas do. I've got a nerd-on.