Get rid of #VALUE, after multiplying

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Worksheet
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - DGET/DSUM etc Have a Lotus (remember that one) spreadsheet that uses lots of Dfunctions. Converting it to Lotus and need help with the Excel function. Here's the Lotus function in my FTE worksheet: @DGET(MAP,"directorate",Activity=B6) In Lotus MAP is the name of the range in another worksheet. That wksht has a column for Directorate (see next) and one for Activity "directorate" is the name of the column in that range in MAP that I want to look in Activity=B6. Refers to the Acivity Col in my FTE worksht and those same activities are in the MAP range. In short.....go to the range named MAP. Look in the Column called directorate and find the Activity that is specified in cell B6 of this worksheet and put that directorate value in this cell in my FTE worksheet. I've tried everything and I keep getting NAME or VALUE errors. I'm sure it syntax but its got me beat. Can send the Lotus file if that would make it easier to understand. Thanks so much broughton.ron@ic.gc.ca or ron_b@hotmail.com Tag: Get rid of #VALUE, after multiplying Tag: 497758
  - 2
    - if, or I am creating a spreadsheet to make things more simple. I want to be able to select a word from a drop down list and once that word appears in the cell I want the tasks associated with that work to fill in the cells below. Example: One of the words is Chiller. When that word is selected I want the tasks associated with cleaning a chiller to automatiically be read from another worksheet and fill in below the word Chiller on the form. -- AJ Tag: Get rid of #VALUE, after multiplying Tag: 497756
  - 3
    - How to make Same formula over multiple rows but still holds row # I am trying to take a new list of people, and based on these people account numbers, I need to display certain Text. For example-If Julies account number is 12345, Then she is Marks guest, but if her account number is 54321, then a field would display Lisas guest. I have a Fixed field of text that I need to match to the account numbers that always change. Is thier a way to Create the If formula over Multiple cells but still keep the original formula without it auto completing, BUT still carry over the Row #. I have done it either one way or the other, Same formula times 2000, or Auto complete formulas times 2000 (auto complete changes the formula so it doesnt op any more). Also, Is thier a way to create quickly a Large If series formula, I need to take one cell and insert a If formula containing 1400 If possibilities. Thanks Alll Tag: Get rid of #VALUE, after multiplying Tag: 497754
  - 4
    - Use Tab Name in Lookup Field I have a large File that I need to summarize. On the Summarize tab I need to pull data from other tabs "2007-01", 2007-02, etc... There are several fields on each tab that I need to lookup and enter into "Summarize". Example, "2007-01!E5" and "2007-01!-G5", etc... How can I use the tab name to repeat and pull data from each tab? See Below: Invoice # is the source tab name. Inv Date, Fee, Per Diem isn the data I need to pull from each tab. BILLING SUMMARY - 2008 Invoice # Inv Date Fee Per Diem Room Other Total 2008-01 1/07/2008 $600.00 $50.00 $126.14 $31.11 $807.25 2008-02 1/14/2008 $600.00 $50.00 $105.57 $- $755.57 2008-03 1/14/2008 $600.00 $50.00 $105.57 $- $755.57 Thanks -- A MACKENZIE, CMA, MBA Tag: Get rid of #VALUE, after multiplying Tag: 497753
  - 5
    - Variable Worksheet Name in Formula Good morning -- I have a workbook storing similar data on multiple worksheets. I have a 'Report' page, from which I'd like to abstract the right person's data onto a jpeg form and print it out. I'd like to be able to put the person's name in a cell at the top of the page, and have formulas extract the data from the associated worksheet. So, if I have worksheets labeled 'Smith', 'Jones', and 'Wesson', on the 'Report' sheet I'd like to put 'Smith' in, say, cell B1 and have a formula pick up that value and go to the 'Smith' page to pull in the info from, say, cell C50. Is that possible without VBA code? TIA Tag: Get rid of #VALUE, after multiplying Tag: 497747
  - 6
    - How do I put worksheets in a workbook in alpha order in 1 step Tag: Get rid of #VALUE, after multiplying Tag: 497746
  - 7
    - Conditional Formatting Excel 2007 Just installed 2007 and like it - would like to use conditional formatting to highlight cells based on if the cells equal a group of numbers - actually it is a worksheet containg all the office pool powerball tickets so I want to go through the worksheet and highlight the winning numbers - so I need to be able to have something that says if a cell in the range = 1 or 2 or 3 or 4 or 5 etc. Can it be done? -- yelnocer Tag: Get rid of #VALUE, after multiplying Tag: 497745
  - 8
    - Merging & Centering Cells I have been working right along in about 8 worksheets now; opening and closing them and then this morning I tried to merge some cells and I could not either by the icon on the toolbar or through format cells. Does anyone have any ideas? I have no protected worksheets in this workbook. Tag: Get rid of #VALUE, after multiplying Tag: 497743
  - 9
    - merge cells disabled how do I enable I have an xcel spreadsheet that has merge cells button disabled. How do I enable this button? Tag: Get rid of #VALUE, after multiplying Tag: 497732
  - 10
    - automatically show date day by day I need to make a list with all the days of the year in it. It is very cumbersome to enter every day manually. Is there a way to do it automatically? Thank you! Tag: Get rid of #VALUE, after multiplying Tag: 497727
  - 11
    - Linking and keeping total Column at the Bottom I am linking data in to a monthly report spreadsheet. For example, I will link 10 lines of data and then I have a "Total" row in line 11. I would like to know how I could keep the total row constant even if I link more than 10 lines at a a time. For example, if I have 12 rows that I am linking in a month, my "Total" row would be overwritten. How can I keep my "total" line the last line regardless of how many rows I link? Thanks. -- Bob Tag: Get rid of #VALUE, after multiplying Tag: 497723
  - 12
    - Help on macro coding for URL Does anyone have any suggestions on following macro coding? [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.stata.com/help.cgi?macro", Destination:=Sheets("Temp").Range("$A$1")) [Not working] myVariable = "http://www.stata.com/help.cgi?macro" With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & myVariable, _ Destination:=sheets("Temp").Range("A1")) It seems to me that "URL;" & myVariable is not equal to "URL;http://www.stata.com/help.cgi?macro". Do anyone have any suggestions on how to solve it? Thank you very much for any suggestions Eric Tag: Get rid of #VALUE, after multiplying Tag: 497721
  - 13
    - references I have a list of dates (consecutive weekdays - some holidays) in Column D in Sheet2. In sheet1, I have a reference to those days. For example in A3 I have the formula =Sheet2!D3 which displays the correct date. I'd like to copy the formula to every 7th cell in Column A in Sheet1. What's happening now is that the copied formula increments by 7 The formula copied into the cell A10 is "=Sheet2!D10". How can I force it to increment only by 1, so that it references the dates in Sheet2 correctly? Thanks Bert Tag: Get rid of #VALUE, after multiplying Tag: 497707
  - 14
    - How to configure Excel to exceed the limit 256 columns. How to configure Excel to exceed the limit 256 columns. Tag: Get rid of #VALUE, after multiplying Tag: 497704
  - 15
    - Compare Columns Ok I have two columns: A B Customers: Prospects Abc <highlight>Ghi<highlight> Def <highlight>Abc<highlight> Ghi Tuv Xyz <highlight>Def<highlight> I want to run a search against the customers in column A and to see if they appear in the prospects column, if they do, I want to highlight them automatically. It has to be done automatically because in the real list there are about 140 customers and 4000+ prospects. Thanks, James Tag: Get rid of #VALUE, after multiplying Tag: 497700
  - 16
    - returning blank cell when conditions not met Entered into G7 the following =IF(F7<3,3-F7,0) F7 is the SUM of C7,D7,E7 If there are no entries in C7,D7 and E7 I want G7 to be blank. meaning until there are numbers entered I don't want to see 0 in G7 Any ideas. Since i can't seem to find a way to put in an else statement with excel 2003 Tag: Get rid of #VALUE, after multiplying Tag: 497691
  - 17
    - Using checkboxes, can I write formulas based on what is checked? If I use checkboxes for different types of defects, can I then create a formula to add up what is checked? Thanks, Liz Tag: Get rid of #VALUE, after multiplying Tag: 497690
  - 18
    - simple If but not for me I have three columns in which contain amounts of inventories for certain items I need for my concession. An adjacent column would tell me how much to order. Ex. c5, d5 and e5 would have on hand amounts. g5 would tell me how much to order. I tried to make it simple by using f5 to add the amount from the three previous cell. So =if (f5=3,0,g5-f5) But with this I get 0 even when f5 is 2. Not every f5 would equal three. Each item on my list would have their own amounts that I would need to compare to and enter in column g. Any help would be appreciated. Thanks in advance. Tag: Get rid of #VALUE, after multiplying Tag: 497684
  - 19
    - counta in array formula not working Hi, My series B13:B20 contains zero's and text values. I wish to count the text values but not zero's or blanks. I have tried the following as an array but its counting all cells in range with the result = 8 which is not correct (as there are zeros and balnks in the series). =COUNTA(IF(B13:B20<>0,"A",0)) Any ideas? Bruce Tag: Get rid of #VALUE, after multiplying Tag: 497676
  - 20
    - Importing data I am importing a report into Excel and I would like to make comments on each line. But this report will change as I update purchase orders in our system. It will also change as purchase orders are received into our system. Our system generates this report and I would like the comments to stay with the line when I import the new report that is different. I would like to make new comments on the new lines. Is this possible? if so, how? Item number Purchase Order Date received Name Comment 11001 102548 2/14/08 Dell Computer Waiting for invoice 12001 152121 3/4/08 Lowe's Cancelled order Tag: Get rid of #VALUE, after multiplying Tag: 497675
  - 21
    - using IF to skip I am using =NETWORKDAYS(B23,B29)-1 as one of my formulas. I have the -1 because I am tracking several different time frames going from one place to another and without the -1 it is counting a day twice. I need this formula in every cell that has the days, but there won't be a time span every time. When there's no time span i get a -1 in the cell which is messing up count. Is there a way to add an IF function to the function above that says If -1 then skip? or count as 0? I've tried several different ways and it's not working. Thanks Tag: Get rid of #VALUE, after multiplying Tag: 497674
  - 22
    - looping formulas I have a worksheet that is laid out in two sections The first is as follows: A B C D F G H I 1 Task PM EE ME Total Jan Feb Mar 2 Controls 2 5 7 14 5 6 3 3 M&S 3 7 10 20 7 7 6 Total 5 12 17 Note: Total is spread manually over months The second section is below the first & looks like F G H I J 6 Labor Jan Feb Mar Total 7 PM 1.76 1.9 1.33 5 8 EE 4.24 4.59 3.17 12 9 ME 6 6.5 4.5 17 The end objective is to summarize the labor by month. So in cell G6 i would like a way to run the formula (B2/F2)*G2 but i need that formula to run for every row that has PM hours so i end up with total PM hours for Jan etc for all labor categories. In the end the totals in the column J of the second section should equal the total row 5 of the first section. I've tried a sumproduct with no luck. I'm really good with formulas & i'm pretty good with macros so any type of solution will work. Any suggestions at all are greatly appreciated. Tag: Get rid of #VALUE, after multiplying Tag: 497667
  - 23
    - SumIF Okay, I am a total rookie with SumIFs. I want workbook March.xls to link whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks Tag: Get rid of #VALUE, after multiplying Tag: 497662
  - 24
    - Extracting values from a single dynamic cell to a list daily I have a dynamic data sheet that is hooked up to a stock tradin monitor. The sheet displays a portfolio which is ever changing. I want to take the portfolio value in a single cell and transfer to value in that cell on a daily basis to a list on another sheet. By doing this I will be able to track the fluctuations of my portfolio over time. How can this be done? Tag: Get rid of #VALUE, after multiplying Tag: 497654
  - 25
    - creating a formula on one sheet that uses results of formulas on o I'm trying to create a worksheet that has a formula summing the results of formulas in several other worksheets, but when I run it the result is blank. Is there a way to use cells from other worksheets where the number in the cell on the other sheet is the result of a formula? Tag: Get rid of #VALUE, after multiplying Tag: 497653
- Next
  - 1
    - multiple functions in a single cell Example: I want to be able to display "Cathy's Check" in cell G4 by typing the number "2" in cell B4 but I also want to display "John's Check" in cell G4 by typing the number "3" in cell B4 as well. Can some one help me please! Tag: Get rid of #VALUE, after multiplying Tag: 497650
  - 2
    - Extract info from one sheet to another I am trying to pull data from worksheet to another. I would like to search for text found in one sheet and return a relative value from a different column . Here is an example Worksheet 1 contains a coumn of values: apple orange banana Worksheet 2 contains two columns: apples (red delicious) red apples (granny) red oranges (florida) orange banana yellow In Worksheet 1... If the string apple is found anywhere in Worksheet 2, column 1, I would like it to return the color from Worksheet 2, column 2 and place it column 2 of Worksheet 1. Any suggestions? Thanks! Tag: Get rid of #VALUE, after multiplying Tag: 497639
  - 3
    - Embedded Video This may not be the right forum, but I can't find one that seems to suit this Q. I am trying to embed a 1.2MB MPG into an Excel 2003 spreadsheet so that users of the spreadsheet can click on the icon and watch the video. It will not work. I am going to Insert, Object, Create From File, and once I chhose the file and click OK, Excel freezes. Any help? Thanks Tag: Get rid of #VALUE, after multiplying Tag: 497636
  - 4
    - Sumif with multiple columns in sum_range Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel Tag: Get rid of #VALUE, after multiplying Tag: 497631
  - 5
    - SUMIF vs SUMPRODUCT I was using the SUMIF command as follows (=SUMIF('Active Data'!Z:Z,2,'Active Data'!AK:AK) ("Active Data" is the previous tab), and the formula works great!.... until the criteria for the calulation changed. Now I require 2 sets of criteria before I sum the AK:AK column. I changed the formula to(SUMPRODUCT(('Active Data'!Z2:Z1000=2)*('Active Data'!BE2:BE1000=1)*('Active Data'!AK2:AK1000)). The additional data is coming from column BE. Apparently the SUMPRODUCT doe not like my referencing the Active Data tab. This formula returns the ever popular #VALUE! answer. Column Z is the month code (Feb), column BE is a customer code (1,0 or -1), and column AK repersents the hours of service. The formula should result all the hours worked(AK) in Feb(Z) for all the customers with criteria "1" (BE). (in this case 408 hours worked.) Tag: Get rid of #VALUE, after multiplying Tag: 497624
  - 6
    - Formula for assigning distinct name to first instance in a list of I have a spreadsheet of 3000 contact names I am trying to assign these in the most "fair" fashion to account resps. My thought was to create a column called "account_rep" I then put account_rep_name1 account_rep_name2 account_rept_name3 In, colum1,Row 1 account_rep_name1 colum1,row 2 account_rep_name2 colum2,row 3 account_rept_name3 colum1,row 4 account_rep_name1 colum1,row 5 account_rep_name2 colum2,row 6 account_rept_name3 the pattern continues respectively through the list to row 3000. However, what has happened is that there may be more than one account rep assigned to an account. For example in the list I have. account_rep_name1 Suzie Queen ABC Company account_rep_name2 Suzie Queen ABC Company account_rept_name3 Suzie Queen ABC Company so, I want to assign unique rep to the account the first time his name hits the account down the list. What is the best way to do this? Thanks, Robert Tag: Get rid of #VALUE, after multiplying Tag: 497623
  - 7
    - formula required to return data from one column Hi, I have a query on some data i'm currently analysing and it's causing me a headache. I'm hoping there is a simple solution to this that doesn't involve macros. Any help much appreciated Sample data: this is what i have in one column of data at present. each name beneath a port represents people working at that particular port. However, what i want is in the column along side this data for it to show the port name that each employee works at. what i have at present is as follows; cell a1 port:rotterdam cell a2 richard cell a3 david cell a4 paul cell a5 port:south africa cell a6 james cell a7 sam cell a8 keith cell a9 duncan cell a10 port:port talbot cell a11 simon cell a12 rachel what i'd like to see is a formula in column B to return the relevant port for each individual (shown below) It is a large document with over 5000 rows so a formula is a must if possible. column A column B Port: rotterdam Richard rotterdam David rotterdam Paul rotterdam Port: south africa james south africa sam south africa keith south africa duncan south africa Port: port talbot simon port talbot rachel port talbot Tag: Get rid of #VALUE, after multiplying Tag: 497622
  - 8
    - formula for selecting cells in one table from values in another I have a table that I created in a GIS of tax parcel numbers that are in a specific zoning district. I have another table that was created in my tax assessing program of all tax parcels in town with parcel location address, owner, mailing address, etc. I'm trying to write a formula that will match the individual lots in the GIS table and select only those in the assessment table that match and returns the parcel #, Location , Owner and mailing address. GIS table 101-005-000-000 105-002-000-000 201-220-000-000 Assessing table 101-001-000-000 221 Lark Street Joe Blow PO Box 111 Belmont, NH 03220 101-002-000-000 222 Lark Street Jane Cool 221 Lark St Belmont....... 101-003-000-000 223 Lark ...... ..... .... ... 220-099-001-000 10 Downing St John Major 2244 Beacon St Boston ..... Tag: Get rid of #VALUE, after multiplying Tag: 497620
  - 9
    - Not sure how to write the formula I am fairly new to writing formulas. The formula I always use is F2-J2/I2. Pretty simple, except when I2 has no data or is zero then I get the #DIV. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula. Tag: Get rid of #VALUE, after multiplying Tag: 497617
  - 10
    - select printer macro morning, i'm using Office 2007 on Vista, and i have the following event recorded in "thisworkbook" Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Purchase Order (Inventory)" Then Cancel = True Application.EnableEvents = False With ActiveSheet .PrintOut Call POInv End With Application.EnableEvents = True End If End Sub it works, but the problem is it sends the print job to the last used printer or the default printer. i want to beable to select the printer before it prints - maybe another macro gets called or something. any suggestions on getting the macro to select a printer. thank you, jat Tag: Get rid of #VALUE, after multiplying Tag: 497615
  - 11
    - Proper Formula is stuck and does not work Hello, I was wondering why my text formula of proper is not working. I have placed the formula =proper(f2) in column e2 All I get is the formula =proper(f2) and nothing else. It looks like it is stuck. When I try to copy down the whole column e has the same formula. I have tried formatting column F to text and General and the formula does not do anything. I have had this problem in the past with other formulas. Chris Tag: Get rid of #VALUE, after multiplying Tag: 497614
  - 12
    - If statement formulas I have two sheets in a workbook. One called Availability, and the other called schedule. The availability tells what time my employees are available to work and it look like this: A1 B1 C1 Name Start End Tom 6 AM 2 PM Jason 9 AM 5 PM John 1 PM 9 Pm Susan 8 AM 4 PM In the schedule sheet look similar to availability sheet accept it has one more column that will if I schedule an employee that will have a time conflict with availablity sheet. Look like this. A1 B1 C1 D1 E1 F1 Name Start End Jason 8AM 5 PM Not available before 9 am Susan 8 AM 6 PM Not Available after 4 pm John 6 Am 12 PM Only available from 1pm to 9 pm Tom 6 AM 2 PM Notice I added three more column. One tell a statement such as "Not Available Before", "Not Available After", and "Only Available From". The other two column tell if Column D has a statement such as "Not Available before", then column E specify the time that particular employee not available before what time. Similary, with "Not Available after" column E specify the time that particular employee not available after. Similary, if column D statement says the time is out of range or "Only Availbable From", specify the start time in E column and end time in F column. If time is within range then don't do anything or display blank. Notice the names of my employees in shedule sheet are not in order with the name of my employees in the availability sheet. I probably need vlookup formulas as well? What formulas do I put in cells of Column D, E, F the get the deserve effect from above? Thanks Tag: Get rid of #VALUE, after multiplying Tag: 497612
  - 13
    - look up and data extraction I am trying to verify that specific numbers are assigned to other specific locations (numbers or alpha characters). The numbers in question can be assigned up to 5 other numbers or alpha locations. Please see example: material assignment 001575-0393 0100 001575-0393 SIM 001681-0828 0100 001681-0828 0400 001681-0828 0401 001682-0828 0100 001682-0828 0400 001682-0828 0401 I want to check that 001575-0393 is assigned to 0100, SIM, and 0101. I would like to know when they are assigned to the required numbers, assigned to any other number not specified, or if the assigned number is not listed. In the example above, 001575-0393 is assigned to 0100 and SIM when it should be assigned to 0100, 0100, and 0400. I would like to display the assigned number and the locations that are incorrect. Any help would be greatly appreciated. Thank you, Robert Tag: Get rid of #VALUE, after multiplying Tag: 497608
  - 14
    - How do I stop the headings moving when I scroll down? What I want to do is set up a couple of my bohemoth spreadsheets so that when I scroll down to look at the values below the bottom of the sheet the headings don't disappear - basically I want to 'anchor' them somehow. Tag: Get rid of #VALUE, after multiplying Tag: 497606
  - 15
    - PLZ help me with MACROS Macros help I accidentally hit disable on my workbook then closed. Now when I try to open my workbook (that now has a xlms extention) I get This file is not a recognizable format, how do I get my work back to where it was? Tag: Get rid of #VALUE, after multiplying Tag: 497604
  - 16
    - Macros help I accidentally hit disable on my workbook then closed. Now when I try to open my workbook (that now has a xlms extention) I get This file is not a recognizable format, how do I get my work back to where it was? Tag: Get rid of #VALUE, after multiplying Tag: 497603
  - 17
    - sorting data from one sheet to another SHEET1 SHEET2 A B A B 1 JOBID MATERIAL 1 JOBID MATERIAL 2 8Job1 concrete 2 3 8Job2 drywall 3 4 8Job3 mason mix 4 5 8Job1 nails 5 6 8Job1 4x2 panels 6 7 7 8 8 #1) I need SHEET2 to gather & sort through data from sheet 1. I need to set it up so that when I enter a JOBID in SHEET2 A2, "ALL" materials from SHEET1 associated "ONLY" with the specified JOBID shows up in the Material column of SHEET2. #2) Data from SHEET1 will continuously be changing on a daily basis as new data (JOBID & MATERIAL) is added each day. I need to be able to set it up so that as data is entered in SHEET1, data is also being automatically updated in SHEET2. For example, if today I enter 8Job1 in SHEET2 A2; the words "concrete, nails, 4x2 panels" show in SHEET2 B2:4. Then, 5 additional materials with the 8Job1 are entered throughout the rest of the day. Tomorrow morning I would like to see those 5 new items in SHEET2. NOTE: both numbers and text will be used in data cells (i.e. 8Job1). NOTE: I am using EXCEL 2007. Is this possible? Can anyone help? Tag: Get rid of #VALUE, after multiplying Tag: 497602
  - 18
    - i want to learn MS OFFICE from micro soft from pakistan Tag: Get rid of #VALUE, after multiplying Tag: 497595
  - 19
    - copying cell links with fixed number in between In a column someone wants a reference to another cell. They want this for a large nr. of cells, with 15 in between =D15 =D30 =D45 =D60 and so on I can build a simple VBA module to get this done, but the whole in itself is variable (for some purposes 15, others 18, in another sheet it'll be a different column) and the user who needs to do this won't be able to change anything in the VBA stuff. They will also need this in more worksheets. I've checked 'paste special' but it doesn't seem to have any functionality for this. Any ideas? Tag: Get rid of #VALUE, after multiplying Tag: 497590
  - 20
    - Fresh pair of Eyes required Hi, with the help of some excellent feedback from the User Group I have created the following formula but I am not getting the expected results The formula works finre until I put the 2nd search for a particular word.. eg This works fine =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'! F2:F4557="C")+(TRIM('Grid data'! F2:F4557)=""))))))) And counts correctly, ignoring anything with SHINE in column D and only including, blanks, C and NS values. The following formula I am not getting the right results. It appears to be ignoring the search for "NON" in column E. =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'! F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'! F2:F4557)=""))))))) Paul Tag: Get rid of #VALUE, after multiplying Tag: 497586
  - 21
    - Sorting CF Color Hello and thanks ahead for your help. I have a row of data that is CF to turn red when true. Then I would like to sort this row to put uncolored cells to the bottom of the list. I have gone to Chip Pearsons sorting CF Colors Page and have tried ColorOfCF and ColorIndexOfCF and allways I get #NAME?. When I enter =ColorOfCF($A$1,FALSE) into theformula bar for B1, the Formula box pops up and shows all entries resuls correct (no errors) but at the bottom it shows "undefined" and the resuls show nothing (results= ), so when I click the OK Button the cell shows "#NAME?" I've placed the codes in my Sheet1 Code Box, this is the sheet I'm using to test these codes, Only A1 has a CF in it at this time.If I can get this to work I will enter data thru A10 and fill down B1 to B10 and there should be numbers in these B1:B10 cell if there is color in the corresponding cells. Right? What am I doing Wrong? Thanks again John Tag: Get rid of #VALUE, after multiplying Tag: 497580
  - 22
    - Button funtion maybe!! Im wondering if the following is at all possible in excel? I want to place a button on the worksheet that on enter (press) it would duplicate and insert a selected cell range (with formulas) at a set location below the selected cell range.? Tag: Get rid of #VALUE, after multiplying Tag: 497575
  - 23
    - Colour multiple cels using range.. I am aware of conditional formatting, but what I want to be able to do is colour multiple cells on a single row based on the 1st cell (being a date) equalling a date in another work sheet, as conditional formatting only allows the function to work on the worksheet that the function is placed in. Tag: Get rid of #VALUE, after multiplying Tag: 497574
  - 24
    - How to create primary/secon single click checkbox line autopopulat I want to create a checkbox type spreadsheet that will autofill line data to a total sheet. What do I use? Formulas? Which one, and how? Ex>>>Cell H63 will be filled in with the amount in F55 if I check G55. Then check a secondary, (G45), and H64 will will autofill with F45 F G 45 103 (X) checked second 55 87 (X) checked first 72 19 (X) checked last AKA...Applicant 87 (H63) first to populate Spouse 103 (H64) Second Child 19 (H65) next and so on Tag: Get rid of #VALUE, after multiplying Tag: 497573
  - 25
    - Excel copy and paste I did something dumb. When i copy and paste a formula, the correct formula copies but the original copied number is displayed. ie cell g4 (8) - cell g5 (4) = cell g6 is, g4-g5, displays 4.00 typical copy and paste ie cell h4 (25) - cell h5 (4) = cell h6 is, h4-h5, BUT displays 4.00 AGAIN !!! help please Tag: Get rid of #VALUE, after multiplying Tag: 497571

If my formula is trying to multiply one empty field with another empty field
that is fine.
But if I am trying to multiply an empty field with another empty field that
has a formula in it (specifically a VLOOKUP) I get a #VALUE error. How do I
get that to go away and be ignored?

Top

Re: Get rid of #VALUE, after multiplying by T

T
Sat Mar 15 19:24:59 CDT 2008

It sounds like the result of your VLOOKUP is a formula blank ("") which is a
TEXT value. So:

number * formula blank = #VALUE!

Let's assume the VLOOKUP is in cell A1 and you want to multiply that by cell
B1:

=N(A1)*B1

If A1 is a formula blank ("") the result will be 0.

--
Biff
Microsoft Excel MVP

"kbouquet" <kbouquet@discussions.microsoft.com> wrote in message
news:C0E89718-FE1F-4757-A144-C99CCA9FDD85@microsoft.com...
> If my formula is trying to multiply one empty field with another empty
> field
> that is fine.
> But if I am trying to multiply an empty field with another empty field
> that
> has a formula in it (specifically a VLOOKUP) I get a #VALUE error. How do
> I
> get that to go away and be ignored?
>

Top