Is it possible to set a cell 'blank' from a formula? i.e. rather than setting = 0 or setting to "" (empty string) In other words IF (<data valid>, <do calculation>, <clear content>) Thanks Peter -- Peter Aspey E-mail: replace 6 by p

0 |

2/7/2005 3:34:39 PM

Within a single column, how would I set a range to be just the cells that have data? I want to lock these cells using a change event, while leaving the remainder of the cells editable. I'm trying to set one range as the entire column, and a second range as cells with data, then using an intersect to lock the cells with data. This should allow for continuous locking of newly used cells. Maybe use SpecialCells(xlCellTypeBlanks) to find the empty cells and lock the column then unlock those -- HTH Bob Phillips "programmingrookie" <programmingrookie@discussions.micr...

My file have two columns A - Month B - Amount I will change the color to red of certain cells in column B Can I use the sumif function to total column B of those cells with same month and color red ? Many thanks eva cheng You can't sum by color (is there some criteria you could use, such as greater than x value?), but you can sum by month like this: =SUMPRODUCT(--(TEXT(A2:A100,"mmm")="Apr"),B2:B100) -- Best Regards, Luke M "eva cheng" <evacheng@discussions.microsoft.com> wrote in message news:769CE3C9-1043-4D16-B872-E80B07E47C73@m...

Excel 2007 I've got this big long formula: =IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1) +(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1) +(CODE(RIGHT(B2,1))-187)/4,B2) What I want to do is go through and change all of the A2 to k16, and change all of the b2 to k15. What is the easiest way to do that? Select at least two cells (click on this cell, and ctrl-click on an empty cell) Then use a couple of Edit|Replace's. JimS wrote: > > Excel 2007 > > I've got this b...

In order to save time it would be useful to create a cell reference that contained a variable. Does anyone know if this can be done and if so what the notation might be? The idea is this: If I normally want to reference a data set from D5:D21 but sometime the length of the data changes an it might be D5:D35 it would be helpful if I could enter 21 or 35 in cell A1 and then reference the data set as D5:D(A1) that way anytime I change the value in A1 my data set would be updated. Thanks in advance Tom I think you want to use =indirect(). A simple example: =SUM(INDIRECT("D5:D&...

In a table below, I am trying to "Count" the number of time a row begining with "H" has a number "1" iin it. I believe this has to be a "Nested" function but I am unsure. I will repeat this function for the leters A/U/S, and for the number 0/2 as well. H 1 1 0 2 0 H 2 2 1 0 0 A 1 1 1 1 1 U 0 0 U 2 1 1 S 0 H 1 2 1 2 1 A 2 2 2 1 1 U 1 1 1 1 2 Rick Hi Rick try the following for counting all H/1 in your range: =SUMPRODUCT((A1:A999="H")*(B1:H999=1)) assumption: Your range extends from A1...

How do I clear the print queue? I've tried "net stop spooler" and deleted all in /WINDOWS/System32/spool/PRINTERS, to no avail. The OS is XP Home SP3. The printer is HP psc 1200. "Herzl Regev" <HerzlRegev@discussions.microsoft.com> wrote in message news:03C416F4-BE1D-4CE5-A865-4F5D0FD3EC57@microsoft.com... > How do I clear the print queue? > I've tried "net stop spooler" and deleted all in > /WINDOWS/System32/spool/PRINTERS, to no avail. > > The OS is XP Home SP3. The printer is HP psc 1200. What happened when you...

I've been going through various postings and I haven't been able to find anything that helps me. I have a list of key numbers with names and registration numbers and I want to extract the registration number which is kept in a set of braces. Key No. 71 Jane Doe - [ PRIVATE ] However, I'm getting spaces, the last brace in the cell, how do I remove it all? so I don't get the following ( _ = space) : _PRIVATE_] Here is my formula: =MID(L12,FIND("[ ",L12)+1,FIND(" ]",L12)-1) I even tried to remove more spaces by doing this: =MID(L12,FIND("[ ",L12)...

Due to a malformed e-mail message or other malformed item in the information store, the Microsoft Exchange Information Store service may crash periodically. It happens when a staff try to move his mail from exchange mail box to his personal folders. Now I have to restart the services - Microsoft Exchange Information Store every 1 min. I attempt to clean his mail box (not delete) in the exchange server such that to fix this problem. Can you perform an "online backup" of the information store? If this is successful, then the problem is probably related to the structure of ...

This is what I want to do I have a cell A1 with the number 40 In another cell I want to use the number 40 to select a cell, something like: =B40+234 Where 40 in B40 is the number typed in A1. how do i do this please? Try this: =INDEX(B:B,A1)+234 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== <jon.berg@gmail.com> wrote in message news:1133883789.555648.81840@o13g2000cwo.googlegroups.com... This is what I want to do I have a cell A1 with ...

Hi all, Have the following code in my Workbook: Private Sub Worksheet_Activate() Dim X As Integer For X = 2 To ActiveWorkbook.Worksheets.Count Sheets(1).Cells(X + 8, 2).Value = "" Sheets(1).Cells(X + 8, 2).Value = ActiveWorkbook.Worksheets(X).Name Next End Sub And currently this results in the following from B10:B24 ... Jan04-Feb04 Dec03-Jan04 Nov03-Dec03 Oct03-Nov03 Sep03-Oct03 Aug03-Sep03 Jul03-Aug03 Jun03-Jul03 May03-Jun03 Apr03-May03 Mar03-Apr03 Feb03-Mar03 Jan03-Feb03 ...

Need to fill blank cells in a column with the data from the previous non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it in Excel try edit>fill>series (select one) Lotus's is simpler. >-----Original Message----- >Need to fill blank cells in a column with the data from the previous >non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it >in Excel > > >. > One way, assuming you mean you have data blank data blank or something like that and you want the cell with data to fill in the blanks below ...

Help - I'm struggling! Is it possible to switch the data in a particular cell to reflect the active cell as and when I move through a range in my spreadsheet? For example: If Cells: A1 = Apples A2 = Pears A3 = Oranges A4 = Lemons and my active cell is A1 then I would like B5 to = Apples. If I move focus so that the Active cell is now A3 I then I would like B5 to = Oranges and so on. Thanks in advance, Peter "Peter Davies" <peter@sedburypark.co.uk> wrote in news:#QJGurkqFHA.620@TK2MSFTNGP15.phx.gbl: > Help - I'm struggling! Is it possible to switch the...

Hi, In one of our office spreadsheets, everytime it is opened it asks do you want to update cells from another spreadsheet. I believe when this spreadsheet was set up, a work sheet was copied from another spreadsheet. I thought that all the references to that other spreadsheet had been removed. Is there anyway to identify (other than manually checking each cell) which cell has a formula linked to the old spreadsheet. Thanks in advance, George Download and install Bill Manville's FindLink add-in, which you can find at: http://www.bmsltd.ie/MVP/ In article <bYd1d.27315$Z14.90...

Hi When you use the open an other user folder, it could be a calendar, the persons name and folder is put into a list under the menu File/Open. I would like to clear that numbered list of folders, does anyone know how to do that? thanks, Rikke ...

Hello all. Usually when building a pivot table report I throw the data sets in the pivot table a few times to find the best way to show data. So I wonder - is there a way to quickly erase all the data sets so the pivot table is again empty as just being created (so I do not have to select each data set and remove it? Macro or some other trick? The closest I got to the answer was the idea to just create another pivot table from the original data=85but it would be so much easier to click a button and all the data is cleared from the pivot table. Thank you as always people! In Excel 2007 ther...

I want to insert a $ in column of cells. Use currency as the format, but I want the $ to stay in the cell so it can be used also when printed out. I hope that's clear? Thanks Frank Frank, The $ in a currency format should also get printed. It is a view of the data, but consistent on screen and print. -- HTH RP (remove nothere from the email address if mailing direct) "Frank" <stratster68@IHATESPAMworldnet.att.net> wrote in message news:%23lA$94VRFHA.3988@tk2msftngp13.phx.gbl... > I want to insert a $ in column of cells. Use currency as the format, but I >...

In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata formular. And when I drag or copy this cell, again it copies the formular. Can you help how to just get the value, then be able to drag to link next cell and its value? thanks. There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html JamesChoi wrote: > In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata > formular. > And when I drag or copy this cell, again it copies th...

I have a cell in a spreadsheet into which I type a date. When the pc clock shows that the date in the cell is more that 30 days old I want the cell to turn red. How would I do that. Basic to many of you I am sure, but to someone who doesn't use this side of Excel, virtually impossible ! (I'm using Excel 97 if that makes any difference - but I presume not) Thanks in advance Tobit LOL - My pleasure. Glad you got sorted, and thanks for the feedback. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP...

Hello, I have Office 2007 and all my excel formulas are in Portuguese. Can I change the formulas to English? Thanks, Miguel Hi shapper For a few dollars(25) you can buy a 2007 language pack http://office.microsoft.com/en-us/suites/FX102113661033.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "shapper" <mdmoura@gmail.com> wrote in message news:7563537d-e963-499b-afb9-5f726ac440a0@c36g2000prc.googlegroups.com... > Hello, > > I have Office 2007 and all my excel formulas are in Portuguese. > Can I change the formulas to English? > > Than...

I have colour coded a spreadsheet and would like to set a cell to recognise the couloured cells and add all these cells to a total Not sure if you can actually add colored cells. What is the content inside the cell? If there is a pattern, you can try a few things to get a sum. -- cparaske ------------------------------------------------------------------------ cparaske's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3793 View this thread: http://www.excelforum.com/showthread.php?threadid=477902 Nothing built in http://www.cpearson.com/excel/colors.htm ...

any one have any scripts to clear 1099 history by vendor or vendor class? Current Year UPDATE PM00201 SET TEN99AYTD = 0 WHERE VENDORID = 'xxxxxxxx' Life to Date UPDATE PM00201 SET TEN99ALIF = 0 WHERE VENDORID = 'xxxxxxxx' UPDATE PM00202 SET TEN99ALIF = 0 WHERE VENDORID = 'xxxxxxxx' Last Year UPDATE PM00201 SET TEN99ALYR = 0 WHERE VENDORID = 'xxxxxxxx' PM00201 is the PM Vendor Master Summary, PM00202 is the PM Vendor Master Period Summary. Life to Date amounts are stored in both tables. Current Year and Last Ye...

Looking at Contexture, I think I'm close, but.... Have a header row of 25 items and in A1 I want to put a list with 4 selections Depending on what item is selected from the list I want to highlight certain columns headings. ie; If I select Text A it will highlight columns D,E,F, J,K, N, P; If I select Text C it will highlight columns B,C, D,F, J,K, N Basically, if it's this then complete these highlighted columns, if its something else, complete those highlighted columns, etc. Probably simple and I'm just too tired to think tonight. Thanks for any help. -- John You can mark th...

I want to create a seed starting chart in excel, but don't know if I can create a formula which would allow me to use dates. I want to have a column where I enter the number of weeks from the first frost free date, and then have Excel calculate what the seed starting date would be. one way: A1 <last ff date> A2 # weeks before last ff date to start A3: =A1-7*A2 format A3 as a date. In article <F2BC24C1-7FED-4FF4-BF14-471A8D120C56@microsoft.com>, Justlearning <Justlearning@discussions.microsoft.com> wrote: > I want to create a seed starting chart...

I'm at a new job. I got the fired guy's work station. How do I clear everything out of MS Outlook and reset it for my own use? Start with a new mail profile; Control Panel-> Mail-> Show Profiles For details see; http://www.howto-outlook.com/faq/newprofile.htm Starting with a new user account would also clear the rest of his settings for the entire Windows environment and configured applications. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http:/...

I am creating a spread sheet that with each Monday in a calendar year, I am needing to multiply 8 weeks, 13 weeks, and 26 weeks out. Please help =if(weekday(Today()) = 2, date+(8*7),"") format as date Change 8 to 13 and 26 for your other dates. if you need it to show these dates for the remainder of the week =Today()-(weekday(Today())-2)+(8*7) format as date. Again, use this formula and change 8 to 13, then 26 for the other dates. the date would actually advance a week on Sunday. -- Regards, Tom Ogilvy "jamie" <tscharbrough@quest-grp.com> wrote in message...