I wish to have many rows with a conditional format between two adjacent cells, i.e. A1 & B1, A2 & B2 ( column B has the conditional format depending on the value of column A ). I can copy the condition ( using format painter ) and paste to B2. I can paste to B3, B4 etc. However if I want to paste several cells at once, the relative value in each cell in column B changes to an absolute value of the whole range of data, ie. $B$2:$B$4. The reference for column A does not change, so each cell in column B from B2 onwards has the whole range and conditional on the one cell ( A2 ). I&#...

I'll do my best to explain my question, please let me know if more information is needed. I have a "calendar" of sorts that is in Excel format. It lists several events, dates that the events will take place, and other pertinent information. I have been asked to format the calendar in such a way that when I enter in a new event, it will be in red text for 30 days, and then convert to "normal" or black text after being in the spreadsheet for 31 days. Is there a way to set this up with Conditional Formatting? Everything I have found relates to the date ac...

We are using the $ to lock the formula but when columns are inserted the formula still changes. How do we correct this? Thank you for any assistance you can give. Hi one way would be to use something like INDIRECT("A1") to always refer to cell A1. Note: copying this formula won't change the cell reference -- Regards Frank Kabel Frankfurt, Germany frankmlr wrote: > We are using the $ to lock the formula but when columns > are inserted the formula still changes. How do we correct > this? > > > Thank you for any assistance you can give. One way: =I...

I already use a formulain D2 that reads: =MAX(0,C2-6)*17+MAX(0,C2-10)*33+MAX(0,C2-30)*50 This formula gives me the Amount to Charge(D2) for Days used at Origin(C2) The Charges used in this formula are as follows: The first 6 days are free the 7th 8th 9th and 10th days are charged $17 a day the 11th through the 29th day are charged $50 a day the 30th day and above are charged $100 a day FYI---->(17+33+50=100) I need a formula that gives me the Days used at Destination.The problem Im having is that I can't make the above formula work because the origin days figure in and if the origin...

Hey all. I am creating a report from a queury I built and for some reason it won't let me format one of the columns. There is nothing in the drop down box under Format. Here is the expression: Expr3: IIf([Expr2]/[SLA]=0," ",[Expr2]/[SLA]) It reflects the correct number, but I want to reflect it as a percent. For some reason the 'IIf' statement is not formatable? Is that correct? Any ideas? Love Buzz wrote: >Hey all. > >I am creating a report from a queury I built and for some reason it won't >let me format one of the columns. There is nothin...

Hello! I want to add a formula to a cell the formula is: =TEXT(A1,"dddd") I have used this code formel = "=TEXT(A" & CStr(NewRow) & ";""dddd"")" Worksheets("Blad2").Cells(NewRow, 2).Formula = formel I get an error on the second line saying: Runtime Error '1004' application-defined or object-defined error NewRow is a valid number, e.g. 36 What is the error? // Anders -- English is not my first, or second, language so anything strange, or insulting, is due to the translation. Please correct me so I may le...

I have a general ledger spreadsheet with a annual and monthly tab followed by the individual account tabs. On the monthly tab I have the following formula: =SUMIF('9000-Muck Site'!$J:$J,"="&(TEXT(I$5,"mmm-yy")),'9000-Muck Site'!$D:$D) My problem is the range is not picking up all of the rows. Based on the formula above it stopped at D96. How do I extend (or show) other rows past D96? Thanks! Are the entries in column J really text? -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "sarge355" wrote: > I have a general ...

I am trying to paste various formulas in excel clipboard, but what it's copying to the clipboard is only the content of the cells. How do I get to paste formulas only? AndyBermuda Wrote: > I am trying to paste various formulas in excel clipboard, but what it's > copying to the clipboard is only the content of the cells. How do I > get to > paste formulas only? goto tools, options, click on "formulas" now you should have no problem -- markline ------------------------------------------------------------------------ markline's Profile: http://www.exce...

In Excel, I have a column with various numbers in each row. I want to multiple each number by 1.02. Short of doing this with extra columns, is there a way to use find/replace and add *1.02 to whatever is already existing in each cell? Paige, Enter 1.02 in some blank cell and copy that cell. Then select the number you want to multiply by 1.02. Go to the Edit menu, choose Paste Special, and choose the Multiply operation. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "paige" <anonymous@discussio...

I have a spreadsheet setup for a golf league with multiple sheets, sheet1 is for input. Sheet3 & 4 have formulas pulling the information from Sh1. Sh2 pulls information from Sh3 and Sh4. Sh1=input, Sh2=league standings(printout), Sh3=team wkly score, Sh4=individual score & handicaps Question for Sh2 and problem I have is; team1 player1's hcp(e4) =TRUNC(Scorecard!W4) team2 player1's hcp(e5) =TRUNC(Scorecard!W6) and then, team1 player 2's hcp(g4) =Trunc(Scorecard!W5) team2 player 2's hcp(g5) =Trunc(Scorecard!W7) I would like to beable to drag down this formula "...

Can you explain in a bit more detail what your question/problem is? -- Kind regards, Niek Otten "wendyp" <wendyp@discussions.microsoft.com> wrote in message news:B7A92B0A-E752-4D58-90FF-3DE5F292D35C@microsoft.com... > Hi Wendy, See Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm To simply remove constants from a selection within a macro Selection. SpecialCells(xlConstants).ClearContents To remove constants manually from a selection Edit, GoTo (Ctrl+G), Constans [you can pick what kind ...

I've never figured this one out. Went and googled yet again but I'm obviously not googling for the right thing. Same as with the help file. What is the type of formula we use for adding up columns or rows, esp. if they're broken up by rows/columns in between? Thanks! =SUM(B1:B10,D1:D10,F1:F10,H1:H10) -- Regards, Peo Sjoblom "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message news:ORPkLEa1FHA.2792@tk2msftngp13.phx.gbl... > I've never figured this one out. Went and googled yet again but I'm > obviously not googling for the right t...

Hi. Has anyone had success printing price labels with a Datamax printer? I have tried using the RMS Label Designer, but have not had much success. I'm able to query data and send it to the Bar Tender program via access, but that is very labor-intensive. Anyone have a working template I could look at? Failing that, any advice on how to develop my own template or find another way to print labels directly from RMS would be appreciated. I'm using a Datamax E-4203. Thanks Since no one answered my post, taking the bull by the horns, I opened up the label designer. Since I need priceta...

Hi, is there a way to hide the formulas in a spreadsheet so they'll only see the result? thank you Just google for "hide formulas excel" there are macro solutions and sheet protection solutions -- Greetings from New Zealand "Heather" <Heather@discussions.microsoft.com> wrote in message news:861C88E9-DE51-4557-85CB-9DB9F1A8ABED@microsoft.com... > Hi, is there a way to hide the formulas in a spreadsheet so they'll only > see > the result? thank you Select the cells to be hidden. Format>Cells>Protection. Checkmark "hidden"......

How to get values obtained by an array formula in a single cell separated by commas or any other symbols? Can we sum up or count the values obtained by an array formula in a single cell? The answer to your second question is yes. Simply wrap the array formula witha SUM() function, and commit the entire formula with Ctrl-Shift-Enter "TUNGANA KURMA RAJU" wrote: > How to get values obtained by an array formula in a single cell separated by > commas or any other symbols? > Can we sum up or count the values obtained by an array formula in a single > cell? ...

quiero saber se me puede ayudar en eso praticamente yo quiero una formula da enserir an la celda CANCELLARE (mira foto para entender) http://img20.imageshack.us/img20/2152/89861598.jpg [img=http://img20.imageshack.us/img20/2152/89861598.th.jpg] que quando uno presiona la tecla CANCELLARE se borre todo el contenido de la linea detras de CANCELLARE o vero ( C9-D9-E9-F9-G9-H9 ) y trambien se tendrian que borrar en la hoja (tabla de datos ) donde estan lo mismo valore de ( C9-D9-E9-F9-G9-H9 ) o vero ( A7-B7-C7-D7-E7- F7-G7 )( mira la foto abajo ) http://img20.imageshack.us/img20/9799/32649272.jp...

Is there a way to apply data validation to one cell, based on the result of another cell? For instance, if in A1, a data validation list displays sports (ie baseball, basketball,etc), and if the user selects "Baseball", then the data validation in B1 would only list baseball teams, from the range named baseball teams. And if basketball is selected, then only basketball teams are displayed. If at all possible, could this be done via a formula and WITHOUT using VB? If not, I could also use any help to accomplish this via VB coding. Any and all help will be appreciated...

hi- i need to return a value based on 2 conditions... i have a list of 500+ names, they all have a unique identifier (employee id) so i need to get different values/scores from tab 2 into tab 1. so, for john i'd have his score from category 1 (80%) in tab 1 C2 and his score for category 2 (20%) in D2 and son on. i did a simple v-lookup =(VLOOKUP(C$2,Tab 2!$A:$C,3,FALSE)) BUT it gives me the first score it finds that matches the category name and I need to score for each category for each employee... any ideas??? tab 1: A B C ...

Hello everyone, This is my second posting. Does anyone know a VBA script to conditionally hide selected rows if they equal zero or are left blank? Please help. Use a formula on the worksheet to provide a null value or an "X" and then filter on that column. Data, Filter You could use advanced filter and include the formula. More information on Filters: http://www.contextures.com/tiptech.html --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/...

Does anyone know how to color format a pick list? Maybe, if we knew what it was. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- <doris.esposito@usss.dhs.gov> wrote in message news:17ea701c44986$218ab400$a001280a@phx.gbl... Does anyone know how to color format a pick list? Hi RagDyer Maybe the op was talking about Excel's drop-down lists that are accessible by right-click/Pick from List or Alt+DownArrow o...

Here is what I am trying to accomplish: I am creating a workflow such that, at the creation of an opportunity, MS CRM will send an email to the potential customer. The template to be utilized depends on certain conditions on the primary contact of the opportunity's potential customer (account). My problem: Though the workflow manager is offering contact conditions in opportunity rules, it does not seem to 'read' any contact at all - conditions are never met. Could anyone help me on this? ...

Does anyone know how I can preserve my date format (dd/mm/yy) when doing a mail merge from Excel to Word? I would like to show either dd/mm/yy or 10 February 2010, but it keeps being switched round to American format (mm/dd/yy). Thanks! Debra Dalgleish posted this: There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill has instructions here: http://sbarnhill.mvps...

A friend of mine asked me if the following formula can be shortened? =IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2> 30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT (D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT (D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address ",M2,"; }")))) FYI: The cell D2 contains: 00E06F734032 Is there a way? ...

Hello All Experts, I am using Office 2003 and have the following problem: File name : AMC2005 Sheet Name : Combined Current Range in Use A1:AB500 (and increasing day by day) The sheet is designed having Headings in Row 1 My problem is that I wish to make to freeze the Row when certain entries is made in two particular columns (viz A and B) If the Value typed in Column A is 'Expired' AND Column B is typed either (Paid, Invoice, or Check) then that entire row should be protected (i.e. un-editable) At present there is data in the current range A1:AB500 but when I fill both Column A ...

I am trying to creat a shipping list from a set of 10 worksheets. Ther are several cells that need to be linked to the shipping list only i "x" is indicated in a particular cell in that column. I am able to ge the cell I want to link by using the following formula =IF(Sheet1!S10="X",Sheet1!E10," ") How can I list only the lines with "x"? I do not want to link only 1 line at a time. I want it to check th first line. If there is no "x" then check the next line until it find an x and then link it. Thanks in advance Cand -- CNA4 -------...