Re: A Sum Function Based on a Dynamic List


One slight twist on the sumif function - If my data is on sheet A and
I need the sums on sheet B, how would I include the sheet name?   I
know in regular, more simple formulas, I would use sheetA!..., but
with sumif, I'm getting #NAME when I use the exclamation point and the
formula.   What I have is:
=SUMIF(sheet A!B:B,xx,C:C).  Thanks in advance for your help.
6/24/2003, 6:39:14 PM

Excel Function help needed please!
1 replies , 3/4/2004, 3:02:26 PM
I have an Excel spreadsheet with several worksheets from which I need to compare values from several columns in sheet one to a single column in sheet two, and return the value contained within one cell (a "title" at the top of the column)in sheet one to an adjacent cell to the original value cell within sheet two. Can anyone help me to figure this one out? Thanks!

IF Functions
3 replies , 6/21/2005, 7:19:04 PM
I would like to see if anyone would be able to help me out on this formula? If B3+D3=o then E3 would have nothing but if C3+D3=any number but 0 that number would show up on E3 and vice versa Does this make sense to anyone?

COUNT Function
2 replies , 10/19/2004, 5:21:20 PM
Thanks again, Tom. I am not a very sophisticated user (can you tell) and am having problems applying the formula you gave me. Can you explain the "--" in the statement? Many thanks Chris

Unable to use Excel Workbook coded functions
1 replies , 7/2/2008, 3:04:02 PM
Hi!!! I did code a workbook to deal with raw data. It was just a Excel 2007 Workbook project in VS but not an add-in project. Debuging and running were all fine to me. I noticed that after building there was a folder with five files appearing instead of a single file. However, when i passed the worksheet and the rest of files to other colleagues. It did not give any chance to them to use. Basically, the buttons and menus etc did not work. Could anyone give me an idea what I need to setup/config for them? I appreciate.

Excel function
4 replies , 8/31/2006, 7:11:34 AM
Hi. I have four coloumns containing number of accounting accounts (coloumns A and C) with values (coloumns B and D), for example: Coloumn A Coloumn B Coloumn C Coloumn D Column E (result) 40100 600 40100 700 100 40101 500 40101 700 200 40103 900 40102 500 500 (I get 0) 40103 1000 100 I need a formula in Coloumn E that will substract the values in coloumn B from the values in coloumn D, depending on the number of of the account. I use a VLOOKUP formula, but I have a problem when in coloumn C there is a account number that is not in coloumn A (Acc. number 40102 with value of 500). The result should be 500, but I get 0. I hope it is clear to understand Please help Thanks -- Gordana Godzo Head of Controlling & Budgeting TITAN Group - Cementarnica "USJE" AD Skopje Prvomajska bb, 1000 Skopje Republic of Macedonia tel: +389 2 2786 138 fax: +389 2 2782 535 e-mail: g**********

Gordana Godzo
I am not sure if or how to use all three in a formula. For example: =if(b2="red", and c3="blue", d6="green") if that makes sense at all.

How do I nest an OR function within an AND function?
3 replies , 11/30/2004, 5:37:04 PM
Here is my example - can I do this? What have I done wrong I can't get it to work. IF(AND(OR($AD4=”A”,$AD4=”SU”,$AD4=”GF”),$AF4<>””),”Updated Allowance is Not Applicable”,"")

Nested IF function Revisted
7 replies , 1/27/2007, 6:57:01 PM
I asked this a few days ago but I didn't realize I had other factors I needed to account for. I need to modify the formula I am using: =IF(OR(J2="N",K2="N",M2="N"),"N",(IF(AND(J2="SP*",K2="Y",M2="Y"),"Y",""))) I'm trying to create a function to automatically determine if an engine is fully assembled in column N. If the answer is "N" (no) to either columns J, K or M then column N must display "N" (no, the engine is not completely assembled). If the answer is "Y" (yes) to columns K and M and if column J has an SP # in it (i.e. SP123-12) then column N must display "Y". If all columns J thru M are left blank then column "N" must display nothing (blank). (By the way, I'm not including the "L" column on purpose). I've been getting "N"s on the rows I want to get "N"s but blanks on all rows I should be getting "Y"s for. J K L M N Belt Motor Oil Valves Engine Assembled? 1 2 N N Y N N (=IF function) 3 SP123-12 Y Y Y Y (=IF function) Help! What am I doing wrong? Thank you, Studebaker

as with my previous post this is for a spreadsheet at work... although with a different spreadsheet instead of periods and cash etc... we have a spreadsheet that is called a transfer sheet. Within this sheet we enter the stock number of a product and it returns the name of the product, we then enter manually the quantity of the product, the current market price and we get a total...there is also a combined total if we enter more than one product... the lookup function for the name is great, but what i hate doing is everytime i have to transfer product from one store to another i have to bring out our truck sheets try to find the price of the item and etc, or if its not on a truck sheet i have to call another store or more stores to find the price on things we do not order everyweek. i am currently trying to make it so that it looks up the price gives me a total(which i know how to do) and then a final total(again know how to do) problem is we have probably 100+ products and not everything is ordered everyweek, just the stuff we use the most, so i dont know the current prices on many of my products....although i know the normal stuff we order 3 times a week... is there a way that when the function goes to lookup the price and finds that the value is 0 or the cell is empty it then asks the user to put something in there without erasing the function??? i have a second way of doing it but it involves putting two cells into one cell having one lookup and if zero let the user use the other one... -- elitewolverine ------------------------------------------------------------------------ elitewolverine's Profile: View this thread:

Simple Indirect Function?
5 replies , 3/27/2008, 5:56:47 PM
Hi all. In sheet1, cell A1 I have a pick list that has all the sheet names in the workbook. In cell D5, I need a formula that is a cell reference to B75 of whatever worksheet is selected in cell A1. Any ideas? Thanks!

Find in list function
2 replies , 8/22/2005, 10:46:06 PM
I have two lists that should contain the same values. In Excel, they are in two columns. In the third column, I want to put the values that are in List A but not in List B. In the fourth column, I want to put the values that are in List B but not in List A. List A is from A1 : A501 List B is from B1 : B458 Can this be done in Excel?

If And Or functions
2 replies , 2/16/2005, 3:49:43 PM
Hello, Looking for help in using above functions in one formula if possible. A B C 1 1234 195 formula (with "yes" return) 2 1234 190 "yes" return also needed because it corresponds with equal number above it 3 2222 195 "yes" return also needed 4 2222 195 "yes" return also needed 5 2222 190 "yes" return also needed I'm looking to get a "yes" return in column (C) if the number in column A (A1) matches the one after it (A2) but only if the first number (A1) has 195 in column B (B1) and 190 or 195 in column B (B2) for the equal number in (A2). I also need a "yes" return for the second number for filtering purposes. I have a long list of numbers and I've Sorted it first by column A then by B in descending order. I'm looking to autofilter list (column c) by "yes" but need to view all equal numbers (A1 & A2). I hope I explained this correctly. Any help is greatly appreciated. Soz

can I execute and 'substring' function in Excel
4 replies , 12/31/2005, 6:46:02 AM
I have a cell that I need to fiding out what the 1st two characters in the cell are AND then put a related character in another column. Can I do this in Excel?????? -- Larry

Loss of functions in copied spreadsheet
3 replies , 11/24/2003, 4:41:08 PM
I moved a spreadsheet to a new computer (XP system), still using Office 2000. Formulas such as =(TIME (0,RANDBETWEEN(0,11)*55,0))+(TIME(7,0,0)) work fine on the original (Win98)and on my laptop (also Win 98), but not on the new machine. Simple functions work on the XP system. What component am I missing? I did enable the "accept labels in formulas" option. Thanks, JB john(dot)benton(at)

3 replies , 1/5/2004, 2:19:51 AM
I want to use conditional formating. I want a formula to check three cells to see if they equal Yes, if they do I want to change the background from white to yellow for cells range A5 through O5

Billy Joe
Function or number depending on input
7 replies , 9/5/2005, 1:00:19 PM
Hi, i've got a spreadsheet with a function i would like to duplicate: There are 2 cells, one with the Yield of a security (in this case a bond) and another cell with the price of the security (the same bond). Lets say A1 holds the price and cell A2 holds the yield. Now Excel functions Price() and Yield() use more or less the same arguments and let you calculate the Price of a bond, based on a certain yield OR let you calculate the yield of a bond, based on a certain price. So it's a bit like the chicken and the need a price to calculate yield or you need a yield to calculate a price. In this spreadsheet i'm having they allow you to type in the Price of a security in cell A1, which will give you a yield in cell a2. The nice thing however is the following: if you type a yield in cell a2 it will give you the price in cell a1. So based upon your input Excel looks at the cell where you have input the value and puts a function in the other calculates the result and puts the original function back into the cell where you have just typed a value, allowing you to do further calculations with that function....So even when you type a value in a cell, somehow excel still knows what functions should behind it once it has done it's calculations......Maybe i'm not explaining this very well but if someone has any idea how you can do this i would love to hear from you ! Thanks in advance!!!! -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: View this thread:

Jan Jansens
Linked Cells and Sort Function
0 replies , 8/14/2003, 5:37:34 PM
I have set up a worksheet with project names/numbers (column for each). On the next worksheet, I pasted this information as linked data and added additional columns for descriptions. When sorting the first worksheet, the linked columns sort appropriately, but the column with the descriptions does not sort. Therefore, the descriptions are not aligned with the correct project name/number. Help - I have done this type of set up in past and did not have this problem. Thank you

Copying Functions
1 replies , 6/11/2007, 12:43:01 AM
I copied a cell from another worksheet A ex. =Sheet1!B12 The info from Worksheet A apears on Worksheet B, the current sheet I am working on. My problem Is when I copy the information from to cell it changes the info, it reads ex. =Sheet1!C12 How can I keep it from going to C12 and Keep the info from B12, without doing it manually? Thank you

if function
5 replies , 3/31/2008, 3:19:00 AM
Hi doing my cert 2 and i am stuck on 1 formula i need to work out the total cost of something where the first 1000 are $0.50 and anything over 1000 is $0.40 using if function. It all seemed so straught forward till i got to this. Thanx in advance

IF Function Multiple logical tests - Syntax
9 replies , 1/15/2004, 5:21:25 PM
I want to impose two conditions. Specifically, If G8>=100 and H8>=, value if true,value if false. I am failing to find the proper syntax. Any help would be appreciated. Thank you. Good day.

Re: Text functions
4 replies

Space Function for Worksheet Functions
2 replies , 12/22/2006, 4:34:25 PM
Is there a way to use the VB Space(function) in a cell? The number of spaces that I need in each cell can vary. Any ideas?? TIA

handout function
1 replies , 3/3/2004, 9:37:35 AM
When printing a number of slides using the handout function (i.e. 6 slides per page), for an unknown reason only the first slide for each page will print. For example the first slide will print on page 1, the seventh slide of page 2, thirteenth on page 3 and so on... No other slides print, an empty space just exists where they should go. If anyone has heard or knows of a solution for this problem it would be much appreciated. Its PowerPoint 2001 for Mac OS9. Various printers have been used including a Canon CLC-3200 and a HP 8000n. When performing the same function from a PC, the problem does not occur. Thanks.

use a formula as a cell reference in a function
6 replies , 8/3/2005, 12:01:05 PM
I have a workbook with a Summary sheet and then a series of sheets fo each month/year. Onthe summary sheet I use column A for the month/yea as a date formatted "mmm yy" ie the same as the sheet names. in columns B,C,D I want formulae that sums or averages a fixed rang on each sheet but I would like the formula to take the sheet name fro Columa A rather than hard coding it. So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take th "Dec 04" from the text in cell A3 Many thanks -- tony ----------------------------------------------------------------------- tony h's Profile: View this thread:

tony h
0 replies , 7/16/2003, 8:01:48 PM
Does anyone know which function can be used to: Lock on to a day in a calender, or a date in a cell? eg. day one = 1st jan period of time on a course = 2 weeks end of period = 14th jan which is wednesday but required is the end period to be friday 16th this also needs to allow the period of time to be variable!! eg 3 weeks 4 weeks. Many thanks.

AVG Function
7 replies , 5/12/2004, 8:50:03 PM
I have a spreadsheet calculating avg hours worked per day. column b5 is where the function is located. the avg function runs from cells c5:ag5. My problem is this....Make this go away please: #DIV/0!. It is sitting in cell b5 waiting for me to enter a value in the function area. But I want b5 to show no text until I enter a value. Can anyone help? Thanks in advance for your time, Steve

Occasionally I type in a simple function or formula such as =AA7 and Excel treats it like a literal instead of a function. If I open another spreadsheet and type in the same formula it correctly gives me the value of cell AA7. Any clues.

Leon Hairie
Lookup function
5 replies , 8/11/2005, 7:35:07 PM
I am using the following lookup function to return a value. I am currently using one sheet for the values. I need to be able to use a number of sheets representing different counties and values. The formula looks like this =VLOOKUP(F12,Sheet1!B2:N53,2) I would like to be able to vary the Sheet1 to be different counties and equal a cell where the user would imput the county name. The sheets would be named the same. Can this be done?? Jeff

The Group Field button on my Excel 2007 pivot tables is not always available. I've even copied all of my data, started a new workbook, and pasted the data into is as values, created a pivot table, and the Group field button is not available for some of the columns of data but for others it is. Does anyone know why this would happen? I've tried other spreadsheets with different data and it does the same thing. I've tried having the data formatted as dates and some of the date columns will work and others won't. Have tried using just as number values and that sometimes works and sometimes doesn't. This also happens on my co-workers Excel 2007 installs as well. Any help would be greatly appreciated.

We have created excel spreadsheet. In one column we have description of the activity , 2nd column has activitiy ranges. We have to build an VLOOKUP formula to check the description of the activity and check the activity ranges in the 2nd column and than give us the correct value in a particular column (Correct values are in 4th column). Request help on the same to build a formula using VLOOKUP AND functions.

Function Macro
1 replies , 11/1/2004, 6:02:09 AM
Hi - I have to do an assignment which involves writing a function macro. This is what I have written: Sub Review() Function Review(Overall As Single, recent As Single) As String If recent >= 4 Then Good& Else Poor& End If If recent >= Overall Then Improving& Else Interview& End If Review = recent & Overall Range("E8").Select End Function End Sub Now I don't know much about vba and I'm having trouble making this work. Would some kind person out there be able to explain to me where I am going wrong? The message that I keep getting is that Review is too ambiguous !! I would very much appreciated any help at all. Thank you Suzie

No response by function "Search in Files"
0 replies , 12/5/2007, 3:56:03 PM
Hi, Scince 1 week I have a prpblem with the function "Search in Files" in .Net 2003. When I search in the actual Document or in all open Documents it's all perfect. But when I'm going to search in the hole Project oder in some directories in Workinplace the Environment doesn't response and I have to close it with taskManager. I've already search the internet but found no further information for this situation. Following steps I tried: - .Net Framework 1.1 inclusive new Update reinstalled - .Net Studio 2003 repaired - .Net Studio removed / new installed - .Net Studio removed / Registry cleaned / ProgrammFiles - VS .Net2003 deleted / documents and settings from user deletet / .Net Studio reinstalled It's not better!! Same error - the studio didn't send response and don't search any Files. I've installed VS 6 and VS2005 in both applications the function "Search in Files" do great work, but not in VS 2003. But for the most Projects I need the .Net 2003 and the Search in Files is the basic function ....

2 replies , 12/22/2005, 3:23:02 PM
In the past I was able to use an interpolation function that would give me an interpolated value from a series of numbers. For example, in excel, my 'A' column would constist of a column of numbers (these numbers would be different elevations) and my 'B' column would consist of a column of numbers (these numbers would be areas that were associated with the elevations). If I used the interpolation function, in a cell I would list an elevation that was between the known elevations and the cell that contained the function would calculate the interpolated area for that elevation. However, recently I just got a new computer that has Excel 2003 and I can not find this function. If anyone can offer any advice, I would greatly appreciate it.

Hyperlink function in an If function
1 replies , 6/30/2008, 9:13:58 PM
Hello: I'm using Excel 2003, and what I'm trying to do is use the HYPERLIN function in an IF statement so that if the value is TRUE then th HYPERLINK is active and if the value is FALSE then the HYPERLINK woul be inactive. Does any one know of a way to make this work withou getting the error message, "Cannot open the specified file.". Mat -- GoBow777