EXCEL question

jimhelm · 11-06-2012, 05:09 PM

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?

jimhelm · 11-06-2012, 05:33 PM

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?

Happy Monkey · 11-06-2012, 10:30 PM

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

limey · 11-07-2012, 03:34 AM

Quote:

Originally Posted by Happy Monkey

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.

jimhelm · 11-07-2012, 08:47 AM

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

infinite monkey · 11-07-2012, 08:53 AM

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/

jimhelm · 11-07-2012, 09:10 AM

oh, cool! thanks shaw!

jimhelm · 11-07-2012, 03:37 PM

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.

infinite monkey · 11-07-2012, 03:39 PM

That's great!

Happy Monkey · 11-07-2012, 04:06 PM

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

jimhelm · 11-07-2012, 04:36 PM

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.

Happy Monkey · 11-07-2012, 04:46 PM

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.

BigV · 11-07-2012, 06:15 PM

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

lumberjim · 11-07-2012, 10:13 PM

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:

Code:

=IF(C8="","-",C8&"_"&COUNTIF(C$8:C8,C8))

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.

11-06-2012, 05:09 PM	#1
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	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? __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-06-2012, 05:33 PM	#2
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	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? __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-06-2012, 10:30 PM	#3
Happy Monkey I think this line's mostly filler. Join Date: Jan 2003 Location: DC Posts: 13,575	You probably have to use a Visual Basic script. I'm not sure how, though. __________________ _________________ \|...............\| We live in the nick of times. \| Len 17, Wid 3 \| \|_______________\| [pics]

11-07-2012, 08:47 AM	#5
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	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 __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-07-2012, 09:10 AM	#7
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	oh, cool! thanks shaw! __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-07-2012, 08:53 AM	#6
infinite monkey Person who doesn't update the user title Join Date: Mar 2011 Posts: 13,002	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/

11-07-2012, 03:37 PM	#8
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	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. __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-07-2012, 03:39 PM	#9
infinite monkey Person who doesn't update the user title Join Date: Mar 2011 Posts: 13,002	That's great!

11-07-2012, 04:06 PM	#10
Happy Monkey I think this line's mostly filler. Join Date: Jan 2003 Location: DC Posts: 13,575	That's a nifty solution. I've wanted to do similar things before, and never found an elegant way. __________________ _________________ \|...............\| We live in the nick of times. \| Len 17, Wid 3 \| \|_______________\| [pics]

11-07-2012, 04:36 PM	#11
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	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. __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

11-07-2012, 04:46 PM	#12
Happy Monkey I think this line's mostly filler. Join Date: Jan 2003 Location: DC Posts: 13,575	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. __________________ _________________ \|...............\| We live in the nick of times. \| Len 17, Wid 3 \| \|_______________\| [pics]

11-07-2012, 06:15 PM	#13
BigV Goon Squad Leader Join Date: Nov 2004 Location: Seattle Posts: 27,063	it helps to think in 3D, like a person in flatland being introduced to the concepts of depth and height. __________________ Be Just and Fear Not.

11-07-2012, 10:13 PM	#14
lumberjim I can hear my ears Join Date: Oct 2003 Posts: 25,571	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: Code: =IF(C8="","-",C8&"_"&COUNTIF(C$8:C8,C8)) 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. __________________ This body holding me reminds me of my own mortality Embrace this moment, remember We are eternal, all this pain is an illusion ~MJKeenan

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)