MSGROUPS.NET | Post Question | Search | About | Groups | Contact | Register | Login



microsoft public excel misc (5743)

microsoft public outlook general (4869)

microsoft public excel programming (4243)

microsoft public word docmanagement (3547)

microsoft public excel worksheet functions (3216)

microsoft public access (3189)

microsoft public windowsxp general (2055)

microsoft public windows vista mail (1637)

microsoft public office misc (1582)

microsoft public windows vista general (1516)

microsoft public windows live mail desktop (1371)

microsoft public access queries (1333)

microsoft public powerpoint (1284)

microsoft public access formscoding (1215)

microsoft public mac office entourage (1208)

microsoft public dotnet languages csharp (1193)

microsoft public access forms (1172)

microsoft public internetexplorer general (1141)

microsoft public access modulesdaovba (1078)

microsoft public windows server sbs (1069)

microsoft public greatplains (1058)

microsoft public mac office word (1027)

microsoft public outlook calendaring (968)

microsoft public excel (938)

microsoft public sqlserver programming (912)

microsoft public project server (886)

microsoft public windowsupdate (827)

microsoft public windowsxp help and support (801)

microsoft public windowsmedia player (789)

microsoft public project (786)

microsoft public access reports (768)

microsoft public mac office excel (768)

microsoft public exchange admin (757)

microsoft public dotnet framework aspnet (749)

microsoft public sqlserver server (705)

microsoft public outlook (704)

microsoft public outlook contacts (681)

microsoft public dataprotectionmanager (666)

microsoft public word vba general (641)

microsoft public windows server general (632)

microsoft public windows server active directory (589)

microsoft public outlook installation (589)

microsoft public word newusers (566)

microsoft public publisher (518)

microsoft public excel newusers (511)

microsoft public excel charting (503)

microsoft public word pagelayout (498)

microsoft public dotnet languages vb (470)

microsoft public vb general discussion (461)

microsoft public development device drivers (448)

microsoft public word application errors (438)

microsoft public office setup (430)

microsoft public sharepoint general (427)

microsoft public windowsce platbuilder (414)

microsoft public access gettingstarted (412)

microsoft public windows powershell (409)

microsoft public onenote (399)

microsoft public frontpage client (394)

microsoft public sqlserver reportingsvcs (390)

microsoft public outlookexpress general (373)

microsoft public access tablesdbdesign (354)

microsoft public pos (354)

microsoft public vc mfc (349)

microsoft public outlook program vba (342)

microsoft public windows live messenger (325)

microsoft public money (310)

microsoft public windowsxp hardware (300)

microsoft public windows 64bit general (297)

microsoft public scripting vbscript (290)

microsoft public windowsxp basics (241)

microsoft public windows mediacenter (230)

microsoft public dotnet general (227)

microsoft public mac office (198)

microsoft public nntp test (195)

microsoft public windows inetexplorer ie6 outlookexpress (194)

microsoft public win98 gen discussion (155)

microsoft public windows vista music pictures video (154)

microsoft public sharepoint setup and administration (142)

microsoft public windows vista hardware devices (131)

microsoft public dotnet framework (105)

microsoft public windows vista performance maintenance (97)

microsoft public visio (90)

microsoft public access 3rdpartyusrgrp (73)

microsoft public dotnet xml (59)

microsoft public access conversion (56)

microsoft public pt windowsxp (36)

microsoft public windows live photogallery (32)

microsoft public access developers toolkitode (32)

microsoft public dotnet faqs (15)

microsoft public dotnet framework aspnet datagridcontrol (10)


microsoft.public.excel.worksheet.functions Post New
Items(3214) /33 Next >> Last >|
Subject Posted Replies From
Using Iserror with If statement and Vlookup

I have the following formula in a cell: =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0) Problem is that the results of this are used for calculations and K2 sometimes doesn't equal "STEEL" so I get an error. I've tried putting an Iserror and have the error = 0 but am doing something wrong. Can you help?

3/12/2010 2:35:01 PM 0 =?Utf-8?B?anVsaWVqZzE=?= <julie...@discussions.microsoft.com>
Help with SUMIF & AND function

Hi, how do I create a formula to sumarize value by group and subgroup? My formula by group works but unfortunately the 2nd one doesn't work. What I need is : sumarize the value (column D ) of all products having group = A and subgroup = A. total group subgroup value by_gr by_subgr A A 10 A A 20 A B 30 B A 40 B A 50 B B 60 C A 70 C B 80 D A 90 D B 10 60 =SUMIF(A2:A12;"A";C2:C12) this is OK total 460 something like this: =SUMIF(A2:A12;"A".... AND ...B2:B12;"A";C2:C12) ??? Thanks in advance, mirko

3/12/2010 2:22:01 PM 1 =?Utf-8?B?bWlya28=?= <mi...@discussions.microsoft.com>
Excel file size increased 3 times after saving!!!!

Hi all, I have a very strange situation with my Excel workbook. I created Excel workbook with conditional formulas (no other formulas) which total size is 3 Mb. After I put simple data for calculation and saved the workbook the size increased to 10Mb! The data itself is just about 70Kb. I use Excel 2002 at work. I should put this workbook on our company server, but our network is not very good and people can't open it because of the 10MB. I have never seen this before! Can somebody help please? Thank you in advance!

3/12/2010 12:57:01 PM 0 =?Utf-8?B?RXhjZWxsZXI=?= <Excel...@discussions.microsoft.com>
Consolidating Data Between Worksheets with Duplicates Removed

I am interested in tracking lab values that my staff enter into a daily worksheet. I would like to compile monthly percentages (% low, % in range, % high) but sometimes it is necessary to have duplicate data on consecutive worksheets for my staff's workflow. These duplicate values will cause my percentages (and absolute numbers) to be incorrect if I just sum between the worksheets. Does anyone have a solution? Thank you, Jody

3/12/2010 12:48:02 PM 0 =?Utf-8?B?Sm9keVNtaXRoUGhhcm1E?= <JodySmithPha...@discussions.microsoft.com>
Return Corresponding Value Based on Comparing Two Sheets of Data

Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234

3/12/2010 5:06:07 AM 4 =?Utf-8?B?UGF1bFE=?= <Pa...@discussions.microsoft.com>
slope?

Hi, if I have the following table how do I find x? 1 1134.9 2 3 1138.99 4 5 x

3/12/2010 4:30:01 AM 1 =?Utf-8?B?Sm9obg==?= <J...@discussions.microsoft.com>
Check to see if a value exists in a list?

Hi and thanks again for your help out there... Is there any function to test if a particular value exists in a list? A simple example: Column A1= car; A2=train; A3=plane In B2 I place "car". Is there a function which will return TRUE if B2 exists in A1:A3, or false otherwise? Thanks Pradhan

3/12/2010 3:04:03 AM 2 =?Utf-8?B?UHJhZGhhbg==?= <Prad...@discussions.microsoft.com>
Any idea why this is giving me a #Value! Error?

Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) 'MsgBox "String" + TmpStr For i = 0 To UBound(TmpStr) If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(TmpStr) = "THE") And _ Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If End If Next End Function

3/12/2010 1:23:01 AM 2 =?Utf-8?B?bXNueWMwNw==?= <msny...@discussions.microsoft.com>
Why won't this function work?

This function works: =IF(B71="","",IF(B71<=330,"Standard","Below Standard")) But when I change "330" to "R4" it doesn't work. R4 is a number field that has 330 in it. It doesn't seem to be recognizing the Value_if_False statement. Any number will make it show "Standard." =IF(B71="","",IF(B71<=R4,"Standard","Below Standard")) Your help is appreciated.

3/12/2010 12:29:02 AM 3 =?Utf-8?B?QW5nZWxzbmVjcm9wb2xpcw==?= <Angelsnecropo...@discussions.microsoft.com>
SUMIF or SUMIFS help

I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to

3/11/2010 11:33:02 PM 1 =?Utf-8?B?UkxE?= <...@discussions.microsoft.com>
Removing Of/And/The from Acronym Function

Someone kindly provided me with: Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) For i = 0 To UBound(TmpStr) If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If Next End Function Which worked great but didn't ignore The/And/Of if capped. So I found some code that should have worked If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(prom) = "THE") And _ Not (UCase(prom) = "AND") And Not (UCase(prom) = "A")

3/11/2010 11:16:02 PM 0 =?Utf-8?B?bXNueWMwNw==?= <msny...@discussions.microsoft.com>
Countif function

I need to find out how this countif works. In my excel class I did the countif like this. Statistical, countif, I selected the range, press F4, tab and with insertion point in the criteria box I clicked the cell that was needed. It came out really well. However, when I had to do the skills review, I did exactly the same thing, the formula came out, but the answer was obvously wrong. The question was to count the number of employees with a rating of 5. So I clicked in the cell that it needed to go, selected the range of the employee ratings and F4, then selected the other cell

3/11/2010 9:51:01 PM 1 =?Utf-8?B?am95Y2UgbG9zIGFuZ2VsZXM=?= <joycelosange...@discussions.microsoft.com>
Data Validation, Identify Duplicates and Limit imput

I have a sheet where I have a reference number in Col D that is comprised of 3 letters, a space then 3, 4, 5 or 6 digits.. I need to identify duplicate entries as people enter a duplicate reference number Col D ABC 1234 ABS 22221 ABR 124 ASR 554477 I have managed to find a formula within data validation that will give an error message if a duplicate reference is entered however I need to add something to make sure that only three letters , then a space, then numbers can be input or that peoiple can only input letters and numbers with no spaces or other characters. The

3/11/2010 9:38:01 PM 2 =?Utf-8?B?TGlseXB1dA==?= <Lily...@discussions.microsoft.com>
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)

I have a bunch of columns with dollar values. I want to add the sum of all the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions

3/11/2010 9:10:01 PM 1 =?Utf-8?B?QW5uIFNjaGFycGY=?= <AnnScha...@discussions.microsoft.com>
Acronym Function?

Is there anyway to generate an acronym on cell contents with a function? Ideally I could ignore certain words e.g. of, the, and, etc. and then for the first letter of each word generate an acronym e.g. United States of America = USA

3/11/2010 8:42:11 PM 2 =?Utf-8?B?bXNueWMwNw==?= <msny...@discussions.microsoft.com>
countif??

i have a list of peoples ages.. i need a formulat that tells me how many people are aged from 0-19, 20-24, 24-25 etc. im using countif but i cant figure out how to make it work. also i need to include 20 and 24 for example. please help!!

3/11/2010 8:26:01 PM 2 =?Utf-8?B?TGl6?= <...@discussions.microsoft.com>
formulas

how do i write a formula to add cell contents that are seperated by ten other cells?

3/11/2010 8:01:03 PM 2 =?Utf-8?B?a2Nhcmxzb24=?= <kcarl...@discussions.microsoft.com>
Calculation Question

I have to calculate total tax based on a graduated scale. Let's say I make $15,000 (in cell A1) and I want to calculate tax in one cell (in cell B1). The tax rate is such that the first $1,000 is at 0%, the next $2,000 at 10%, the next $3,000 at 20%, and then anything higher than $6,000 at 25%. What would the formula look like in cell B1 to calculate the tax expense of $3,050. Thanks, Scott

3/11/2010 8:01:03 PM 1 =?Utf-8?B?U2NvdHQ=?= <Sc...@discussions.microsoft.com>
Odd Drop Down Box Behavior

I have a drop down box on a spreadsheet in a workbook with several spreadsheets. For some reason the contents of the drop down box is beeing bled through to other spreadsheets in the exact same cell reference that the drop down box is linked to, replacing what may be in that cell. It happens randomly yet often, and I have not been able to discern any patterrn or catalyst. The contents and cell formatting are being transfered. It also happens on other spreadsheets using the drop down box. This is an Active-X box, not a forms box, using Excel 2007. Anybody seen this? Squeaky

3/11/2010 8:00:01 PM 0 =?Utf-8?B?U3F1ZWFreQ==?= <Sque...@discussions.microsoft.com>
The Tueday prior to a holiday...

If I have holidays identified with an H in the A column, I need to find the prior Tues, from the Sat prior to the holiday. Basically I need to find the Sat prior to the holiday, then go back 4 days. I'd like the results to show in the D column like ALERT. Thanks, Steve A B C D 6/28/10 Mon 6/29/10 Tue ALERT 6/30/10 Wed 7/1/10 Thu 7/2/10 Fri 7/3/10 Sat H 7/4/10 Sun 7/5/10 Mon 7/6/10 Tue 7/7/10 Wed 7/8/10 Thu 7/9/10 Fri

3/11/2010 7:39:01 PM 2 =?Utf-8?B?U3RldmU=?= <St...@discussions.microsoft.com>
Conditional Formatting for 4 conditions

How can I set up conditional formatting for the following: >3.5 = red 2.5 - 3.49 = yellow 2 - 2.49 = green <2 = blue Thanks, Steve

3/11/2010 7:32:01 PM 2 =?Utf-8?B?U3RldmU=?= <St...@discussions.microsoft.com>
Autogroup then sum - can it be done?

This is what I would like to do, if possible: Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/microsoft_excel_2003_function_0027.html), but it requires that you type the different things in column A into the formula. I want excel to

3/11/2010 6:58:01 PM 1 =?Utf-8?B?SmVzc2ljYSBSb2JpbnNvbg==?= <Jessica Robin...@discussions.microsoft.com>
Sum Multiple Worksheets, same named cell

I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks!

3/11/2010 6:57:02 PM 1 =?Utf-8?B?dW5jcmVhdGl2ZQ==?= <uncreat...@discussions.microsoft.com>
Running Sum

How do I calculate a running total for specific functions? My example is as follows A B C D E F 1 Function: Email Phone Email Faxing Prepping 2 Start Time: 4:25 PM 6:15 AM 6:30 AM 7:05 AM 7:50 AM 3 End Time: 5:05 PM 6:25 AM 6:35 AM 7:30 AM 8:00 AM 4 Total Time(min): 40 10 5 25 10 In another Cell, for example B6, I want to total up the total time of all Email (B4+D4), Phone, Faxing, Prepping etc. The user is picking from a

3/11/2010 6:27:01 PM 1 =?Utf-8?B?SmVzc2ljYQ==?= <Jess...@discussions.microsoft.com>
Macro question

I need a macro that will add the text -0000 at the end of whatever text is in that current cell. For example Cell = 75154 I need to add -0000 at the end of this so then it will be 75154-0000 I have tried all the formating stuff, but the Zipcode cell format doesnt work.

3/11/2010 6:19:01 PM 1 =?Utf-8?B?VGluYQ==?= <T...@discussions.microsoft.com>
Merging different sheets

hello everybody, I have to work with 2 sheets. In the 1st one my data are arranged as follows: Payment Date Type 15/02/2010 XS 15/02/2010 LS 16/02/2010 XS .... ... and in the 2nd sheet: Task Activity XS Activity1 XS Activity2 XS Activity3 LS Activity4 LS Activity5 .... .... and I want to create in a new sheet the following set of data Payment Date Type Activity 15/02/2010 XS Activity1 15/02/2010 XS Activity2 15/02/2010 XS Activity3 15/02/2010 LS Activity4 15/02/2010 LS Activity5 16/02/2010 XS Activity1 16/02/2010 XS Ac

3/11/2010 6:18:01 PM 2 =?Utf-8?B?dmFzc2lsaXM=?= <vassi...@discussions.microsoft.com>
Highlight Cell that contains invalid email format

Hi again, I've been working on a spreadsheet that will highlight cells different colours based on criteria. I've been doing this with VBA...well, trying :) I've got very very basic skills with VBA and have got stuck trying to highlight a cell with an invalid email address. I'd like to be able to check an email address is larger than 7 characters, contains basic characters only and is in one of these formats: Text@text.com Text.text@text.com Text.text.text@text.com Text@text.text.com text.text@text.text.com text.text.text@text.text.com Text@text.text.text.com Text.text@

3/11/2010 5:43:04 PM 1 =?Utf-8?B?R2FyZXRoX0V2YW5zIChJbnRlckNhbGwgRU1FQSk=?= <GarethEvansInterCallE...@discussions.microsoft
left/right or center ?

I have days off in the P column in this format: Sat/Sun I'm using them in another column in this format SatSun, so I'm using this formula: =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun However, I have some days off that just have Sun, which produces SunSun. How can the formula be writen to produce SatSun when two days are showing, and only Sun when one day is showing ? Thanks, Steve

3/11/2010 4:45:02 PM 12 =?Utf-8?B?U3RldmU=?= <St...@discussions.microsoft.com>
create a button to print certain tabs in excel 2003

I would like a way to search every tab in 1 workbook and the same cell on each tab, than based an the value in the cell print the page or not

3/11/2010 4:20:16 PM 1 =?Utf-8?B?bGFycnk1MDQ=?= <larry...@discussions.microsoft.com>
Multiply Cell Values which include text units

I have a formula in cell G13 that shows "=B13*C13". Is there a way that i can multiply cell B13 which has a number format and reads "61 Units" to cell C13 which has a currency format and contains "34.23 / Ea" to get a value of $2,088.03 shown in cell G13? I don't know if I can show text in the cell that is being referenced in a formula. Can you show text in a cell that is being referenced in a formula without receiving the "#value!" in the formula cell? Thanks.

3/11/2010 3:50:05 PM 6 =?Utf-8?B?RGF2ZVI=?= <Da...@discussions.microsoft.com>
colating multi rows of data into single rows - no to pivot tables!

the formula below allows me to state the number of days by month i.e. 10 days from 23rd April means 8 in April and 2 in May. I have 2 issues with it though. {=IF($H$16:$H$30=A4,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B4&":"&$C4)),ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))),)} Issue 1: H16:h30 is a list of names, A4 is the name of the student Using the formula I can divide the dates over the months but for some reason when I try to match a name (a4) against the list (h16:h30) I only ever match the first name i.e. what is in h16??? other wise I get a "FALSE" sta

3/11/2010 2:23:01 PM 3 =?Utf-8?B?VUtNQU4=?= <UK...@discussions.microsoft.com>
what function to use?

What function do i use? eg. DATE PAYMENT CODE 01/03/2010 J96 01/03/2010 B24 02/03/2010 C25 04/03/2010 J96 04/03/2010 U96 04/03/2010 J96 im trying to count how many times the letter J in payment code is used in a specific date.... eg how many J codes was used on 04/03/2010? Please help.

3/11/2010 1:04:11 PM 2 =?Utf-8?B?VEo=?= <...@discussions.microsoft.com>
Why does my text in a cell with wrap text and auto height get cut

It seems that wrap text and autofit row width and row height have a limit. Any idea where to change this limit? I have quite a large text in a cell and when I do wrap text and autofit, then parts of the text get cut of. The text is still there, but it is not shown in the cell and it is cut off when printing.

3/11/2010 12:21:01 PM 3 =?Utf-8?B?SGVyYmVydEI=?= <Herbe...@discussions.microsoft.com>
sumproduct and N/A dates

The following formula produces #N/A because some of the formulae in AB produce #N/A. If I overwrite or remove the formulae producing the error, it calculates correctly but I then lose information. How can I rewrite this to essentially ignore errors - I would put in a NOT(ISERROR(blah blah)) but can't see where to fit it in. =SUMPRODUCT((A4:A278=1)*(AB4:AB278<TODAY())) Many thanks for your help Louise ------------------------------------- fluffymitten<at>fluffymitten.com

3/11/2010 11:46:01 AM 1 =?Utf-8?B?Zmx1ZmZ5bWl0dGVu?= <fluffymit...@discussions.microsoft.com>
Using english and non-English Excel commands simultaneously in a non-English installation

Hi community, I like this forum very much - I got many valuable insights from it. In the past I had an English Excel installation at the office available, recently my employer moved to the German version and deinstalled the english one. Now, when i use a english command like =VLOOKUP(A1,'Sheet 2'! A1:H200,8,FALSE) adopting the country settings for "," with ";" Excel comes up with #NAME? since I used the english term, not the german one - but sometimes the german equivalent is not at hand as fast as needed ;-) However if a open a english coined workbook of a US/UK colleague with a

3/11/2010 10:35:22 AM 6 "Michael.Tarnowski" <emt...@gmx.de>
Search Function in Discussion Groups Home

The search function does not work properly. Yesterday I filed a new thread labelled Autofill, have tried searching but it does not appear anywhere. Also, if you run a search for any thread posted this year, they never appear. Why can you not view all threads in 'Worksheet Functions' without having to search for a keyword? Can the search esults be sorted by date? Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you d

3/11/2010 9:24:01 AM 0 =?Utf-8?B?SlBEUw==?= <J...@discussions.microsoft.com>
Formula problem with Excel 2010

Hi there, I am trying to enter this formula in conditional formatting window but my Excel 2010 wont accept it. I get the error "the formula you entered contains errors". I tried the formula as =AND(H3=1,I3=1) and =AND($H$3=1,$I$3=1) doesnt matter. But when I try it like ="AND(H3=1,I3=1)" Excel accepts this formula but does nothing:( My range is =$A$3:$I$3. I cant think of any solutions. help please.

3/11/2010 6:57:01 AM 13 =?Utf-8?B?bW5z?= <...@discussions.microsoft.com>
Totalling data from several workbooks to a Summary Sheet

I am trying (unsuccessfully) in creating a formula, that will allow me to total data from several workbooks, to a Summary sheet within Excel. Does anyone know how to complete this?

3/11/2010 4:23:01 AM 1 =?Utf-8?B?UGhpbGxpcCBBbmRlcnNvbg==?= <PhillipAnder...@discussions.microsoft.com>
auto height

I have merged cells across the bottom of a worksheet for a comment area. I have formatted the text to wrap. How do I get the area to auto format the height? I want to be able to see the full comment area at a glance. I saw an explanation once, didn't really understand it and can't locate it to try and decipher. It said something about BTW code. I would appreciate simple directions. Thank you!!

3/10/2010 11:15:01 PM 0 =?Utf-8?B?bGdyZWdncw==?= <lgre...@discussions.microsoft.com>
Pivot Table Wizard causes crash

Hello, I'm using Excel 2003(11.8316.8221) SP3 When I use the Pivot Table Wizard and press the "back" button to redefine the range of data. Excel crashes. I've tried with no other files open. I can use the wizard to create now pivot tables, but not redefine this existing PT. The file is about 1 meg. Thanks in advance. Mike

3/10/2010 10:19:13 PM 1 =?Utf-8?B?bWlrZSBpbiB0ZXhhcw==?= <mikeinte...@discussions.microsoft.com>
Percentage max formula?

i need to find out how divide two cells but the answer cannot be more than 150% for example: I want to divide A2 and A1 abd have the answer be on A3 but the percentage in A3 can not be higher than 150% A 1 1 2 3 3 300.00%

3/10/2010 9:59:04 PM 2 =?Utf-8?B?bGlhcGVy?= <lia...@discussions.microsoft.com>
lookup question

I have a sheet in Excel 2007 that lists all of the courses we offer. Columns L to O indicate if a particular course if offered each quarter. For example, for one course (row 5), you might see L5: NO M5: 5F N5: 1F1B O5: NO L5 (Summer 2010) and O5 (Spring 2011) equal "NO" because the course is not offered that quarter. M5 (Fall 2010) equals 5F because 5 sections of F(ace-to-face) sections are offered, and N5 equals 1F1B because 1 section of F(ace-to-face) and 1 section of B(lackboard, or online) is offered. I am creating a separate sheet that users can use to lookup a list

3/10/2010 9:50:04 PM 1 =?Utf-8?B?QXJ0?= <...@discussions.microsoft.com>
customize drop-down box

Is there a way to use Data Validation to create an in-cell drop-down box from a range of cells...but before displaying that list in the drop-down box, can you (1) remove duplicate values (e.g., names) (2) alphabetize the list I have a list of instructors I want to automatically appear in the drop-down box, but the range the drop-down box draws from contains duplicate instructor names, and the names are not alphabetized. Thanks!

3/10/2010 9:36:01 PM 1 =?Utf-8?B?QXJ0?= <...@discussions.microsoft.com>
how can I use conditional formatting with formula and 3 poss resul

Want to test 5 calculated results against a static target. If result greater than target, color cell green. If result = target, color cell yellow. If result less than target, color cell red. Results are presented in a column with the target number in a cell 2 rows above headers in the same column.

3/10/2010 9:34:01 PM 1 =?Utf-8?B?Q0JT?= <...@discussions.microsoft.com>
How do you make a userform open automatically when you open excel?

I created a Userform in VBA, and want it to automatically appear upon opening the Excel file everytime. How do I do that?

3/10/2010 9:23:01 PM 1 =?Utf-8?B?RXZhbg==?= <E...@discussions.microsoft.com>
Referecing Worksheet names that are dates in a formula

Hi There, I've got a tricky one here, and I'm not sure if anything can be done about it. Here's the situation... I've got a workbook that I use to track the number of proposals my company writes each month. There is one tab for each month, labelled with the format mmmm yyyy (i.e. November 2009, December 2009, January 2010, etc.) and a couple of summary sheets that reference data from each of the months. I need to start a new summary sheet that only looks at the last 6 months, excluding the current month, (so during March 2010, I need it to draw data from September 2009 to

3/10/2010 9:18:59 PM 2 "David McLean" <dmcl...@careerquestcanada.com>
Formating cell by Comparing two cells for value

I have a column of data and I want to use conditional formating to compare the current cell with the one above. If the cell above is the same as the current cell I want to fill in the cell with a color. I have tried using an IF function in the condition formating but it does not seem to work. I tried =if(p9=p8) and set formating to fill the cell but it say invalid formula. This has to be simple but I am missing something. Thanks for the help.

3/10/2010 9:12:01 PM 2 =?Utf-8?B?QUpL?= <...@discussions.microsoft.com>
Averaging every 5th cell while omitting zeros

Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you.

3/10/2010 9:09:03 PM 5 =?Utf-8?B?RXJpa2E=?= <Er...@discussions.microsoft.com>
SUMPRODUCT and Dates

Greetings, I am using the following: =SUMPRODUCT(($D$27:$D$500=C12)*($G$27:$G$500>=$K$2)*($G$27:$G$500<=$K$3)*($H$27:$H$500="Single")*($B$27:$B$500)) where C12 is a depot, Single refers to a size, either Single, Part or Full and Column B is the column to sum. K2 and K3 refer to a start and finish date. The formula appears to work well but I wondered if it was possible to include the dates in the formula? Glenn

3/10/2010 8:13:02 PM 2 =?Utf-8?B?R2xlbm4=?= <Gl...@discussions.microsoft.com>
Count Cells Within 10 of a Cell Value

I need a formula that calculates how many out of a range of cells are within 10 of another cell. For instance, I have cells F3 through U3. I want to know how many of them are either 10 above or 10 below the value in cell V3. If cell V3 equals 20, than I want to know how many are between 10 and 20 and how many are between 20 and 30. Ideally, this would be in the same formula but two seperate formulas is also acceptable.

3/10/2010 8:00:01 PM 2 =?Utf-8?B?TWlzc2Jyb29rZTA2?= <Missbrook...@discussions.microsoft.com>
How to remove headers when importing from an external source?

I am combining several worksheets into a summary document. When I import external data, the headers for each worksheet remain. These are the headers that CANNOT be removed (the ones with the boxes with arrows).

3/10/2010 7:56:01 PM 1 =?Utf-8?B?ZS1MZWFybmluZw==?= <e-Learn...@discussions.microsoft.com>
How do I subtract todays date from range of dates

I need to make todays date as a constant so when I drag the formula down the cells used in calculating the cell with todays date does not change.

3/10/2010 7:47:02 PM 2 =?Utf-8?B?QUhEMw==?= <A...@discussions.microsoft.com>
Wrap text in cell

It seems that wrap text and autofit row width and row height have a limit. Any idea where to change this limit? I have quite a large text in a cell and when I do wrap text and autofit, then parts of the text get cut of. The text is still there, but it is not shown in the cell and it is cut off when printing.

3/10/2010 7:23:03 PM 0 =?Utf-8?B?SGVyYmVydEI=?= <Herbe...@discussions.microsoft.com>
excel number appearances

some numbers in excel speadsheet appear like ####### How do I get rid of them/fix them to show a currency amount

3/10/2010 7:13:01 PM 1 =?Utf-8?B?bG9uZ2dvbmU=?= <longg...@discussions.microsoft.com>
vLookup and Line Breaking

Greetings all, I have a worksheet where cell B8 and B10 perform a vLookup based on a data validation in B6: =IF(B6="","",(VLOOKUP(B6,Position,2,0))) IF(B6="","",VLOOKUP(B6,AD_Groups,2,0)) I have the following code in place to perform a line break after each comma in the returned value. It works great the first time, but then anytime I change or add something to any cell in the range B1:B10 it adds another line break to B8 and B10. Anything after B10 is fine and doesn't affect the previous cells. I guess what I'm asking is if there is a way to alter this code (which I fo

3/10/2010 6:56:08 PM 0 =?Utf-8?B?Sm9uIE0u?= <J...@discussions.microsoft.com>
How to MATCH value up a column

I need to match/find the first matching cell up a column. Ideas? TIA!

3/10/2010 6:42:01 PM 3 =?Utf-8?B?RGV2b3VyVQ==?= <Devo...@discussions.microsoft.com>
Month (MMM) from date (dd/mmm/yy)

In my spreadsheet I have a date column (col A) which I have in format (dd/mmm/yy). I need to show the corresponding month in Col B, in MMM format - April, May, etc. I'd like the month to appear automatically in the corresponding cell in col B when I type the date into col A, so that it looks like this: A B 1 Month Date Added 2 Mar 10 March 2010 3 Apr 04 April 2010 4 May 05 May 2010 I managed to do this in A2 by using the formula =B2 and then changing the format in cell A2 to MMM. However, I can't successfully copy this formula down Col A to the other cells! The forma

3/10/2010 5:03:02 PM 1 =?Utf-8?B?QW5uaWUxOTA0?= <Annie1...@discussions.microsoft.com>
Macrs with Validation Rules

I have a spread sheet with a tab called Estimnates once the Validation Item for the estimated item is changed to "Contracted" I want it to Copy the Entire row to another Sheet called Contracted Projects. Is this possible? Would the best Option be to have it Copy all Items listed "Contracted" when the Workbook it closed or upon clicking a select button? I can not have it duplicate Items.

3/10/2010 4:59:01 PM 1 =?Utf-8?B?Q2hyaXM=?= <Ch...@discussions.microsoft.com>
how to sheet1 data goes to sheet2 automaticaly ?

Hai all I would like to know one excel sheet1 data to sheet2 automaticaly,which command use for it ,please....

3/10/2010 4:03:01 PM 2 =?Utf-8?B?QUJEVVNTQUxBTQ==?= <ABDUSSA...@discussions.microsoft.com>
Custom Cell Formatting Using % With Dates

First, thanks in advance for any help. I'm using the NETWORKDAYS function along with the TODAY function to calculate the amount of time that has elapsed for a given phase on a function. The problem I have is with formatting the percentage complete. So for example... 1. Today is 3/10/10. If the phase of the project is say 3/5/10 to 3/12/10 I'm fine. 2. If the start date is later, say 3/11/10 it displays a negative number in the cell, like -10% complete. 3. If the completion date was 3/9/10 I might end up with something like 110% complete. Now, I've been able to fix this b

3/10/2010 3:41:54 PM 2 Jason McDonald <jasonandpam...@gmail.com>
Prevent dragging cell contents

I am trying to prevent dragging cell contents in a form. I cleared "Allow cell drag and drop check box" under Tools. But it works in only one computer. If I use the same form in a different computer, it is not working. Need help. Thanks.

3/10/2010 3:41:02 PM 0 =?Utf-8?B?R1M=?= <...@discussions.microsoft.com>
Text Function

Is there a text function counterpart to the SUMIF. A concatenate if? I have a table of data and based upon a numeric value in the data I would like to concatenate all of the text strings that might be assocaited with all of the instances of a given numeric value...

3/10/2010 3:29:02 PM 1 =?Utf-8?B?VWx2YVplbGw=?= <UlvaZ...@discussions.microsoft.com>
Locking a cell reference in a formula

I have a worksheet that has formulas built within 6 columns. I want to drag the formula in each column down 100 rows, but as I am calculating a date that references only 1 cell (A1) when I drag the formula, it picks up A2, A3, A4 etc. as the formula progresses down the column. Is there a way to lock the cell reference to A1 so that when I drag the formula down the column, that cell reference doesn't change?

3/10/2010 3:21:01 PM 1 =?Utf-8?B?b2Rvbm5qaw==?= <odon...@discussions.microsoft.com>
Matching Data

Hi I have two sheets in a single workbook I want to look up where data in sheet 1 Column A matches the data and sheet 2 Column A and where there is a match bring the relevant data from Sheet 2 Column H and place it into Column H on Sheet 1. I think this is a Lookup, but cannot get the syntax right. Any help much appreciated thanks Alex

3/10/2010 3:05:21 PM 1 Alex Hammerstein <...@misnet.co.uk>
Autofill from Data Validation

I have a list of staff in sheet 1, along with where they work in the company i.e. their geographic location, their department name, their section and their team name etc. How do I use a Vlookup to show me all the names of staff from the list who are in 'x' department and/or 'y' team name? Thanks

3/10/2010 2:54:02 PM 1 =?Utf-8?B?SlBEUw==?= <J...@discussions.microsoft.com>
Removing Hyphen(s)

I have numbers separated by Hyphen(s) and I am looking for an easy way to remove the Hyphen and just leave the text My data could be 1410-4031 or 1469-1555-043 Thanks -- ce

3/10/2010 2:49:01 PM 2 =?Utf-8?B?Q3VydGlz?= <curtis.ea...@yahoo.ca>
Returning a label rather than a number in pivot table field

I am new to pivot tables and have managed to create a few that show the desired data. However, I would like the value returned to be the name of a company rather than a number. Is this possible? For example, I have company types in the column (e.g. small & big) and geographic location in rows (e.g. Europe & North America). The data shown in the pivot table is minimum, maximum and average. However, I would like the company name instead of the minimum value. I know that I can right-click and show details, but that is all the data, not just for the particular cell. One o

3/10/2010 2:18:01 PM 1 =?Utf-8?B?S3Jpc3RpYW4=?= <Krist...@discussions.microsoft.com>
Report one value out of a range

Hello everyone. I have the following scenerio that I sure could use some help on: A B C D E 1 Mike tools Sam tools 2 Tom bike 3 Mike bed 4 5 Sam tools ab2 6 Sam bike ab3 7 Sam bed ab4 8 Tom tools ab5 9 Tom bike ab6 10 Tom bed ab7 11 Mike tools ab8 ab8 12 Mike bike ab9 13 Mike bed ab10 Cells A1 & B1 are each list boxes, drawing from the lists on D1,2&3 and E1,2&3. In cell D5 I have the formula: =IF(AND(A5=$A$1,B5=$B$1),C5,"") This formula also exists in cells D6:D13, using the app

3/10/2010 2:01:01 PM 2 =?Utf-8?B?am9sbHly?= <jol...@discussions.microsoft.com>
how do I print edge to edge with absolutely NO margins

I'm trying to create a form and want the print edge to go as far to the right as possible. How can I "get ur done"?

3/10/2010 1:29:02 PM 2 =?Utf-8?B?R2lybDRjaHVja2ll?= <Girl4chuc...@discussions.microsoft.com>
validation

hi, every one.. How to validate between two workbook..? Is there any option on this

3/10/2010 11:57:01 AM 2 =?Utf-8?B?c2VsdmFyYWo=?= <selva...@discussions.microsoft.com>
Changing info from one worksheet to the next

Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i

3/10/2010 11:18:04 AM 1 =?Utf-8?B?RnJlZERhdmU=?= <FredD...@discussions.microsoft.com>
Dynamic Named Ranges - is this possible?

Is it possible to take 2 dynamic named ranges that are effectively side by side and print them on one piece of paper, one below the other? I hope so........

3/10/2010 10:32:01 AM 2 =?Utf-8?B?Q29kZSBOdW1wdHk=?= <CodeNum...@discussions.microsoft.com>
Sum and Percentage Question...

Hi, I'm after some help on what should be a fairly simple formula... I have 2 columns of data as follows: Column A - Product Name Column B - Quantity In Column C I would like a formula that does the follwing: 1. Subtracts 1 from the Quantity. 2. Multiplies the revised quantity by a price manually entered in the formula. 3. Subtracts a % from this value - this % is variable and could be 5%, 10% or 20%. So for example: Product Name = "Apples" Quantity = 3 % to discount from Sum = 5% Manual Price = £50 The formula would therefore subtract 1 from 3 (leaving 2),

3/10/2010 9:51:02 AM 2 =?Utf-8?B?RG9vZnVz?= <Doo...@discussions.microsoft.com>
Formulas to reference range based on data in column

I probably didn't title this too well. I have a spreadsheet with 500 rows of data, sorted by the date in column A. I want to extract certain data from rows that all start with the same specified date. Previously I used an IF formula but that necessitates having the formula in 500 rows which won't work for my purposes. I'm going round incircles trying to think how best to achieve this.

3/10/2010 9:08:05 AM 6 =?Utf-8?B?Q29kZSBOdW1wdHk=?= <CodeNum...@discussions.microsoft.com>
"search" a different sheet with multiple criteria

I would like to know if its possible to use a formula like VLOOKUP using more than one criteria in Excel 2007? For example, say I have Sheet 1 that includes A :: B :: C :: D BUS 280 :: SU2009 :: online :: Art BUS 340 :: FA2009 :: online :: Mike MGT 240 :: FA2009 :: hybrid :: Steve MKG 344 :: SP2010 :: online :: Terrance MKG 401 :: SU2009 :: online :: Art On Sheet 2, I want to have three drop down boxes in A1, B1, and C1, so the user can select the criteria on which to search. (A1---Choose a course ID, B1---Choose a first run date,

3/10/2010 5:10:01 AM 2 =?Utf-8?B?QXJ0?= <...@discussions.microsoft.com>
Calculating Problem: Desperate :(

Hi. I have run into a snag trying to figure out how to make one of my cells calculate the way I need it to. Here is the rundown: I have a column of data that needs to have only 0,1,2,3, or 4 entered in each cell. These values actually represent qualitative data. At the end of the column I need to count how many variable values were entered across 20 cells (e.g. 10 out of 20, or all 20). The syntax for that function I used is =COUNTIF(D12:D31,">=0"), which works. Now the area that I am having trouble is that I need another cell that will calculate the total percentage for the col

3/9/2010 11:37:01 PM 4 =?Utf-8?B?Y2lvbm5haXRo?= <cionna...@discussions.microsoft.com>
Date Calculation problem

Hello, I have a work sheet with "JIRA" priority levels and dates the JIRA was created. Each priority leve allows a certain number of days to be complete the JIRA. I need to be able to figure out if we resolved the JIRA within the allotted number of days, excluding: 1. weekends 2. holidays I have tried to use the NETWORKDAYS() function, but I cannot use this function because I do not know the due date of the JIRA. I used the weekday function to add days for weekends and come up with the actual due date....but this does not take holidays into account. My data b

3/9/2010 11:05:01 PM 2 =?Utf-8?B?cGV0ZWRhY29vaw==?= <petedac...@discussions.microsoft.com>
using indirect to return a named range?

I'm having a mental block on how to do this. I have a boatload of named ranges in my workbook. I have one worksheet that has two data validation cells and a graph. I want the two data validation cells to be used to select the named range to use to populate the graph series. My named ranges pull from a large variety of locations (not orderly) so I put the named range names in a table format for easier reference; here is an example with named ranges by month and location- the user selects a month and location, and the graph should use the named range with the name in the corr

3/9/2010 9:48:09 PM 1 =?Utf-8?B?a2VyXzAx?= <ke...@discussions.microsoft.com>
Can a formula be "built" referencing text content from another cel

Hi, and thank you for your help. I have run across this need many times...is there any way to use to contents of one cell, to construct a formula in another cell? Here is my particular requirement. I have worksheets named January, February, March...etc. On a separate Summary Worksheet, I reference the same cells on each of those sheets. For example, I may have in 3 adjacent cells, =January!A1 =February!A1 =March!A1 and then in the next row: =January!A7 =February!A7 =March!A7 etc. It would, obviously, be much easier to "build a formul

3/9/2010 9:40:01 PM 3 =?Utf-8?B?UHJhZGhhbg==?= <Prad...@discussions.microsoft.com>
COUNTIF across sheets in columns

I am trying to count names that appear in multiple sheets. All of the names appear in the column K in 32 sheets. Is there away to do this? Thanks for any help.

3/9/2010 9:25:01 PM 1 =?Utf-8?B?bWVla2pqMTM=?= <meekj...@discussions.microsoft.com>
Normalize/concatenate

Hello, I have a data set that looks like this: Sortterm Doc # Main Term CAS # CFR REG # DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.300 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.380 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.390 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 176.170 I'd like to concatentate the last column with the unique values to look like this: Sorterm DocNum Mainterm CAS Regnum DIPHTHA

3/9/2010 8:09:01 PM 10 =?Utf-8?B?RWxsZW5N?= <Ell...@discussions.microsoft.com>
Earliest time in a range

Hi, I have a range of dates and times, and the following will give me the latest time: {MAX((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))} However, I now want the earliest time, but "MIN" does not work: {MIN((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))} What is the proper formula? Thanks.

3/9/2010 7:08:01 PM 3 =?Utf-8?B?cGV0ZQ==?= <p...@discussions.microsoft.com>
Pivot Table Defaults

In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"?

3/9/2010 6:53:01 PM 1 =?Utf-8?B?YnRleHBycw==?= <btex...@discussions.microsoft.com>
vlookup---using data outside the range specified in the formula

I am sure I can guess the logical answer to this question, but... I have a sheet (Sheet 1) in Excel 2007 with a list of courses. The Course ID is in Column B and course title in Column C. The remaining columns are used to keep track of content that is developed for each of the 10 weeks of the course. I use a COUNT formula to show how many cells in the "development week columns" are NOT blank. If no content, for example is developed for BUS 110 (thus, no dates of completion are includes in Columns X, Y, Z, etc.), a 0 is put in column A. If we completed three weeks, we put complet

3/9/2010 6:00:01 PM 4 =?Utf-8?B?QXJ0?= <...@discussions.microsoft.com>
using vlookup to find data outside a specified range

I am sure I can guess the logical answer to this questions, but... I have a sheet (Sheet 1) in Excel 2007 with a list of courses. The Course ID is in Column B and course title in Column C. The remaining columns are used to keep track of content that is developed for each of the 10 weeks of the course. I use a COUNT formula to show how many cells in the "development week columns" are NOT blank. If no content, for example is developed for BUS 110 (thus, no dates of completion are includes in Columns X, Y, Z, etc.), a 0 is put in column A. If we completed three weeks, we put comple

3/9/2010 5:59:01 PM 1 =?Utf-8?B?QXJ0?= <...@discussions.microsoft.com>
"clear contents" is not working. a list is still enforced.

suggestions would be much appreciated.

3/9/2010 5:44:13 PM 1 =?Utf-8?B?cGV0ZXJnMTE=?= <peter...@discussions.microsoft.com>
Can Sheet name be part of formula for a cell (+ sheet name)

Worsheet name will change from template. Would like worksheet name to auto print in cell on separate worksheet. Can this be done?

3/9/2010 5:38:01 PM 1 =?Utf-8?B?TGVzbGll?= <Les...@discussions.microsoft.com>
Lookup Function? Employee Number with Employee Name

I have a spreadsheet that I believe that I need some form of LOOKUP function, but have been unable to find the type that I need. I have a column "C" that has an employee number in it. I have created a table on Sheet 2 with the Employee Number and the corresponding Employee Name in that table. I want to have the column "D" on Sheet 1 to automatically fill in the Employee Name based on the Employee Number in column "C". What type of function would this be? and suggestions on syntax? Thanks for the help.

3/9/2010 5:16:01 PM 1 =?Utf-8?B?TGFuY2UgSGViZXJ0?= <LanceHeb...@discussions.microsoft.com>
Lookup changing data range in external workbook

I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it gi

3/9/2010 5:10:01 PM 5 =?Utf-8?B?Um9ja0NvdW50ZXI=?= <RockCoun...@discussions.microsoft.com>
Data Validation: Validate Specific Day of the Week?

Users will enter a date in cell D6. The date they enter must be a Monday. How can I validate for that? Thanks, -Ted

3/9/2010 3:52:49 PM 2 "Ted" <ted.gallag...@gmail.com>
Help requested for nested conditional formulas referencing other c

Hello--i can better explain the problem with an example: 24h 1wk change Person A 1 0 -1 Person B 0 1 1 Person C 0 0 0 Person D 0.25 0.25 0 Person E 0.5 Person F 1 0.5 -0.5 Person G 0 0.75 0.75 What I am trying to do is make three equations: 1 to calculate the average of the values in the 24h column, IF the value is NOT equal to zero, which I have accomplished with the array formula: {=AVERAGE(IF(B2:B8>0, B2:B8, ""))} here's where things get difficult--now, I want to find the average of the values in the 1wk column, only if either the values in the 24h OR 1 wk column

3/9/2010 3:48:01 PM 3 =?Utf-8?B?QmVybWll?= <Ber...@discussions.microsoft.com>
connecting formulas

I’m working on a Excel sheet where I have to connect two formulas as follows: A1 and B1 have together a value of 20%. if A1 is 12%, B1 should automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1: =0.2-A1 but its not working properly. Could someone please help me? Thanks in advance!

3/9/2010 12:41:01 PM 6 =?Utf-8?B?Q2hyaXM=?= <Ch...@discussions.microsoft.com>
I know this is obvious but ..

I'm just not seeing it this morning! I have 3 columns: Col P16 to P500 contains Project Names Col Q16 to Q500 contains Pricing Mechanism descriptions Col X16 to X500 contains Dates In Col AA16 - AA 500 I want to do the following: AA16 = Last Date for Project Name in P16:P500 where Q16:Q500 <> "Fixed Price" I can do it with a sumproduct array but it (obviously) sums the dates where the project names and pricing mechs are the same. Many thanks for your help! -- "There are 10 types of people in this world. Those who understand Binary and those who don''t ..."

3/9/2010 10:51:01 AM 3 =?Utf-8?B?Qm9ueSBQb255?= <bony_ponySPAMS...@BLOODYSPAMbtinternet.com>
Count

Hi, On my file I get a list of accounts. I have a look up which lets me know which account belongs to which individual. I need to know how many different individuals there are each day. There can be a lot of change with new accounts etc so was thinking of doing a pivot table and somehow using a COUNT function but am not sure if this is going to work or if there is a better alternative. Thanks Louisa

3/9/2010 10:16:19 AM 7 Louja <louisa.c.thomp...@googlemail.com>
AutoFill Changing Wrong Value

I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa

3/9/2010 9:37:01 AM 6 =?Utf-8?B?TGlzYQ==?= <L...@discussions.microsoft.com>
arrange columns through sheet and multi sheets

good morning A B C D Equipment serial type model mudump 123 triplex 1600hp In another sheet A B C D Equipment serial model type mudump 123 1600hp 1600hp i need to collect "type" in column C without copy and paste because the data is too wide more than those items

3/9/2010 8:07:01 AM 1 =?Utf-8?B?bS5tb2hpZQ==?= <m.mo...@discussions.microsoft.com>
Drag Down Formula Incorrect

I've got a long list of text data in column A, and am using the MID command to extract one bit of data from each which is then inserted into four columns (B-E) e.g. B9 =MID(A27,23,2) C9 =MID(A28,23,1) D9 =MID(A29,23,5) E9 =MID(A31,23,5) When I drag down B9, I want the formula to change by 20 (from A27 to A47). I have created 25 rows now, but the drag down still won't pick up the pattern of 20 between cells. Can anyone tell me how to achieve this?

3/9/2010 8:07:01 AM 3 =?Utf-8?B?RGlzY28zU3R1?= <Disco3...@discussions.microsoft.com>
Import multiple photos into excel

I am trying to create a photo sheet that will allow me to import all photos in a directory into an excel spreadsheet. I want it to: Import all images in a directory. (even if I have to choose to open the folder and then select all) Place each photo into a separate box Auto-format all the photos to the same size where (2) images fit on each printed page. Have a space for a text description to the right of each photo. Auto-number each row/photo if possible (so it changes all the numbers if I delete a row) Place a header with a logo on each printed page. Insert a merge field in the

3/9/2010 3:35:19 AM 1 "shanelawler" <u58...@uwe>
Import multiple photos into excel

I am trying to create a photo sheet that will allow me to import all photos in a directory into an excel spreadsheet. I want it to: Import all images in a directory. (even if I have to choose to open the folder and then select all) Place each photo into a separate box Auto-format all the photos to the same size where (2) images fit on each printed page. Have a space for a text description to the right of each photo. Auto-number each row/photo if possible (so it changes all the numbers if I delete a row) Place a header with a logo on each printed page. Insert a merge field in t

3/9/2010 3:33:41 AM 0 shanelawler <shanelawler.5db7...@officefrustration.com>
Summary

i have a transaction of a few people with the figures. i can only get the total amount using the filter. so, i want to have a summary in another worksheet. IS there anyway to do the summary? cos..once i change the filter in the sheet 1, the total is different. Please help!. Thank you.

3/9/2010 3:29:01 AM 2 =?Utf-8?B?RWxpeg==?= <E...@discussions.microsoft.com>

Pages: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33











Newest Articles

Excel Workbook
57 min. 37 sec. ago

How do I send monthly automatic e-mails
6 hour 58 mins ago

Does Consistency Check obey "exclude" rules in protection group?
7 hour 2 mins ago

Picture Manager
7 hour 2 mins ago

Using Iserror with If statement and Vlookup
7 hour 4 mins ago