sum values from range of cells if cells correcponding have the sam

hiya, i have a small project and i've spent too much time on this already - 
can't figure it out myself. What i need to do is to create a formula which 
will find the same values in column A and will sum the corresponding to them 
values from columns B:E. 
this is a sort of result i want to get: 
	A	B	C	D	E	F	RESULT:	
1	A1	1	5	2	3		A1	31
2	B5	2	6	3	3		A2	19
3	C1	3	7	4	3		B1	22
4	A1	4	8	5	3		B5	37
5	B5	5	9	6	3		C1	33
6	C2	6	1	6	3		C2	16
7	A2	7	2	7	3			
8	B1	8	3	8	3			
9	C1	9	4	0	3			

I would appreciate any ideas how to do this. Thanks and regards,
0
Eisaz (1)
11/17/2008 9:41:01 PM
excel.newusers 15348 articles. 2 followers. Follow

15 Replies
685 Views

Similar Articles

[PageSpeed] 32

Assume in G1 down are the unique items: A1, A2, etc
Then in H1: =SUMPRODUCT(($A$1:$A$10=G1)*OFFSET($B$1:$B$10,,,,4))
Copy H1 down to return required results. Adapt the ranges to suit.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
--- 
"Eisaz" wrote:
> hiya, i have a small project and i've spent too much time on this already - 
> can't figure it out myself. What i need to do is to create a formula which 
> will find the same values in column A and will sum the corresponding to them 
> values from columns B:E. 
> this is a sort of result i want to get: 
> 	A	B	C	D	E	F	RESULT:	
> 1	A1	1	5	2	3		A1	31
> 2	B5	2	6	3	3		A2	19
> 3	C1	3	7	4	3		B1	22
> 4	A1	4	8	5	3		B5	37
> 5	B5	5	9	6	3		C1	33
> 6	C2	6	1	6	3		C2	16
> 7	A2	7	2	7	3			
> 8	B1	8	3	8	3			
> 9	C1	9	4	0	3			
> 
> I would appreciate any ideas how to do this. Thanks and regards,
0
demechanik (4694)
11/17/2008 10:02:01 PM
Couldn't quite follow where some of your data was, so, assuming your labels 
are column A, rows 1-9, and any data relative to those labels are in B1:F9 (I 
have all of column F empty).
In cells H2:H7, I typed each individual label: A1 | A2 | B1 | B5 | C1 | C2
In cell I2, I typed the following formula:
=SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))
and then copied this formula down to I7.
Obviously, lengthen the range beyond 9 as needed :)
-- 
** John C **
Please remember if your question is answered, to mark it answered :). It 
helps everyone.


"Eisaz" wrote:

> hiya, i have a small project and i've spent too much time on this already - 
> can't figure it out myself. What i need to do is to create a formula which 
> will find the same values in column A and will sum the corresponding to them 
> values from columns B:E. 
> this is a sort of result i want to get: 
> 	A	B	C	D	E	F	RESULT:	
> 1	A1	1	5	2	3		A1	31
> 2	B5	2	6	3	3		A2	19
> 3	C1	3	7	4	3		B1	22
> 4	A1	4	8	5	3		B5	37
> 5	B5	5	9	6	3		C1	33
> 6	C2	6	1	6	3		C2	16
> 7	A2	7	2	7	3			
> 8	B1	8	3	8	3			
> 9	C1	9	4	0	3			
> 
> I would appreciate any ideas how to do this. Thanks and regards,
0
Utf
11/17/2008 10:02:15 PM
To the OP, this response, while it does work, uses a volatile function. See 
my formula for a non-volatile solution. The real headache from volatile 
functions occur when you open a file, then do nothing before trying to close 
the file, it will ask you if you wish to save changes, since volatile 
functions are always calculating.
-- 
** John C **

"Max" wrote:

> Assume in G1 down are the unique items: A1, A2, etc
> Then in H1: =SUMPRODUCT(($A$1:$A$10=G1)*OFFSET($B$1:$B$10,,,,4))
> Copy H1 down to return required results. Adapt the ranges to suit.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,500 Files:363 Subscribers:64
> xdemechanik
> --- 
> "Eisaz" wrote:
> > hiya, i have a small project and i've spent too much time on this already - 
> > can't figure it out myself. What i need to do is to create a formula which 
> > will find the same values in column A and will sum the corresponding to them 
> > values from columns B:E. 
> > this is a sort of result i want to get: 
> > 	A	B	C	D	E	F	RESULT:	
> > 1	A1	1	5	2	3		A1	31
> > 2	B5	2	6	3	3		A2	19
> > 3	C1	3	7	4	3		B1	22
> > 4	A1	4	8	5	3		B5	37
> > 5	B5	5	9	6	3		C1	33
> > 6	C2	6	1	6	3		C2	16
> > 7	A2	7	2	7	3			
> > 8	B1	8	3	8	3			
> > 9	C1	9	4	0	3			
> > 
> > I would appreciate any ideas how to do this. Thanks and regards,
0
Utf
11/17/2008 10:16:01 PM
see your other post

-- 
Biff
Microsoft Excel MVP


"Eisaz" <Eisaz@discussions.microsoft.com> wrote in message 
news:9C549B51-4527-4008-97FB-BE16AA2C5F9B@microsoft.com...
> hiya, i have a small project and i've spent too much time on this 
> already -
> can't figure it out myself. What i need to do is to create a formula which
> will find the same values in column A and will sum the corresponding to 
> them
> values from columns B:E.
> this is a sort of result i want to get:
> A B C D E F RESULT:
> 1 A1 1 5 2 3 A1 31
> 2 B5 2 6 3 3 A2 19
> 3 C1 3 7 4 3 B1 22
> 4 A1 4 8 5 3 B5 37
> 5 B5 5 9 6 3 C1 33
> 6 C2 6 1 6 3 C2 16
> 7 A2 7 2 7 3
> 8 B1 8 3 8 3
> 9 C1 9 4 0 3
>
> I would appreciate any ideas how to do this. Thanks and regards, 


0
biffinpitt (3172)
11/17/2008 10:41:13 PM
On 17 Nov., 22:41, Eisaz <Ei...@discussions.microsoft.com> wrote:
> hiya, i have a small project and i've spent too much time on this already=
 -
> can't figure it out myself. What i need to do is to create a formula whic=
h
> will find the same values in column A and will sum the corresponding to t=
hem
> values from columns B:E.
> this is a sort of result i want to get:
> =A0 =A0 =A0 =A0 A =A0 =A0 =A0 B =A0 =A0 =A0 C =A0 =A0 =A0 D =A0 =A0 =A0 E=
 =A0 =A0 =A0 F =A0 =A0 =A0 RESULT:
> 1 =A0 =A0 =A0 A1 =A0 =A0 =A01 =A0 =A0 =A0 5 =A0 =A0 =A0 2 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 A1 =A0 =A0 =A031
> 2 =A0 =A0 =A0 B5 =A0 =A0 =A02 =A0 =A0 =A0 6 =A0 =A0 =A0 3 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 A2 =A0 =A0 =A019
> 3 =A0 =A0 =A0 C1 =A0 =A0 =A03 =A0 =A0 =A0 7 =A0 =A0 =A0 4 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 B1 =A0 =A0 =A022
> 4 =A0 =A0 =A0 A1 =A0 =A0 =A04 =A0 =A0 =A0 8 =A0 =A0 =A0 5 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 B5 =A0 =A0 =A037
> 5 =A0 =A0 =A0 B5 =A0 =A0 =A05 =A0 =A0 =A0 9 =A0 =A0 =A0 6 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 C1 =A0 =A0 =A033
> 6 =A0 =A0 =A0 C2 =A0 =A0 =A06 =A0 =A0 =A0 1 =A0 =A0 =A0 6 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 C2 =A0 =A0 =A016
> 7 =A0 =A0 =A0 A2 =A0 =A0 =A07 =A0 =A0 =A0 2 =A0 =A0 =A0 7 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> 8 =A0 =A0 =A0 B1 =A0 =A0 =A08 =A0 =A0 =A0 3 =A0 =A0 =A0 8 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> 9 =A0 =A0 =A0 C1 =A0 =A0 =A09 =A0 =A0 =A0 4 =A0 =A0 =A0 0 =A0 =A0 =A0 3 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>
> I would appreciate any ideas how to do this. Thanks and regards,

Hi

With the list of unique values from colunmn A listed in column F, copy
this formula til G1 and copy down.

=3DSUMIF($A$1:$A$9,F1,$B$1:$B$9)+SUMIF($A$1:$A$9,F1,$C$1:$C$9)+SUMIF($A
$1:$A$9,F1,$D$1:$D$9)+SUMIF($A$1:$A$9,F1,$E$1:$E$9)

Hopes it helps

Regards,
Per
0
11/17/2008 10:49:22 PM
> To the OP, this response, while it does work, uses a volatile function.

To the OP: Of course it works. I would have tested it carefully before 
posting it. And the volatile function used earlier is OFFSET - Thought I'd 
just state it here for thread completeness since John omitted mention. It's 
a versatile function to me (volatility notwithstanding), and it deserves its 
place in the sun. By all means, do go with what you prefer, for example the 
shortest, non-volatile amongst the many suggestions which your posting is 
fortunate to receive.

To John: Should I thank you for reducing the worth of my response to zero?
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---  


0
demechanik (4694)
11/18/2008 4:24:22 AM
> =SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))

Think the parens for the 2nd term can be removed
This shorter one would work equally well: 
=SUMPRODUCT(($A$1:$A$9=H2)*$B$1:$F$9)
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
--- 
0
demechanik (4694)
11/18/2008 4:35:01 AM
I know from many of your posts that you love the volatile function. And I 
also know that almost always after you post a volatile function, someone else 
comes along and posts a non-volatile function. Your formula does work, but 
95% of the time, the user does not want a volatile function, and in fact, 
many times it generates a question "Why does excel ask me if I want to make 
changes when I haven't made any changes?" Again, your formula works, and 
trust me, I use OFFSET in many of my spreadsheets, but only when I really 
don't have a way around it.


-- 
** John C **

"Max" wrote:

> > To the OP, this response, while it does work, uses a volatile function.
> 
> To the OP: Of course it works. I would have tested it carefully before 
> posting it. And the volatile function used earlier is OFFSET - Thought I'd 
> just state it here for thread completeness since John omitted mention. It's 
> a versatile function to me (volatility notwithstanding), and it deserves its 
> place in the sun. By all means, do go with what you prefer, for example the 
> shortest, non-volatile amongst the many suggestions which your posting is 
> fortunate to receive.
> 
> To John: Should I thank you for reducing the worth of my response to zero?
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,500 Files:362 Subscribers:64
> xdemechanik
> ---  
> 
> 
> 
0
Utf
11/18/2008 1:51:01 PM
I always include the second set of parenthesis. The microscopic amount of 
memory you could save by excluding them isn't worth it to make the formula 
'cleaner looking'.
-- 
** John C **

"Max" wrote:

> > =SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))
> 
> Think the parens for the 2nd term can be removed
> This shorter one would work equally well: 
> =SUMPRODUCT(($A$1:$A$9=H2)*$B$1:$F$9)
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,500 Files:363 Subscribers:64
> xdemechanik
> --- 
0
Utf
11/18/2008 1:53:01 PM
> .. And I also know that almost always after you post a volatile function,
> someone else comes along and posts a non-volatile function...

But of course. The inherent graciousness in me is to always allow space for 
complementary suggestions by others. This can only add further value and 
richness with alternatives.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---  


0
demechanik (4694)
11/18/2008 8:08:45 PM
As stated, volatile functions, such as OFFSET, definitely have their uses, 
and in fact, there are many times where one would want/prefer a volatile 
function. I have a workbook that I use very often every day that does 
want/need the OFFSET function, and I use it gladly. But why would one want a 
volatile function when one is not called for? Why would the OP double post 
and never respond back that 1/all of the proposed solutions actually worked 
for them?
-- 
** John C **

"Max" wrote:

> > .. And I also know that almost always after you post a volatile function,
> > someone else comes along and posts a non-volatile function...
> 
> But of course. The inherent graciousness in me is to always allow space for 
> complementary suggestions by others. This can only add further value and 
> richness with alternatives.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,500 Files:362 Subscribers:64
> xdemechanik
> ---  
> 
> 
> 
0
Utf
11/18/2008 9:17:01 PM
The comments were meant more for the OP, not for you, heavens forbid.
Please continue to do whatever you want. Its a free world.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
--- 
"John C" <johnc@stateofdenial> wrote in message 
news:6A72C4C3-FB61-4BE1-8E2A-866162926D1E@microsoft.com...
>I always include the second set of parenthesis. The microscopic amount of
> memory you could save by excluding them isn't worth it to make the formula
> 'cleaner looking'.
> -- 
> ** John C **


0
demechanik (4694)
11/19/2008 8:17:01 AM
> .. But why would one want a volatile function when one is not called for?

You're taking it further than what was asked by OP as the crux issue.
This wasn't mentioned by OP as an exclusion.

OP said:
> I would appreciate any ideas how to do this.

And I merely ventured an option for OP to address his/her crux. That's it.
Ok, so yours is better. But man, did I ever contest that? Look again.

> Why would the OP double post and never respond back
> that 1/all of the proposed solutions actually worked for them?

OPs are not always as gracious as we would like them to be, I'd guess, John. 
As an OP I'd always be gracious to all responders to my query, and I'd thank 
& reply to each and every response received in my thread.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
--- 
"John C" <johnc@stateofdenial> wrote in message 
news:4F64F3A7-E6A1-4A0B-9940-866544605261@microsoft.com...
> As stated, volatile functions, such as OFFSET, definitely have their uses,
> and in fact, there are many times where one would want/prefer a volatile
> function. I have a workbook that I use very often every day that does
> want/need the OFFSET function, and I use it gladly. But why would one want 
> a
> volatile function when one is not called for? Why would the OP double post
> and never respond back that 1/all of the proposed solutions actually 
> worked
> for them?
> -- 
> ** John C **


0
demechanik (4694)
11/19/2008 8:35:23 AM
I am trying to post a question and I can't get it to open so I hope you don't 
mind me asking you a question.
I have an if formula =if($Z$58="",ag46+ag48+ag50)-if($Z$58="X",0) If cell 
z58 is empty I want it to add the ag cells and if there is an x in Z58 I want 
it to put in 0.  But it is not adding it is only putting in 0 for both 
functions.  Can you help me?
-- 
Louise


"Max" wrote:

> > .. But why would one want a volatile function when one is not called for?
> 
> You're taking it further than what was asked by OP as the crux issue.
> This wasn't mentioned by OP as an exclusion.
> 
> OP said:
> > I would appreciate any ideas how to do this.
> 
> And I merely ventured an option for OP to address his/her crux. That's it.
> Ok, so yours is better. But man, did I ever contest that? Look again.
> 
> > Why would the OP double post and never respond back
> > that 1/all of the proposed solutions actually worked for them?
> 
> OPs are not always as gracious as we would like them to be, I'd guess, John. 
> As an OP I'd always be gracious to all responders to my query, and I'd thank 
> & reply to each and every response received in my thread.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,500 Files:362 Subscribers:64
> xdemechanik
> --- 
> "John C" <johnc@stateofdenial> wrote in message 
> news:4F64F3A7-E6A1-4A0B-9940-866544605261@microsoft.com...
> > As stated, volatile functions, such as OFFSET, definitely have their uses,
> > and in fact, there are many times where one would want/prefer a volatile
> > function. I have a workbook that I use very often every day that does
> > want/need the OFFSET function, and I use it gladly. But why would one want 
> > a
> > volatile function when one is not called for? Why would the OP double post
> > and never respond back that 1/all of the proposed solutions actually 
> > worked
> > for them?
> > -- 
> > ** John C **
> 
> 
> 
0
11/19/2008 4:40:12 PM
Try one of these:

=IF($Z$58="X",0,IF($Z$58="",AG46+AG48+AG50,0))

=($Z$58="")*SUM(AG46,AG48,AG50)

-- 
Biff
Microsoft Excel MVP


"Louise" <louise@aol.com> wrote in message 
news:106A4A85-468F-4CE0-9F28-FE49080B14CC@microsoft.com...
>I am trying to post a question and I can't get it to open so I hope you 
>don't
> mind me asking you a question.
> I have an if formula =if($Z$58="",ag46+ag48+ag50)-if($Z$58="X",0) If cell
> z58 is empty I want it to add the ag cells and if there is an x in Z58 I 
> want
> it to put in 0.  But it is not adding it is only putting in 0 for both
> functions.  Can you help me?
> -- 
> Louise
>
>
> "Max" wrote:
>
>> > .. But why would one want a volatile function when one is not called 
>> > for?
>>
>> You're taking it further than what was asked by OP as the crux issue.
>> This wasn't mentioned by OP as an exclusion.
>>
>> OP said:
>> > I would appreciate any ideas how to do this.
>>
>> And I merely ventured an option for OP to address his/her crux. That's 
>> it.
>> Ok, so yours is better. But man, did I ever contest that? Look again.
>>
>> > Why would the OP double post and never respond back
>> > that 1/all of the proposed solutions actually worked for them?
>>
>> OPs are not always as gracious as we would like them to be, I'd guess, 
>> John.
>> As an OP I'd always be gracious to all responders to my query, and I'd 
>> thank
>> & reply to each and every response received in my thread.
>> -- 
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> Downloads:20,500 Files:362 Subscribers:64
>> xdemechanik
>> --- 
>> "John C" <johnc@stateofdenial> wrote in message
>> news:4F64F3A7-E6A1-4A0B-9940-866544605261@microsoft.com...
>> > As stated, volatile functions, such as OFFSET, definitely have their 
>> > uses,
>> > and in fact, there are many times where one would want/prefer a 
>> > volatile
>> > function. I have a workbook that I use very often every day that does
>> > want/need the OFFSET function, and I use it gladly. But why would one 
>> > want
>> > a
>> > volatile function when one is not called for? Why would the OP double 
>> > post
>> > and never respond back that 1/all of the proposed solutions actually
>> > worked
>> > for them?
>> > -- 
>> > ** John C **
>>
>>
>> 


0
biffinpitt (3172)
11/19/2008 5:26:18 PM
Reply:

Similar Artilces:

sum of cells
Hi, In Cell AB10 I need a formula to sum the following cells inthe same line. The cells are: D, F, H, J, L, N, P, R, T, V, X, Z Is there a way to do this other than using: =d10+f10+h10........+z10 (sum of every other cell!!) =SUMPRODUCT(--(MOD(COLUMN(D2:Z2),2)=0),D2:Z2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Khalil handal" <khhandal@yahoo.com> wrote in message news:up25YVaXIHA.280@TK2MSFTNGP04.phx.gbl... > Hi, > In Cell AB10 I need a formula to sum the following cells inthe same line. > The cells ar...

Summing rows based on criteria in another column
I'm trying to consolidate rows of data based on the ID and OP column. In the attached image, I would like to consolidate ID # 190 because OP column is blank or zero and leave the other rows of data as is if there is a value in the OP column. +-------------------------------------------------------------------+ |Filename: excel problem.gif | |Download: http://www.excelforum.com/attachment.php?postid=4732 | +-------------------------------------------------------------------+ -- reybie -----------------------------------------------------------...

"You tried to assign a Null value..."
Hope I can be helped! Here’s the error message: “You tried to assign a Null value to a variable that is not a Variant data type”. Here’s the setup: tblContacts ContactID tblChapters ChapterID tblChapterMembers ChapterMemberID ContactID ChapterID (which is a bad design since a contact can only be a member of one chapter, but that’s how I designed it) The row source for subfrmChapterMembers is SELECT Chapter.ChapterID, Chapter.ChapterName FROM Chapter; frmContacts is based on tblContacts. I have subformChapterMembers on frmContacts. Not all contacts belong to a chapter. If I leave t...

How do I automatically change color of value if value is > 50 in .
Hi, I want to change the color of the value when it reaches a value that is > a certain value. Use 'Conditional Formatting' on the 'Format' menu Jock W "Vichingo" wrote: > Hi, > > I want to change the color of the value when it reaches a value that is > a > certain value. conditional formatting is what you want. Menu Format>Conditional Formatting, should be straight-forward what to do. -- HTH RP (remove nothere from the email address if mailing direct) "Vichingo" <Vichingo@discussions.microsoft.com> wrote in message ...

How do I put drawing objects behind text/cells in a spreadsheet?
Using a background will not work because I am dealing with mutliple objects which need regular moving and changing. since objects sit on top of the sheet, i am not sure you can do that unless the object has a transparent setting. >-----Original Message----- >Using a background will not work because I am dealing with mutliple objects >which need regular moving and changing. >. > ...

Find difference between 2 cells if critera in a 3rd cell is met
How would I find the difference between two cells depending on what is in a third cell? For example, in column G1 I want to show the difference E1 and whichever cell in column F contains the number 1. I was thinking something along the lines of using SUMPRODUCT to find the row with the #1 and then somehow subtracting G1 from the number column E that match the row where the number 1 was found. This is VERY confusing! At least, to me it is. There's probably a simple solution but I don't understand. You want the formula in cell G1? This is the really confusing part: ...

Cell Value + open application
Hi all, the following is what I am trying to accomplish: I have a spread sheet that I use to track incoming and outgoing documents. What I would like to be able to do is have cell in which I enter a name into open up and outlook template I have saved in the same directory (.oft file). Also be able to a pull information from other cells to populate the subject of the template. Thanks in advance for any assistance with this ...

cell reads as label but returns a value
How do you have a cell read on the spread as a lable (using the label in a drop-down list) and when selected, the cell will return a constant value? The cell might read: "Red Widget" but it would return a value of say: 257. Got any ideas on this one.... Thanks... Spydor -- spydor ------------------------------------------------------------------------ spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438 View this thread: http://www.excelforum.com/showthread.php?threadid=480383 Hi spydor, Is this what you mean? http://hanjohn.customer....

Range of worksheets for printing.
How do I select and then deselect a range of worksheets for printing? Click the tab of the first worksheet, hold the Shift key down and click a second tab. All the worksheets between those two endpoints (inclusive) have been selected--and you can print them, print preview, etc. The selected sheet tabs will appear slightly whiter than the non- selected sheet tabs. If you want particular sheets, then CTRL + click on each of the sheet tabs. To unselect the sheets, click on a different sheet tab (not holding the Shift or CTRL key down). Or rightclick one of the sheet tabs within your s...

Links not showing updated value
I have linked spreadsheets and I have noticed that one of the links is not showing an updated figure. How can I 'refresh' the spreadheet so that it shows the new value? Wanson try Edit/Links and click Update Values any use? Nick -- Nicko ------------------------------------------------------------------------ Nicko's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29477 View this thread: http://www.excelforum.com/showthread.php?threadid=491779 Thanks for this Nick. I did Edit/links, open souce/update now and then saved both the source and ...

Named ranges across worksheets
I want to have a named range that encompasses cells in two or more different worksheets. Excel help talks you through this - but it doesn't seem to work. Can anyone help ? (I want to do it so I can pivot on the data - I have found the "Multiple consolidation ranges" part of pivot tables somewhat impossible to work) Do you want to define one range that includes the same cell address or range on several worksheets? This is called a "3-D reference", and instructions on doing this are in Excel's help files. Or do you want to define the same cell reference on ...

Default value in a lookup field
Hello, Could someone help me : How can we set a default value in a lookup field ? Thanks regards WT If in 1.2 use the Form editor, select the field, click the Properties option and then the values Tab. Should be easy from there. If 3.0 then you need to go to Settings, Customization, Customise Entities, Double click the relevant entity name, Click Attributes, Double click the fieldname and you should see what you need. Be aware that MS have not allowed all picklists to be edited. If you can't edit them they are greyed out. -- Simon Morris "WT" wrote: > Hello, &...

combo box in each cell of a column
I would like to have all the cell of a column (ie column B) associate to a combobox Each selection should be independent from the other, but the list o selection values should be the same. Can someone help me? Thanks & cia -- Robert ----------------------------------------------------------------------- Roberts's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1576 View this thread: http://www.excelforum.com/showthread.php?threadid=27276 Hi use 'Data - Validation - List' and use the same list source for all drop down. Also see: http://www.contexture...

How to set focus or select next cell
I have five fields of inputs (say B5,C5,D5,E5,F5), after putting the data in cell F5, when I hit the enter key is there a way that the next focus cell is B6 for the next input, if so how? In other words after data is inputted in cell F5 the focus jumps to the next line at cell B6 ... thanks ahead for the solution. -dave Hi Dave Select the range B5:F62 before you start typing and it will behave like that for quite a while. HTH. Best wishes Harald "dave wagner" <dwagner@lancorpnet.com> skrev i melding news:IHbaf.7447$D13.133@newssvr11.news.prodigy.com... > I have...

Formula for transposing Row Values to Col Values
Excel2003 ... WS1 ... Row Range L2:Z2 ... Contains Text WS2 ... Col Range B3:B17 ... I wish formula to populate Text from WS1 ==> I know I can do this with Copy/Paste/Special/Transpose ... However, I wish a Formula to do it as I need to apply in additional places ... Thanks ... Kha One way... With Values in A1, B1, C1... use this formula in A3 and drag down =OFFSET($A$1, 0, ROW() - 3, 1, 1) Note that this function is volatile and as such will add a lot of calculation overhead to your spreadsheet. If you use a lot of these your performance will suffer... -- H...

Tab in a cell #2
Hey all! You know how in excel if you tab, you go to the next cell to the right? How can I tab in a cell, to make an indent? I also have a weird problem with this particular cell (maybe it is due to me spacing instead of tabbing...) In a cell, I have 3 paragraphs of written information. The last three lines however just keep going to the right, instead of wrapping, like I have this cell formatted to do. Any thoughts on how to resolve either of these problems? -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/me...

Formatting just visible cells
Is there a way to apply formatting to just visible cells on a worksheet. I have two columns hidden and when I apply font color to my worksheet, the hidden columns are applied with the font color as well. Is there a way to prevent this? Suggestions or help would be appreciated. Select your region, then choose Edit/Goto. Click Special, then select the Visible cells only radio button. Click OK. Apply your format. In article <140e801c3f7ce$ba5656d0$a501280a@phx.gbl>, "bagia" <bagia@ureach.com> wrote: > Is there a way to apply formatting to just visible cells ...

Format cells to Auto Enter
I have 2 columns that I only have to input 2 characters in each column. ie 3d, ad. Is there a way to format the cells so that when the 2nd character is entered it will automatically shift to the next cell without having to hit the "ENTER" or "TAB" keys TIA Dave No Excel needs to know when you have finished editing a cell. Enter, Tab or Arrow Gord Dibben MS Excel MVP On Wed, 25 Jun 2008 16:21:00 -0700, Dave <Dave@discussions.microsoft.com> wrote: >I have 2 columns that I only have to input 2 characters in each column. ie >3d, ad. >Is there a way...

Filling formulas with specific cell references remaining the same
I need to fill a formula in a range of cells downward, specifically the formula IF(OR(O2=U1,P2=U1,Q2=U1),2-L2,0), with the U1 cell references remaining the same, continuing to refer to U1, while the other cell references (O2, P2, Q2, and L2) change and fill normally. Conversely, I need to be able to fill the formula right with U2 being the only reference that changes. Is this possible to do? If so, how? Doing this manually would take several hours. I am using Excel 2007. A suitable topic to type into Excel help is "relative & absolute addressing" The dollar sign i...

Account Description Missing when copy range of account in Mass Mod
In GP 9.0 When you create accounts, the account description can be generated automatically by combining the descriptions of the segments that are included in the account. In Version 8 When I use mass modify to copy a range of account the new account description keep the source account description. ...

sutraction after sum
if i have data a, b, c, up to z in my table with specific amounts, how do i get their sub-total if i don't want to include data l, m, n, o & p? how can you sum it up and subtract those specific data that are excluded? what is the code? -- Message posted via http://www.accessmonster.com In query design, type an expression like this into the Field row: Nz([a],0) + Nz([b],0) + Nz([c],0) + ... The real problem here is that you've build a spreadsheet in Access. Repeating columns like that is not the way you store data in a database: it gives you exactly the kind ...

Chart Series from single value
I have a series of charts (Bar-Line) embedded on a worksheet. The bars in each chart represent monthly values and are dynamically set using VBA code, this works well. The line needs to extend across the chart for the number of months in the chart, the value of this line is the same at each point and is stored as a single value in one cell on the worksheet. Is this possible? -- Regards, Nigel nigelnospam@9sw.co.uk I usually advise people to use as many cells as makes what they want easy. You can use this approach: http://peltiertech.com/Excel/Charts/AddLineHorzErrBar.html to add...

Unable to enter "S" or words starting with "S" in cells
Am working with Excel 2000 (Version 9.0.6926 SP-3) When entering words starting with "s" or just "s", the entry fails and brings up one of several menus. The menus vary depending upon how many keystrokes I have made before realizing the text entry failed. "S" is teh only letter I have found where this problem occurs. If I double click on the cell then enter the word starting wtih "s", then I can enter the text. This is cumbersome and very time consuming. Please advise Do you have the same problem with any other programs? It sounds to me like a...

Text values to numeric values
Is there a formula to convert a text value in a cell to a numeri value? Thanks, Jayveeja -- jayveeja ----------------------------------------------------------------------- jayveejay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=504 View this thread: http://www.excelforum.com/showthread.php?threadid=39463 Good afternoon Jatveejay Yes there is. Say your numeric value is in A1, in B1 the formula: =VALUE(A1) will show the number as a value. If you wanted to convert a number in its cell from a text number to a value number, ie., not use a formula in a di...

Where I can find Value Added Chain Diagram in Visio?
Or is this diagram in different name in Visio? I can't find it... If you mean Value Stream Map, there is a new template for this diagram in Visio 2007 Professional. -- Mark Nelson Office Graphics - Visio Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Dia" <Dia@discussions.microsoft.com> wrote in message news:9E5C79D9-54B7-4115-9709-85AB28169CA1@microsoft.com... > Or is this diagram in different name in Visio? I can't find it... ...