a few excel questions... 1

[smiley0q0]

i know i read in a previous thread on how to make sure your sheet isn't think it's bigger than it really is, like when you used to have 500 rows but you deleted all but 5 of them, the vertical bar still thinks there is data down there. i can't remember how to make it realize that nothing is down there....

another question is can i have 2 vlookups in one formula? if so how? here is what i am trying to do... i have 4 columns of info the first one is my date column, second is my client column, 3rd is curramt, and 4th is prevamt.
there are 3 or 4 clients to each date. so it looks like this...

Date Client CurrAmt PrevAmt
4/19/01 Clnt1 2,000 1,500
4/19/01 Clnt2 1,000 5,500
4/19/01 Clnt3 3,000 1,000
3/17/01 Clnt1 5,000 6,000
3/17/01 Clnt2 4,000 3,500
3/17/01 Clnt3 1,500 2,500
3/17/01 Clnt4 2,000 1,500

i would like to be able to say that i want clnt1's row where the date is equal to a certain cell. basically i need it to look like this...

4/19/01 3/17/01
clnt1 2,000 5,000
clnt2 1,000 3,500
clnt3 3,000 1,500
clnt4 0 2,000

kind of like a transpose i guess...
i am kind of lost here... any help would be appreciated.

Thanks,
Smiley :-9

 

[JVFriederick]

If you can hang on for about 10 minutes, I can write up a nice solution.......

 

[JVFriederick]

To answer your first question, delete unneeded rows and columns, save and close the file, then open the file. Pressing End+Home will jump to the lower right cell.


Now for the second question. What I would like to try and explain is a not the easiest way, but the analysis technique can be used in so many ways it is WELL WORTH the trouble in my opinion.

Assume the table of date you described starts in cell A1 as follows :

A B C D
1 Date Client CurrAmt PrevAmt
2 4/19/01 Clnt1 2,000 1,500
3 4/19/01 Clnt2 1,000 5,500
4 4/19/01 Clnt3 3,000 1,000
5 3/17/01 Clnt1 5,000 6,000
6 3/17/01 Clnt2 4,000 3,500
7 3/17/01 Clnt3 1,500 2,500
8 3/17/01 Clnt4 2,000 1,500


Now construct your analysis table starting in cell B10 as follows :

B C D
10 4/19/01 3/17/01
11 clnt1
12 clnt2
13 clnt3
14 clnt4

Now in cell C11, type the following equation (you can paste the formula from this reply). This formula assumes you want to Sum the CurrAmt column. I'll explain the formula a little later.

=SUM(($A$2:$A$8=C$10)*($B$2:$B$8=$B11)*$C$2:$C$8)

Go ahead and press Enter - and you will get a #Value! - but don't worry. Now press F2 key (edit) and then press Ctrl+Shift+Enter at the same time. Now the formula should look like this :

{=SUM(($A$2:$A$8=C$10)*($B$2:$B$8=$B11)*$C$2:$C$8)}

Now you should have those little {} on each end of the formula, and the result shows 2000 in cell C11. Highlight C1114 and then press Ctrl+D then Ctrl+R, which copies down and then copies to the right. Now your table is complete.

OK, so this seems pretty weird ?

There are 3 parts to the equation.
The first part looks in column A2:A8, which has Dates. Compare that to the date you entered in C$10. Note that $10 is used to freeze the row reference.
The second part looks in column B2:B8, which has Clients. Compare that to the client you entered in $B11. Note that $B is used to freeze the column reference.
The third part looks in column C2:C8, which has CurrAmt, which is what we wish to Sum. If a match is found in both the first and second parts of the equation, then the value in column C is added.

This analysis technique is maybe a bit much for this task, but can easily be expanded to handle 3, 4, or many more summation levels. Example : $4000 for Clnt1 on 4/19/01 that falls in accounting period 3 and is being managed by Tom Jones who reports to Henry. To summarize such a combination, just add Accounting Period and Salesman and Manager to the table, and then add the new column references to the formula. You can easily summarize all sorts of combinations.

Likewise, by dropping the second part of the equation, which is Clients, will give a subtotal by Date

=SUM(($A$2:$A$8=C$10)*$C$2:$C$8)

Give it a try. If you get the hang of it, you'll be able to summarize almost anything in any way you need to by writing a single equation.

 

[Dreamboat]

JV: You're gonna post this as a FAQ, right?

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[JVFriederick]

Ummmm. Sure, I guess. I don't know how to do that! As long as it isn't too painful!

 

[Dreamboat]

No brainer, JV. Just get out of this Q and go to the list of Qs. At the top, you'll see Forum, Keyword Search, FAQs, Links. Click on FAQs. There's a dialog just like the reply dialog at the bottom of the list. After they've posted it, you can edit it. Didn't you ever take a look at the FAQs there? Not a lot of them, but I have sooooo many more. I guess I just have other priorities, like eating, for instance. Sleeping--you know. The usual. LOL
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[JVFriederick]

Have I ever looked at the FAQs ? Well, no. I can only handle one thing at a time!!!

But I will look into it after some gardening !

 

[JVFriederick]

OK, it's done. Had to use the dictionary a few times . . .

Sleep awaits.

 

[smiley0q0]

WOW this is GREAT!!!! it took me a little while to get the hang of it, but i think i have it now, and it works very efficiently. it even gave me the correct number without the litte {} thingy's... don't know why, but i put them in anyway, and also thank you for showing me another copy/paste shortcut. i like that one too. :-D :-V

 

[JVFriederick]

Dreamboat even twisted my arm to put your question into a FAQ, so maybe others will benefit from it as well.

 

[Dreamboat]

smiley: Please don't spell thingy's wrong. It's plural, so no apostrophe, okay? Also, it ends in Y. When a plural ends in Y, you change the Y to an IE. Got it?

thingies​

LOL​

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[JVFriederick]

The things one can learn around here !

 

[smiley0q0]

i thawght i wood make yor life moor enter-esting.

it looked better to me spelled

thingy's,

than

thingies

: ​

 

[Dreamboat]

Whell! Ydintcha sigh sew!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[smiley0q0]

now that is how to say things. ;-) it's kind of fun spelling things wrong. ain't it. %-) *:->*

 

[JrClown]

U guys are kraissy QUOTE OF THE DAY
"The most important ingredient in the formula of success is knowing how to get along with people"
<%
Jr Clown
%>

 

[Dreamboat]

A better quote might be

&quot;The most important ingredient in the quote of the day is knowing which font color to use!!&quot;



techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[JrClown]

My apologies, you're not kraissy. You are Funiee

Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
QUOTE OF THE DAY
&quot;The most important ingredient in the formula of success is knowing how to get along with people&quot;
<%
Jr Clown
%>

 

[JrClown]

Here a better1
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
Dreamboat
QUOTE OF THE DAY
&quot;The most important ingredient in the formula of success is knowing how to get along with people&quot;
<%
Jr Clown
%>

 

[smiley0q0]

nope, just nuts... >:O>