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 |

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 |

11/17/2008 10:16:01 PM

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 |

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 |

11/18/2008 1:53:01 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 |

11/18/2008 9:17:01 PM

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...

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 -----------------------------------------------------------...

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...

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 ...

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. >. > ...

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: ...

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 ...

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....

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...

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 ...

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 ...

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, &...

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...

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...

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...

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...

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 ...

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...

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...

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. ...

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 ...

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...

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...

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...

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... ...