Protected Workbook, no comments?

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
    - Create macro with button in excel spreadsheet I'm trying to create a macro in an excel workbook and have a button so that when a customer completes the form, they hit the macro button and it creates an EMail and populates the subject line. The folks who use the form download it from my company's internal portal; all users are internal to the company. Note, I have a rule set up in Outlook so that these EMails automatically route to another folder so they don't get "lost" in my general inbox. Users send the excel attachment to me OK, but the subject line is usually missed, even though I have mistake-proofed it as best I can. Tag: Protected Workbook, no comments? Tag: 518801
  - 2
    - Excel Function - Date evaluation I want another cell to evaluate if there is a date (of any kind) in another cell. If there is then I want the formula to place the date minus 30 days. If there's not, then I want it to say "NA". Ex: Cell A4 can have, "OK", "Overdue", or physically input a date. Cell A5 will be A4-30, if there is a date. If there is not then A5 will read "NA". Thanks Tag: Protected Workbook, no comments? Tag: 518796
  - 3
    - Help with combining lists I'm pretty new at this, but I'm having trouble with something. I have 2 lists that each have item numbers in them. I'm trying to take out all the item numbers that appear in both lists so that what I'm left with is a few numbers that are only in one list or the other. I can't figure out how to do this because all the "remove duplicate" tools and formulas leave one of the 2 duplicates. I need to remove both of them. Does anyone have an idea of how to do this? The only way I can figure out is to paste both of the lists in one file, do Conditional Formatting for the duplicates, and then pull out the ones that don't have the special formatting. There has to be an easier way! Thank you in advance! Tag: Protected Workbook, no comments? Tag: 518795
  - 4
    - if statement syntax help I have been reading a lot about conditional formatting. I have cells in D2 that I want highlighted in yellow if g2>5 and i2>69%. If g2 and i2 do not both meet the criteria, I don't want anything to happen to d2. I'm assuming I can use conditional formating for that, but how do I write it? Tag: Protected Workbook, no comments? Tag: 518785
  - 5
    - Generate list Im trying to simplify shopping lists in my home. I have a basic list that we can complete weekly. how can i generate from this a list that will only include items and stores that have to be shopped at? I am primarily looking for a list that the column headers will be source and the row headers just the items that have a value in the quantity column with the quantity showing under the proper column. So that a list that starts like this: Item Source Quantity Alaskan Salmon Trader Joes Almond Milk Whole Foods 1 Apple Cider Smart & Final 3 Apple Juice Smart & Final 7 Apples Ralphs will generate a list that looks like this: Whole Foods Smart & Final Almond Milk 1 Apple Cider 3 Apple Juice 7 Thank you. Tag: Protected Workbook, no comments? Tag: 518784
  - 6
    - Pivot Table data I'm not sure if this is the correct forum for this but here goes. I have a pivot table that draws from several pages. when I click on the values to find out what makes the values I get a second sheet that has: ROW Column Value Page1 Item number Cumber title a date Item3 (or Item1, or Item2...you get the idea) How do I change the "Item#" to the actual sheet it came from. if I need to explain more please let me know. Thank you Tag: Protected Workbook, no comments? Tag: 518778
  - 7
    - Conditional Sum of cells Hi I have data in Cell A1,A2, C1,C2 E1,E2 A C E 10 15 12 1 0 1 I WANT THE SUM OF CELLS A1 & C1 ONLY IF THE VALUE IN A2 AND C2 IS NOT ZERO THIS IS AN EXAMPLE. ACTUALLY THE NUMBER OF CELLS THAT I NEED SUM OF ARE MORE THAN 20 IN THE SHEET. BUT THEY ARE NOT IN ORDER Tag: Protected Workbook, no comments? Tag: 518776
  - 8
    - Separating Strings I'm sure this will come as a really simple question to the rest of you, but I have a column with names in it; I want to divide them into first and last names. How can I do this? I started with finding the space in between the first and last names, like this: SEARCH(" ",A2) Then when I got the number of the space, I started a conditional statement: IF(SEARCH(" ",A2)>0, . . . .) But that's where I lose my line of thinking.So I'm telling it that if it finds a space, then I want it to return everything BEFORE the space to one column and then I need another function to return everything AFTER the space in another column. TIA for your help! Tag: Protected Workbook, no comments? Tag: 518775
  - 9
    - change formula in a shared worksheet without losing change history Hi Rick, Thank you for calling my attention for the lack of details with my question. My question is: I posted a shared worksheet in a shared drive that others could update with their changes and corrections. However, getting back to the shared worksheet, I realized I had to change an array formula in one of the tabs. When I tried to change the formula, I receive a message saying that it is one of the things that I am not allowed to do without losing the change history ( records of people's ID, time & date they went to the file and the changes they made). One way I thought of doing is to save the old file in a place others do not have access to, & re-create the file with a new file name. That will allow me now to change my formulas. However, I will lose the change history in the new file and I do not want that. I know that all the changes are there but I want to be able to see the change history just in the one new file without having to bring out the previous file. I hope this clears my dilemma. Thank you Rick for your suggestion. Tag: Protected Workbook, no comments? Tag: 518773
  - 10
    - Printing with "" I have a chart and it uses a forumla to retrieve data, and if no data is available it sets the cell to "". My problem is that when I try to print the worksheet, it prints an extra blank page (because of all the ""). My forumals look something like: =if(DATA,GET THE DATA,"") Is there a way to prevent an extra page from being printed without using Print Area? Tag: Protected Workbook, no comments? Tag: 518770
  - 11
    - Expansion of COUNT if Question I have two large columns of data, one with numbers and the other is verbal. I need to write a code in a specific cell that will SUM of a group of those numbers if the adjacent column meets a certain Criteria. For Example Column 1 Column 2 A 2 B 5 C 7 A 8 A 4 B 3 C 2 So i need the code in the cell to Sum up all the numbers in column 2, that have a A in column 1. How can i do this?? Thanks in Advance for you help!!! -Adam Tag: Protected Workbook, no comments? Tag: 518766
  - 12
    - Adding duplicates... I need help figuring out a formula to add duplicates as follows: I have 3 months of figures with duplicate names on each month. I need to add these amounts to a quarterly sheet. I want the names on the quarterly sheet to show once, and with the totals for the 3 months. I'm hoping to not also have to use a formula indicating a name in there, if that makes sense: i.e January; Janet 5 Carol 10 February: Bill 4 Janet 8 March: Jon 11 Carol 14 Quarterly: Janet 13 Carol 24 Bill 4 Jon 11 Tag: Protected Workbook, no comments? Tag: 518764
  - 13
    - Insert Graphics File into Worksheet via Formula <<Previously posted in m.p.e.charting under "Graphics Filename via Formula">> I am trying to programmatically generate a 9-panel chart similar to those you see in stock prospectuses, with the horizontal displaying "Investment Style" and the vertical displaying "Market Cap". Each of the 9 panels within the grid represents a different combination of the horizontal vs. vertical values. I have a very kludgy solution using conditional formatting on a grid of 9 individual cells to turn one of them on and leave the rest neutral, but this solution is hard to maintain and expand for more source data. I would like to take nine graphic files, one for each possible highlighted square position, and use a formula to derive a value that would be used to display a graphic with that filename. For instance, for "Growth" and "Large", if the formula produced a result of "GL", the file named "GL.jpg" (or GIF or PNG) would appear in a certain cell (each of the nine graphic files would be identical in dimensions). If the source data changes, the graphic would automagically be changed, too. If two rows of data produce the same combination of horizontal vs. vertical values, they would each display the same graphic file. How do I do this in Excel (2003, preferably)? advTHANKSance! Tag: Protected Workbook, no comments? Tag: 518753
  - 14
    - correct formula (cell1)1254-(cell2)987=267 cell2)267/(cell1)1254= answer I am looking for one formula to pick up this answer please help. so 1254 minus 987 equals 267, 267 divide by 1254 = answer % This is all revenue and budget based 987 is the actual 1254 is the budget and i am trying to add two colums for growth vs budget and growth vs prior year. Tag: Protected Workbook, no comments? Tag: 518751
  - 15
    - calculate percentage of numbers <= zero in a given range I need to calculate the percentage of numbers, within a range of cells, that are equal to zero or less than zero. Simple example: numbers are 8, -1, 8, 8, 0, 0 The percentage of numbers that are equal to zero or less than zero would be 3/6 or 50% How can I do this in Excel? Tag: Protected Workbook, no comments? Tag: 518740
  - 16
    - how to change formula in shared sheet without loss of change histo Tag: Protected Workbook, no comments? Tag: 518734
  - 17
    - Vlookup on Dates I have a table containg start dates in column a and end dates in column b. The start date for the second (and subsequent) record is the end date for the first record plus one day e.g. if the end date was 31/12/2007 then the next row would show a start date of 01/01/2008. How can I get a vlookup function to correctly identify the row in which the target date falls between a start date and end date (both on the same row). Once the correct row has been selected the vlookup will need to lookup the contents of a cell to the right of the indexing columns. I have tried this by only using the end date and find that the function selects the row before the row containing the relevent start and end dates. Thanks for any help/advise. Tag: Protected Workbook, no comments? Tag: 518729
  - 18
    - Extracting data from Outlook to Excel using VBA Hi, I need to get data from messages in Outlook to fill fields within Excel worksheet using VBA. Can anyone suggest me a website or ebook where i can obtain information? Thanks in advance HBruno Tag: Protected Workbook, no comments? Tag: 518728
  - 19
    - IF I need to create a formula for the following scenario ... any help would be appreciated! If value in cell is 3, then multiply another cell by .376, If 4, multiply by .668, and so on. For instance: A1 = 3 B1 = 4.5 Formula Cell C1 should return 1.69 OR A1 = 4 B1 = 4.5 Formula Cell C1 should return 3.01 Can this be done? Tag: Protected Workbook, no comments? Tag: 518721
  - 20
    - Annual Leave automatic calculation Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C 1 Name Marcel Fouad Breidy 2 Staff Number 50005 3 Position ST1 4 Starting Date 01-May-03 5 Total Working Days =+(NOW()-C7)/30/12 =5.31 years D 7 Annual Leave pr year Days Taken Days Remaining 8 17 61=D11 19.67=A10-D11 9 Total Days allowed 10 =5*15+(C8-5)*17 11 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C7>10*365,19,IF(TODAY()-C7>5*365,17,IF(TODAY()- C7>0.25*365,15))) What i want is an automatic calculation for the total days allowed instead of mannually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? Thank you in advance Tia Tag: Protected Workbook, no comments? Tag: 518720
  - 21
    - How to Create Macro to Increase Cell Value to one and Print Worksheet We are trying to automate the printing of a worksheet. In sheet "Jobs" of the workbook it has consecutive numbers in column A (1, 2,3 ...) and is variable. We would like to automate the insertion of each consecutive number from this column A into cell C6 of the Summary sheet, and print out the Summary sheet, until the last number is reached in column A. Example: Column A has numbers 1-32. The macro will copy 32 times (numbers 1 through 32) into cell C6 and each time print the Summary sheet out. (by copying the number into C6 it will activate other formulas...) Thanks so much for anyone who can assist! Tag: Protected Workbook, no comments? Tag: 518719
  - 22
    - how do I set up or adjust my column chart? I am creating a shipping schedule template and have 9 weeks that I ship products. I would like to show each customer how many units they receive per week. My template is set up for 9 ship weeks. Here is an example for 3 shipments. 0 means no product is being shipped. Row 1: Oct 6, Oct 13, Oct 20, 0,0,0,0,0,0 (total 9 weeks) Row 2: 233, 545, 102, 0,0,0,0,0,0 (number of units shipped that week) Now most of my customers only receive 2 or 3 shipments and only a few take as many a 8 or 9 shipments. How can I set up a column chart so that I only see 3 columns when I ship 3 weeks and not all 9 weeks where only the first 3 weeks show a value and the other weeks are all 0? Thanks. Tag: Protected Workbook, no comments? Tag: 518718
  - 23
    - Pivot Table Question Hi. When I create a pivot table, the last row before the "Grand Total" row is titled "(blank)." Even if there are no blank items within the pivot table, Excel still produces a "(blank)" row in the pivot table. Is there any way to getting rid of or automatically defaulting Excel to not produce the "(blank)" row? (Without, of course, hiding a column.) Thank you. Tag: Protected Workbook, no comments? Tag: 518712
  - 24
    - MCONCAT Hi All, I have a list of hand typed subjects in column B2 thru B20 I use MCONCAT to group them into cell B1 I now have the need to bring in the subjects via VLOOKUP. Obviously, MCONCAT will not group formulas. Any suggestions? Thanks for your help in advance. Tag: Protected Workbook, no comments? Tag: 518711
  - 25
    - Use tab name in Vlook Up I have a formula that looks up data in a sheet using Vlookup, every week the formula changes as the week number changes. The tab to look for data has a tab name of wk## and thus the forumla for week 28 looks like this =VLOOKUP(O2,wk28!A4:L3000,12,FALSE) How can I substitue the wk28 tab number in the formual for a cell reference that contains the text "wk28" ? This will save me modifying forumlas on weekly basis. Thanks Tag: Protected Workbook, no comments? Tag: 518707
- Next
  - 1
    - Entering changing months with a cell Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF(D2:AH13<>"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? Tag: Protected Workbook, no comments? Tag: 518706
  - 2
    - I have year based data want to convert into weekly basis How can I collate daily based data to weekly data eg : 21st july 08 - 25th july 08. Tag: Protected Workbook, no comments? Tag: 518704
  - 3
    - why do I lose cell format in a pivot table? i have created a pivot table to pull data through from a worksheet. In the worksheet I have some cells with blue text and some with red text to signify two catagories. When the information is displayed in the pivot table all the text is black. Is there a way to make sure the pivot table data corresponds to the colours in the worksheet? Thanks Tag: Protected Workbook, no comments? Tag: 518702
  - 4
    - Replace mutiply sign * I need to replace the * sign with something else. If I replace * with say /, not only * sign but everything will be replace. Anyone knows how it can be done? Thanks. Tag: Protected Workbook, no comments? Tag: 518700
  - 5
    - Adding a sort to a working query asks for a parameter value The following query works fine : SELECT [Base Data].[Scale Max] AS Old, [Base Data].[New Scale Max FTE] AS New, [New]-[Old] AS Diff FROM [Base Data]; However, if I add a descending sort to the last column and then run the query, I get a window opening asking me to "Enter a Parameter Value" for New. Can, SKS give me an idea as to how to resolve the problem. The query that causes the parameter box to open is below Tx in advance Rob SELECT [Base Data].[Scale Max] AS Old, [Base Data].[New Scale Max FTE] AS New, [New]-[Old] AS Diff FROM [Base Data] ORDER BY [New]-[Old] DESC; Tag: Protected Workbook, no comments? Tag: 518698
  - 6
    - sumproduct_zafar I have two diff things in two diff column and rage of columns are 30 I always calculate manually by using +. Kindly advise me any easy formula and mention the site of temples to look the flow of application Examples Total Description Pack Kgs Pack kg Pack Kg A123 2 400 1 200 ? ? B123 1 200 3 600 ? ? C123 5 1000 2 400 ? ? D123 1 200 2 400 ? ? Regards Tag: Protected Workbook, no comments? Tag: 518697
  - 7
    - print range is gray and i cannot change I have a vista version spreadsheet and I want to change print area. However the "print area" tab is gray and does not respond ..as are a few other tabs . How do I clear this to allow changes? Tag: Protected Workbook, no comments? Tag: 518696
  - 8
    - Date Calculation in Conditional Formatting I have a column of dates and if a date is 6 month or less in the future of the current month TODAY(), I need to have the cell red. I know this can be accomplished through conditional formatting but need some guidance on the formula. Thanks -- JPS Tag: Protected Workbook, no comments? Tag: 518691
  - 9
    - Removing Digit Separator I've string of digits like this 111/2222/33333. How can i change it to 111222233333? -- Daze Tag: Protected Workbook, no comments? Tag: 518690
  - 10
    - Excel filtering of split panes I have a huge sheet containing product model information. I often want to view the same data in a split view but want to filter the top pane on one model, and thee bottom pane filtered on another model. How can I do this? Tag: Protected Workbook, no comments? Tag: 518680
  - 11
    - unhiding formulas I have a column with 5 rows and the formula is invisible except for the toolbar but I forgot how I hide that. Under the protect tab it is unchecked for hidden or locked. How can I unhide my formula? Tag: Protected Workbook, no comments? Tag: 518678
  - 12
    - Concatenate a formula ="'C:\Accounting\Files\[" &H5& ".xls]Sheet1'!$I$6" Will return this in the cell. C:\Accounting\Files\[Accounting1.xls]Sheet1'!$I$6 But not the value of the cell I6 which is what I want. Thank you, Steven Tag: Protected Workbook, no comments? Tag: 518674
  - 13
    - certain time I have to compute an extra night pay rate for those who work between 22:00 to 06:00 the following day. Can this information be taken from the Start Time and Ending Time? And is the a formula that will look between these hours only> Tag: Protected Workbook, no comments? Tag: 518667
  - 14
    - format a right justified zero filled field to a prn file I need to save the excel file to a PRN file. One of the fields, according to the spec, is a numeric filed (check amount), right aligned and zero filled for total 10 characters in length. (0000010050 for $100.50) Would you have an advice on the formatting? Tag: Protected Workbook, no comments? Tag: 518666
  - 15
    - Collapse/Expand Buttons in a Pivot Table I have a pivot table with line items that only show the "collapse" button because they do not contain any information to show the "expand" button. Does anyone know why the "collapse" button appears when there is no data to expand or collapse? Can this "collapse" button be turned off without turning off those other items in the Pivot Table that do need the "expand/collapse" buttons showing? The Show/Hide Buttons under Options Ribbon is a toggle that turns all of them off or on so that doesn't work for this purpose. Tag: Protected Workbook, no comments? Tag: 518665
  - 16
    - Multiples conditions in an array I have a table with 4 columns. I need to get the sum of column D if certain conditions are present in each of the 3 columns. I want the sum of column D if Column A = 6000, Column C = North and Column B = ABC or DEF or MNO When I use the formula below, the result I am getting is 3500 which is equal to all the "North". I need the sum to only be 2500. Is the formula below correct or is there a more appropriate one to use? Thank you ! SUMPRODUCT((OR(B1:B24="ABC",B1:B24="DEF",B1:B24="MNO"))*(A1:A24=6000)*(C1:C24="NORTH"),D1:D24) A B C D 1 6000 ABC NORTH 1000 2 6000 DEF NORTH 500 3 6000 GHI EAST 1000 4 6000 JKL WEST 500 5 6000 MNO NORTH 1000 6 6000 PQR SOUTH 500 7 6000 STU NORTH 1000 8 6000 VWX WEST 500 9 6100 ABC NORTH 1000 10 6100 DEF SOUTH 500 11 6100 GHI EAST 1000 12 6100 JKL WEST 500 13 6100 MNO NORTH 1000 14 6100 PQR SOUTH 500 15 6100 STU EAST 1000 16 6100 VWX WEST 500 17 6200 ABC NORTH 1000 18 6200 DEF SOUTH 500 19 6200 GHI EAST 1000 20 6200 JKL WEST 500 21 6200 MNO NORTH 1000 22 6200 PQR SOUTH 500 23 6200 STU EAST 1000 24 6200 VWX WEST 500 Tag: Protected Workbook, no comments? Tag: 518661
  - 17
    - COLUMN & ROW EQUAL How do I get a row and a column to add up in the same cell, if it's correct give the answer, if it's wrong give an error message. Tag: Protected Workbook, no comments? Tag: 518658
  - 18
    - Question about Comparing Data I have identical worksheets that I create every week. The only thing I change is the backup data (weekly data). Then I open up last weeks worksheet and compare the two. Is there an easy way to compare and display the differences between two identical worksheets? Where it would say this week and last week we have a difference of 2k? And automatically pull in that information? Tag: Protected Workbook, no comments? Tag: 518655
  - 19
    - month text in calculation I have a drop down list in 'A1' with all the months spelled out. I want to use the selcted value, of that cell, in a formula. A1="august" in 'E2, i want to perform an IF function. If A1 is > or = july, return A2*B2, if not return A2*C2. Thanks Tag: Protected Workbook, no comments? Tag: 518644
  - 20
    - Combine 6 Worksheets to 1 Worksheet with total amounts I have 6 worksheets from Jan, Feb, Ma,r Apr, May, Jun that have different information on each worksheets. And would like to have another worksheet that would combine all the different codes (over @120 lines) with the appropriate dollar amount for each city. For Example Jan Code Atlanta Chicago Detroit 300 - Bill Expense 359.23 234.23 1132.32 301 - Process Fee 65.00 45.00 50.00 400 - Bill Expense 4560.00 333.22 232.32 Feb Code Atlanta Chicago Detroit 300 - Bill Expense 359.23 234.23 1132.32 301 - Process Fee 65.00 45.00 50.00 302 - Sales 3333 4444 5555 400 - Bill Expense 4560.00 333.22 232.32 and so on Total Code Atlanta Chicago Detroit 300 - Bill Expense 359.23 234.23 1132.32 301 - Process Fee 65.00 45.00 50.00 302 - Sales 3333 4444 5555 400 - Bill Expense 4560.00 333.22 232.32 Tag: Protected Workbook, no comments? Tag: 518641
  - 21
    - Changing worksheet reference in a VLOOKUP Using Excel 2007 and XP Pro SP2. I am using a VLOOKUP formula which is working fine, however I have about 20 worksheets within my workbook each containing unique values (same data ranges however). Is it possible to change the worksheet reference in the example below to be a value in a column? In other words if I have columns that represent each worksheet, can I place a cell reference in that column to call the respective worksheet? I am looking to formulaically change the value '220' so if I copy across it will pick up the next worksheet value of '221', '222', '223', etc. This way I can copy that same VLOOKUP command across a number of columns and it will automatically grab the data from that worksheet. This formula would reside in a separate worksheet in the same excel file. =VLOOKUP($A8,'220'!$B$8:$C$1274,1) So if I copy across the function would look like this for each new column: Cell A1=VLOOKUP($A8,'221'!$B$8:$C$1274,1) Cell B2=VLOOKUP($A8,'222'!$B$8:$C$1274,1) Cell C2=VLOOKUP($A8,'223'!$B$8:$C$1274,1) This would go on for about 30 columns etc. Thanks Mark Tag: Protected Workbook, no comments? Tag: 518635
  - 22
    - I think I know how to ask this sumproduct question now: This is a sumproduct formula I have worked out to count the number of recordable events that occurred within a date range. =SUMPRODUCT((Data!A$2:A$2550>=ListValues!A2)*(Data!A$2:A$2550<ListValues!B2)*(Data!H$2:H$2550="Recordable")) Now what I am trying to do is capture the cost of these recordable events (adding another array to sumproduct). These costs are located in cells Data!K$2:K$2550. I have not been able to get this $ total by using sumproduct. Somehow I keep screwing up. The only way I have been able to get this total is by using conditional sum- but by doing this I am not able to "pull" these calculations to adjacent cells- and I'm summarizing 20 years of data, so with each calculation only giving me a month-total, I could be entering the next 3 days if I don't figure this out. So here's the conditional-sum formula that gives the total- but that I have to enter each time: =SUM(IF(Data!$A$2:$A$2500>=ListValues!A2,IF(Data!$H$2:$H$2500=A19,IF(Data!$A$2:$A$2500<ListValues!B2,Data!$K$2:$K$2500,0),0),0)) Is it possible to get to this total with SumProduct? Tag: Protected Workbook, no comments? Tag: 518628
  - 23
    - Formulae stop working Hi I created a worksheet with some cells which work out network days etc from some date columns everything working fine. I sent the spreadsheet to a colleague and when he opened it it looked fine to. However when he copied data into the reference section all the formula stop working and return #Value#. As far as I can tell the formulae are correct. Retyping them has no effect. The only difference I can see is he is running excel 2003 sp3 whilst I created the workbook in exel 2003 sp2. I get the same problem whenever I put new data into the spreadsheet if the machine is running sp3. Is this a known issue and is there a workaround or am I goosed on this one? RD Tag: Protected Workbook, no comments? Tag: 518623
  - 24
    - IF function, nested with 2 source cells? Hi, This may be simple, but I'm trying to assign a % to a sales item, but I ma having issues: The value of cell C33 is 30%, the value of C10 is 46% If I use the formula =IF(C22>0,C33,C10), provided that the value of C22 is greater than zero, the result is C33. That all works fine. If I use the formula =IF(C29>C25,C10,C33) in the next cell, I can assign those %s according to the value in Cell C25. That works fine on its own. How do I combine them so that if C22 is greater than zero the result will be 30%, less than zero 46% and if C29 is greater than the value in C25 the result will be 46%, otherwise 30%? Thanks for your help Tag: Protected Workbook, no comments? Tag: 518620
  - 25
    - personal hrs is accrued @ 4 hrs./month, need formula to calculate Hi, i'm trying to track how many hours i have left to use at the end of each month, when i use them...is this something that can be done with a formula. we accrue only 4 hours per month. Tag: Protected Workbook, no comments? Tag: 518616

I have cells unlocked in a protected workbook, those using the unlocked cells
need to be able to add comments but it is not an option. How do I allow
comments?

Thanks!

Top

RE: Protected Workbook, no comments? by johnc

johnc
Wed Jul 23 14:40:01 CDT 2008

When you protect the sheet, be sure to put a checkmark next to Edit Objects,
then they should be able to add comments.
--
John C

"Kat" wrote:

> I have cells unlocked in a protected workbook, those using the unlocked cells
> need to be able to add comments but it is not an option. How do I allow
> comments?
>
> Thanks!

Top