Peo, It works like a champ. Thanks everyone for the help. Darrell (Lankchevy)

I have a spreadsheet with columns, first name, last name, home phone, business phone and cell phone. When I don't have phone information in any of the three columns I would like that column to be hidden or deleted. Can I do this in some automated fashion? Thanks. Michael try adding a column in that column put something like =if(counta(b2-d2)>0,"",1) and use autofilter to hide the 1s "mlkpied" wrote: > I have a spreadsheet with columns, first name, last name, home phone, > business phone and cell phone. When I don't have phone information in any of ...

I have created drop-down cells (type of business) for my client contact list but when I sort that list alphabetically or by last contacted date the drop-down cells don't sort with it. How can I solve that issue? Thanks for the responses! Are these Data Validation dropdown lists? Where is the list range located? i.e. =$A$2:$A$20 If inside your sort area the lists should change to whatever is in A2:A20 after the sort. Tested in 2003 and 2007 Gord Dibben MS Excel MVP On Tue, 26 Jan 2010 13:19:01 -0800, Lisa in Victoria <Lisa in Victoria@discussions.micros...

Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

In Column B I have certain values which start with GB2 e.g GB2-02210. I would to like to copy these values and paste it in another cell. I would like to do this with only values which start with GB2. Many thanks -- Message posted via http://www.officekb.com I would use Data>Filter>Autofilter and use custom>begins with... -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "mohd21uk via OfficeKB.com" <u20517@uwe> wrote in message news:5ede27c5beb13@uwe... > In Column B I have certain values ...

Hi all, How do I make all the existing formulas in a worksheet appear on it, instead of the resulting values? I opened a worksheet that have looks this way, but I don't know how it was done. Thanks in advance, Alejandro I found it out! tools/options/view/formulas Thx! -- Alejandro Caballero Aste Ministerio de Econom�a y Finanzas Direcci�n General de Asuntos de Econom�a Internacional, Competencia e Inversi�n Privada Tlf 311-5930 Axo 3536 ecaballero@mef.gob.pe "Alejandro Caballero Aste" <ecaballero@mef.gob.pe> wrote in message news:%23qJrSVgVHHA.4796@TK2MSFTNGP05...

I have a fairly large spreadsheet that are sorted based on a file # (ie: E0800100, E0800101). The spreadsheet is setup to where each entry is on an individual row as seen below: A B C E0800100 Review.... 1.0 (hr) E0800100 Review.... 2.0 E0800101 Review.... 1.5 E0800102 Review.... .5 I am trying to organize the spreadsheet so that there is only one row per file number and the Descriptions (B) and Time (C) extend along the columns of that row. A. B....

The following is the pattern of the data I have. I need to know which is the last row which has the data A1 - AAA A2- BBB A3 - <blank> A4 - CCC A5 - DDD A6 - <Blank> A7 - <Blank> A8 - <Blank> A9 - EEE. In the above example I want to get the row number as 9 as the last row which has the data. Can this be acheived by some formula. I tried COUNTA, but it does not serve my purpose. Please help. Thanks Anand ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Excel...

Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1 and then look for the next empty row on sheet 2 and paste it in range ("A27:L27") . I would also like the macro to insert a new blank row (or insert the copied row) for the purpose of shifting existing SUM functions on sheet 2 down. I would like those functions to be right below the copied/pasted cells every time the macro is executed. Thanks for any help - Jim A You don't Mention What column you want to sum This code will copy and paste to the fist row and then sum column D Sub Cop...

I work a lot with excel and I know that the row limit is 65,536, but I need more than that. Is there any way to make the number of rows infinite or at least to give me a certain amount of more rows. If there is please let me know. Thank you. -- Please help Molly, that is all there is, per sheet. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Molly" <molly_carols@yahoo.com> wrote in message news:FD2C7921-E249-4...

Hi Aladin, Thank you for assistance and Formulas. Tin� Aladin Akyurek Wrote: > What follows is a bit heavy... > > In B5 enter & copy down: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET($A$5:A5,ROW($A$5:A5)-MIN(ROW($A$5:A5)),,1)),--($A$5:A5=A5)) > > In A3 enter: > > =IF(SUBTOTAL(3,A5:A16)<>COUNTA(A5:A16),MAX(IF(SUBTOTAL(3,OFFSET(A5:A16,ROW(A5:A16)-MIN(ROW(A5:A16)),,1)),B5:B16)),"") > > which must be confirmed with control+shift+enter instead of just with > enter. > > Note 1. I used a bit larger range than your sample range for testing...

Hello, I am trying to write a sum if array formula that will look up against multiple criteria and then finally compare to an array...here is an example of what I wrote: {=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Sheet2!$C$12:$C$10000=(Lots!$H$6:$H$18),Sheet2!$K$12:$K$10000,0),0))} The formula works fine if I take out the $H$18, but as soon as I try to match that, it will not work... So to clarify what I am trying to do: Sheet 2 contains my data: Column A is weeks - Column K is Sales - (what I want to sum) Column C is SKU ID Sheet 3 is the Form I am summing to. Cel...

I need to copy an ActiveX control approximately 200 times in 1 worksheet (not form). The LinkedCell property value does not change when I copy and paste - so I end up with 200 controls pointing to the same cell. This is a pain to edit. The control is invoked with the following syntax =EMBED("Forms.ScrollBar.1",""). It "smells like I should be able to expose that cell address so a copy might work but I can't find any reference to the EMBED on line or in 6 books. Any help or redirection would be much appreciated. ...

What can be used instead of "=MOD(ROW(),2)=1" that accommodates filtering, pls? The above gives clumps of identically-coloured rows, dependent on the particular filter criteria used. Thank you! :oD As long as there are no empty cells within the filtered list: Assume A1:B1 is the header row. A2:B10 is the data Select the range A2:B10 Conditional Formatting Formula Is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:%23wKPl8LmHHA.4768@TK2MSFTNGP05.phx.gbl... > What can be used instead of "=MOD(ROW...

My default Excel template has the top row formatted as a header, with bold text and a dark bottom border to distinguish it from the rows below. So if I insert a row at row 2, the new row is formatted like the header. Then I have to select the new row and remove the bold font, remove the dark border, select the header row and reapply the bottom border. Is there a way I can change the insert default so that it takes it's formatting from the row below instead of the row above. Or better yet, so that it has no formatting at all? Hi AFAIK you can't change this behaviour -- Regards Fra...

I am doing a project for work and need to copy a interest formula to th next 214 months (Ipmt function). Ii am receiving an error when I try t fill the remind cells. Is there a way to copy that formula? Savings Goal - $750,000 Term - 10 years Interest - 5.5 -- Message posted from http://www.ExcelForum.com Maybe you could post your formula for comments. -- Don Guillett SalesAid Software donaldb@281.com "BradP >" <<BradP.17pbbl@excelforum-nospam.com> wrote in message news:BradP.17pbbl@excelforum-nospam.com... > I am doing a project for work and need to copy...

I would like to have Excel total the sum of a row 5 of randomly generated numbers in a 6th column, one column at a time. I have no problem generating the random numbers and totalling them but I would like to generate the first number and place it in the total column then when I initiate it generate the second number, add this to the first and place it in the total and so on to the 5th column. Is this possible? Many thanks. Unless I'm missing something here, this formula should do it for you. This is for row 2 on the sheet, and would go into cell F2 =SUM(A2:E2) That formula will &q...

Hello, I am currently working with Visual Studio 2008 and asp.net 3.0 I have the following question I hope that someone can help me with: 1) I have a GridView with rows that I read from a database. 2) The user can select a row from the grid, which fires the event: protected void MyGrid_SelectedIndexChanged(object sender, EventArgs e) { <update text field on form> <call client javascript function> } 3) I have the <update text field on form> portion of the routine working, but need to call the <cal...

Excel 2003: Is there a way to sort rows by color? Put all the blue rows together, all the green together, etc? There is but you need to work it. Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html#sorting -- HTH RP (remove nothere from the email address if mailing direct) "SharonJo" <anonymous@discussions.microsoft.com> wrote in message news:144901c4f9c9$f734ec80$a601280a@phx.gbl... > Excel 2003: Is there a way to sort rows by color? Put all > the blue rows together, all the green together, etc? ...

HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo Hi Geo This array formula will do the job: =AVERAGE(IF(F35:U35<>0,F35:U35)) To be entered with <Shift><Ctrl><Enter> instead of <Enter>, also if edited later. -- Best Regards Leo Heuser Followup to newsgroup only please. "Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse news:9C2B0B65-1AF...

Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

Hello Experts. I have a Yahtzee game with 2 buttons/2 macros. One button (Roll Dice) rolls the dice (Calculate) and the second button (Next Player) clears all checkboxes on the sheet. However, I'd like to know if there's a way to disable the first button after it's been clicked 3 times, then re-enable it once the second button is clicked and start the count over... Please advise. I thank you for your time. We can use a Global variable to communicate between the two macros: Dim IAmTheCount As Integer Sub FirstButton() If IsEmpty(IAmTheCount) Then I...

how do i fix this situation? ...

In excel my rows are numbered 1 through XXX and my columns are also numbered 1 through XXX. On my other computers using the same current version of Excel the columns are identified using Alpha A,B,C,D etc. Anyone know how to change the columns to Alpha v.s. numbers? Joe, tools, optins, general, and uncheck R1C1 reference style -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Joe" <anonymous@discussions.mic...

I am selecting row 1 and row 2 to freeze and excel keeps freezing at row 12? Select one row only, if you want 2 rows above where it freezes select row 3, that will leave row1 and 2 unaffected when you scroll down -- Regards, Peo Sjoblom "Lori Brooks" <Lori Brooks@discussions.microsoft.com> wrote in message news:C968CA9B-35A3-427E-993C-D36F3B6D37B7@microsoft.com... >I am selecting row 1 and row 2 to freeze and excel keeps freezing at row >12? Hi, To freeze row 1 & 2 select A3 and then Window|Freeze Panes Mike "Lori Broo...

when I enter a formula in a cell it displays a 0, when there is not an answer to the formula. Is there a way to keep the formula but get rid of the zeros any help is greatly apperciated! Thanks, Darrell Two ways, In Tools>Options>View, uncheck the zero values checkbox - that hides them Other way is to trap it, like =IF(formula=0,"",formula) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Darrell" <anonymous@discussions.microsoft.com> wrote in message news:D4B48DFF-...