microsoft.public.excel.worksheet.functions Google Group

Calculating text as values in a row

mickkemp2004-gene...@yahoo.co.uk (Mick) — Sat, 05 Dec 2009 22:46:58 UT

I am stumped and wondered if anyone could asisst, I have looked through the
newsgroup but found nothing similar to me problem.
I want to obtain the total number from a text input, in a row (up to 31
cells wide) the following could be entered, FD, AM or PM.
These have different values, FD=1, AM=0.5, PM=0.5.

Counting dates with specific criteria

st...@discussions.microsoft.com (Steve) — Sat, 05 Dec 2009 22:10:01 UT

Hi, I have a column with various dates, I need some formulae to count
different criteria.
The criteria are: 1. Dates in the current month. 2. Dates last month. 3.
dates in a calender year. 4. Dates in a financial year.
Each criteria will be shown in seperate cells on a different worksheet on
Excel 2003, also the current month is always the current month as we progress

Populate Text based on conditions

e...@discussions.microsoft.com (envy) — Sat, 05 Dec 2009 21:13:01 UT

Hi -
I am trying to determine a way to populate text based on input conditions.
My example is with moon phases. I have all the moon phases based on date as
follows in one sheet:
A B
Date Moon Phase
9/2 1st
9/3 1st
9/4 Full
etc..
In another I have a table where the user inputs the date she worked and I

excel pivot trables

doug...@discussions.microsoft.com (douglas) — Sat, 05 Dec 2009 21:01:01 UT

I have created an excel workbook 2007 and have saved lotsof pivot tables in
each tab. I need to have these spreadsheets in excel 2003 since that is what
most of my users have.
The problem is that when I save as the excel 2003, the data is saved
including the pivot tables. However once a user tries to with the pivot

Find data in two ranges

sant...@discussions.microsoft.com (Santafe) — Sat, 05 Dec 2009 20:57:01 UT

Hello,
I have a worksheet with in the same row two different ranges:
one with nummeric data
one with time values.
These two ranges works as follow the first nummeric data cooresponds with
the first time value, the second nummeric data corresponds with the second
time value.
For the nummerric data I calculated the best of 3.

Pivot Tables

a...@discussions.microsoft.com (Al) — Sat, 05 Dec 2009 18:50:01 UT

I have just begun to work with pivot tables and need to make my format look
like the following. I want to use the pivot table to develop work schedules
and want the finished product to look like the format below:
Data:
name date title 0000 0100 0200 0300 0400 0500
xxx xxx xxx x x x x x x

Need Help Fast! How to Calculate a sales percentage

jacopea...@discussions.microsoft.com (JacoPeanut) — Sat, 05 Dec 2009 18:35:01 UT

I am trying to give the sales percentage between two cells and am not sure
how to do it... Any help would be greatly appreciated. I am under a
deadline!!

Colors according to the month

dev.subraman...@gmail.com (dave) — Sat, 05 Dec 2009 17:48:47 UT

Hello,
im working on xl sheet which contain 12 months information. i need to
highlight the next column to the date according to the month. If the
date in cell A2 between 1/1/09 to 31/01/08 the next cell B2 should be
highlighted in red. If date in cell A3 between 1/5/09 to 31/05/09 then
the next cell should be highlighted in yellow. Same applies from

Calculating current grade percentage

lintoni...@yahoo.com.. (lintonindy @ yahoo. com donotspam) — Sat, 05 Dec 2009 17:32:01 UT

I'm sure this has been answered before, but I can't find it in the search
If I have values in column B (B3:B18) and enter in values in column C
(C3:C18) and want to find the current total percentage in cell C19 how do I
set up the formula to only add the cells in column B that have values in
Column C?

HELP please... easy function

nom...@nomail.com (JoZo) — Sat, 05 Dec 2009 16:12:15 UT

Hi,
I have a problem.
Here is the main formula:
=(A5*B5)/C5
but if there is nothing( blank) in cells A5 and C5 i got #DIV/0!
I don't want to have this error #DIV/0!, i just want that IF cells A5
and C5 are blank result 0 (ZERO).
I tried with that formula but is not working:
=IF(A5="blank";0;(A5*B5)/C5)

Finding the 3 best values

sant...@discussions.microsoft.com (Santafe) — Sat, 05 Dec 2009 15:29:01 UT

I have a worksheet, where in the same row is a range of numbers and another
range of times. The fist time corespond to the first number, the second time
corresponds to the second number, and so on ...
From the range of numbers I have calcutated the best of 3. Now I like to
find the times that correspond to those 3 numbers. Preffably using a

Row Colors

bet...@discussions.microsoft.com (Betty K) — Sat, 05 Dec 2009 12:11:01 UT

In Excel 2007, need to differentiate rows by color.
In column M,
"H" = Color 1
"A" = Color 2
"P" = Color 3
"L" = Color 4
I will be printing data from the spreadsheet, so I'd like to use lighter
shades of colors rather than the bright, primary colors that will show up
dark on printed pages.
Thanking you in advance.

Lookup Function Help

fur...@discussions.microsoft.com (Furn16) — Sat, 05 Dec 2009 11:46:01 UT

Hi,
I am trying to do a spreadsheet for a budget. I have one tab with the detail
of the budget with headings in columns for each category of expense, then a
second tab showing the overall position. On the overall tab the headings
appear down the left in rows rather than columns as in the detailed tab.

Sheet Protection / Edit Objects

keyrookie.55fc...@excelbanter.com (Keyrookie) — Sat, 05 Dec 2009 03:38:19 UT

I have a spreadsheet with multiple objects that I'm trying to lock. My
problem is I also have cells that I need to have comment insertion
capabilites. When going to Tools/Protection/Protect Sheet and allow
Edit Objects is checked I can insert a comment. However, it also
allows the other objects on the sheet to be accessed. Is there a way

command button to move data

du...@discussions.microsoft.com (dummy) — Sat, 05 Dec 2009 02:19:01 UT

If it's possible, I would like to use a command button or checkbox to
trigger moving data from cells in a column (b34-b41) on one worksheet to the
next available ROW on another worksheet.
Thanks in advance.

Need greater than = .. less than = combined with index/match state

dingy...@discussions.microsoft.com (Dingy101) — Sat, 05 Dec 2009 02:12:01 UT

Excel 2003
I am using formula below succesfully , but I need to expand on it
=INDEX(K3:AM30,MATCH(A4,K3:K30 ,0),MATCH(A5,K3:AM3,0))
What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,...
I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ...
0 5
6 10
11 15
16 20
21 25
26 30

Formula to copy related information from sheet 1 to sheet 2

dev.subraman...@gmail.com (dave) — Sat, 05 Dec 2009 00:12:23 UT

Hello guys,
I hope you'll can help me. i have the information below in sheet 1. i
need the information to be filtered, copy and paste in sheet 2
according to the month. example. if october, i need all information
for the month of october to be copy and past from column A, B, C sheet
1 to column A, B, C sheet 2 please help.

Remove blank cells from data validation drop down box

j...@discussions.microsoft.com (Jay) — Fri, 04 Dec 2009 23:37:02 UT

First, I've checked out Debra Dalgeish's contextures web site and found some
really *GREAT* info there (thank you!).
But I'm still having issues with the drop down function using data validation.
I have drop down boxes that rely on the value of the cells to the left. I've
got it functional with a work around, but I'd like to make this simpler.

referencing multiple worksheets in macro

michellethomp...@discussions.microsoft.com (Michelle Thompson) — Fri, 04 Dec 2009 22:11:01 UT

So I kind of found the answer to my question in a post from a couple years
ago but am still confused so if anyone could help I'd appreciate it...I'm
trying to protect the sheet while still being able to use the subtotal
function. I have several worksheets I want to do this for and this is the
post from before with the code but I can't figure out where to put the names

vlookup help pls...

s...@discussions.microsoft.com (Soth) — Fri, 04 Dec 2009 21:23:01 UT

Scenario -
A F V

11701001 $10 69003160
22130001 $20 69003121
69003120 $30 69003120
69003121 $40
69003160 $50
------------------------------ ---------------------

Time question

gu...@discussions.microsoft.com (gueyo) — Fri, 04 Dec 2009 20:53:01 UT

I need a formula that would figure out what time it would have to be to
complete a 40 hour week
The information I have to plug in is this.
Current total hours - 37 hrs 32 mins
Current time is - 12:39 pm
What time does it have to be to complete 40 hours?
Seems like it should be simple but....

Complex day of the month calandar

k...@discussions.microsoft.com (Kai) — Fri, 04 Dec 2009 20:14:01 UT

I am creating a tool for my work. It would essentially function as a sales
tool for forcasting to the end of the month. My plan was to map out the days
of the month to 31, and have a forcasted number pre-fill for the user. The
number would be based on a beginning number (say 30 on the 1st of the month).

Read specific time in NOW() function

smith...@discussions.microsoft.com (Smith512) — Fri, 04 Dec 2009 19:56:01 UT

If I have a counter that is continuously active in a cell, is there a way to
pull that count a specific time; if the NOW() fucntion is in a different
cell on same sheet?

macro will not run with shortcut keys

d...@discussions.microsoft.com (Doug) — Fri, 04 Dec 2009 19:41:02 UT

I have several macros that will not run with the shortcut keys. They are not
complicated just like auto indent 1 space and such. the macro will run when I
run it from the macro box just not the short cut keys like Ctrl+Shift+L is
the indent 1 on left side. office excel 2003

Lookup Results Problems

do...@discussions.microsoft.com (Doug S) — Fri, 04 Dec 2009 19:09:01 UT

I am attempting to search a second worksheet for and exact match using a
parameter in the first worksheet. I've solve the NA problem thanks to this
forum and MOST of the searches are turning up good data.
Unfortunately, I have a master list of hospitals in spreadsheet A and am
trying to mark which ones are transplant facilities from a list of these

Combine sheets into one

e...@discussions.microsoft.com (Eva) — Fri, 04 Dec 2009 18:48:01 UT

Hi
I have three sheets called:"Matched",Unmatched", "Other" with the same
columns and headings.
I need to combine all in one sheet called "Data All"
I think I need the macro, but I am not so experienced (I started learning
VBA) so I don't know how to do it
Can you help me?

get back a deleted column in error in excel, please help

angelstor...@discussions.microsoft.com (Angelstorm99) — Fri, 04 Dec 2009 18:25:01 UT

I have in error just deleted a whole column of my password information. Is
there a way to get that back, can't seem to find a reverse arrow.
Do hope someone can help me......

Compare 2 columns and delete duplicate text

m...@discussions.microsoft.com (MF) — Fri, 04 Dec 2009 18:05:01 UT

I need to compare a column of 50,000 terms against a column of the 50 states
to remove any state names from the terms. Is this a formula or macros? How
would it be accomplished?
Ex.
Terms:
Furniture
Chairs Idaho
Desks
Desks in Alabama
Waterbeds
Florida Waterbed Store
State list (to remove):
Alabama

IF statement inside a SUMIF statement.... or alternative method

sungibu...@discussions.microsoft.com (Sungibungi) — Fri, 04 Dec 2009 17:54:02 UT

I'm just not sure whether this is possible or not.
Can I create a function that goes as below:
=SUMIF(IF(......),Z,A:A)
It seems when I insert an IF statement inside a SUMIF statement, Excel
returns #VALUE! If this is not allowed, how do I get around this problem?

conditional formatting

ja...@discussions.microsoft.com (jas52) — Fri, 04 Dec 2009 17:45:01 UT

Excel 2007 is adding conditional formatting to cells when opening and
converting a 2003 workbook. How can I prevent this?

aging calendar days

mma...@discussions.microsoft.com (mmanis) — Fri, 04 Dec 2009 17:33:01 UT

In my excel file, I have to add a certain number of days to any given date.
If the end date ends up on a weekend, I need to round it up to the next week
day. For example. The contractor timing is 20 calendar days (Not business
days). The start date may vary, but the Contractor timing is set at 20 days.

referencing text in an IF statement

michellethomp...@discussions.microsoft.com (Michelle Thompson) — Fri, 04 Dec 2009 17:09:02 UT

I am trying to use an IF statement to look for text in range of cells but
cannot get it to return the value I want I think because I'm searching for
text not a value. If any cell in a column contains the text "Error" I want
the cell I'm in to also read "Error". Here's the formula I have but it won't

When do I use quotation marks in an Excel formula?

d...@discussions.microsoft.com (Deb) — Fri, 04 Dec 2009 16:40:01 UT

What are the guidelines as to when you do and do not use quotation marks in
the criteria section of a function.

Static current date based on when data in another column was enter

dan...@discussions.microsoft.com (Daniel) — Fri, 04 Dec 2009 16:09:01 UT

I want to setup a date column that each cell in the date column reflects the
date that another column in the same row was filled in.
Example: I enter a numerical value in cell D5 that is greater than 0. I want
A5 to record the date that I entered that data.
Also I would like to do the same thing with time in cell B5. How could I do

Match and combine multiple rows into one

b...@uchc.edu (C.) — Fri, 04 Dec 2009 15:50:23 UT

Hi all,
I have the data set below in three separate columns in one worksheet
A3 B3 C3
10 71 120171
10 71 43311
10 71 43312
10 72 101321
10 72 10372
1 421 42101
1 421 42102
I want the data to just match and return the first two rows and
combine it into one cell in another worksheet so it would look like

Testing for an Integer result in a formula

je...@guisinger.com (JerryG) — Fri, 04 Dec 2009 15:23:01 UT

I'm writing a nested IF statement in which one step needs to test the value
of one cell divided by another cell, and determine if it is an integer before
it completes the calculation.
So, IF cell R10 / I13 = INTEGER, then I'll perform a Future Value calc. IF
NOT = I want to do nothing.
I tried ISEVEN, but when I turned on the Analysis Took Pak nothing happened.

How do search a range to generate a value?

stevehick...@discussions.microsoft.com (Steve Hickman) — Fri, 04 Dec 2009 14:24:01 UT

Period 0-7 41852 15-21 22-28
Value Time to pay
1000 21 [1000]
500 4 [500]
I am trying to set up a worksheet so that once an individual selects a value
in the Time to pay column, a value will be generated in the relevant period.
I've shown the desired output in square brackets above. I've tried an IF

Help with Formula in Excel 2003

a...@discussions.microsoft.com (Andy) — Fri, 04 Dec 2009 13:54:01 UT

I have a column of data which I add to twice a day for one month.
I have a second column which calculates the daily average.
I need to calculate the difference between the last entry in the average
column & the first, placing the result into another cell.
What I can't find is how to make this a dynamic update.

can you have sub parts relating to a line

jji...@discussions.microsoft.com (JJINTY) — Fri, 04 Dec 2009 13:47:01 UT

when scanning drawing on to a worksheet, sometimes there are 10 or so
drawings under the same no, is there any way you can only see the first one
and then in some way see the hidden ones if they can be attached

Showing 0 after decimal with ROUND

li...@discussions.microsoft.com (Libby) — Fri, 04 Dec 2009 13:44:01 UT

Hi,
I'm using the ROUND function like this:
=ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
Unfortunately, if the numeral after the decimal is a 0, then it doesn't
display. How can I force a 0 to display after the decimal?
Libby

Summing the last 200 days

louisa.c.thomp...@googlemail.com (Louja) — Fri, 04 Dec 2009 13:42:24 UT

Hi,
I was wondering if anyone could help me - I need to add up the 200
most recent days for a spreadsheet and was wondering rather than
messing with cut and paste if there was a formula that could do it all
for me. I will have more than 200 days worth of info.
Also the sheet I am adding up on has gaps between the weeks so its not

Excel file saved as txt with justified column numbers when viewed in a txt viewer

m3wei...@gmail.com (Mike Weiler) — Fri, 04 Dec 2009 13:30:01 UT

I was wondering if its possible/how to export an excel file to txt
format and make sure that the first column in excel will start be from
column 1-10 on a txt viewer and the second column would be from 11-20
in the txt viewer. If anyone could help me with this, I would really
appreciate it.

Count

c...@discussions.microsoft.com (CWH) — Fri, 04 Dec 2009 12:44:01 UT

I am using Excel 2003.
I have the following in a worksheet
TEACHING LUNCH ASC HC ASC
01/Aug/10 Sunday 0.00 0.00 0.00 0.00 0.00
02/Aug/10 Monday HC
03/Aug/10 Tuesday HC
04/Aug/10 Wednesday HC
05/Aug/10 Thursday HC
06/Aug/10 Friday HC
07/Aug/10 Saturday 0.00 0.00 0.00 0.00 0.00

a quick way of inserting and naming multiple worksheets in Excel?

s...@discussions.microsoft.com (Sue) — Fri, 04 Dec 2009 12:26:01 UT

We have to insert 20 worksheets that each have a different name into an
existing Excel Workbook - is there a quick way of doing this?
Please help if you can
Sue

If Function

ham...@discussions.microsoft.com (Hammer) — Fri, 04 Dec 2009 12:06:01 UT

I want a cell to show the highest value based on the criteria below, Could
somebody provide me with a simple formula to show this, I presume I would
have to use the If Function.
For example
If cell F11 = £200 & Cell N11 = £146 I would like cell O11 to return the
highest value (£200)

Index & Match Question

stringhaus...@discussions.microsoft.com (Stringhaussen) — Fri, 04 Dec 2009 11:39:01 UT

Hi,
Getting a little confused on how to do this one. I have a list of raw data,
in column A I have a list of categories, in B a list of countries, C has a
list of teams and D has a list of values that I need pulling across on to
another spreadsheet like the one below. I'm pretty sure I have to do an Index

Problem with AND function

vram...@discussions.microsoft.com (VRamsay) — Fri, 04 Dec 2009 11:36:01 UT

I'm using the function as shown below
=IF(AND((COUNTBLANK(O14:AE14)< 17),L14>0),N14*L14, "enter project")
essential =IF(AND(TRUE, TRUE), do this, else "enter project"
however, I'm getting "enter project" even if only one of the options is TRUE.
I thought the whole point of AND was that BOTH had to be TRUE?

How to reverse an Excel formula?

ni...@options.net.pl (Nige) — Fri, 04 Dec 2009 11:34:05 UT

Hi
I'm hoping somebody can help me with a little problem.
The scenario is this:
I'm playing an online game in which players are able to scout villages
for resources, troops, etc. However, if the opposing player has x
number of scouts present in their village, then y number of my scouts
will be destroyed and the scouting attempt is detected. Attacking

How to set a cell to expand to fit text in Excel not shrink fit

eli...@discussions.microsoft.com (Elisal) — Fri, 04 Dec 2009 11:16:05 UT

I'm not sure it this is doable but is it possible to set a cell so that it
automatically displays all the text that is in there, expanding where
necessary without manual changes.
i don't want shrink to fit as I need to text to be of a certain size

Index match issue

stringhaus...@discussions.microsoft.com (Stringhaussen) — Fri, 04 Dec 2009 10:25:01 UT

VBA for document formatting - HELP.

drjchamberl...@gmail.com (JRC) — Fri, 04 Dec 2009 10:01:11 UT

Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file.
My system is a Mac G5 running the latest version of Leopard (not Snow

How to reference cell that changes locations.

arrdubbsm...@discussions.microsoft.com (ArrDubbSmith) — Fri, 04 Dec 2009 09:40:01 UT

Here is a portion of my table:
Shane $44,476.28 90.9
$67,103.19 49.2
$105,943.68 94.9
$59,601.69 92.7
$63,188.49 87.4
$81,969.00 91.6
$74,373.28 107.8
$83,153.10 91.3
$50,764.05 62.6
$62,418.70 82.8
$61,355.07 74.4
$69,577.73 87.4
Total $923,083.30 1128.8
Brantley $71,818.41 83.3

Excel 2007 - Change link source requiring multiple entry of passwo

glenmil...@discussions.microsoft.com (Glen Miller) — Fri, 04 Dec 2009 09:06:01 UT

I have an issue with repointing links in Excel 2007:
When I change the link source to repoint to a different file, Excel is
asking for the source file pasword to be entered for each cell in the file I
am linking from.
This is causing major time issues in our month-end reporting:
I have a management accounts file which is linked to a large number of other

Calculation / Format Error

j...@discussions.microsoft.com (Jim) — Fri, 04 Dec 2009 05:21:01 UT

I'm using this formula in cell AE69
=IF(AL69="NO","",AE68*(AM69/10 0))
When the logic is true I want the cell value to be zero, but show as blank.
When this cell is summed up with others, a #VALUE! is returned. I am
assuming excel is seeing this cell as text and thus returning an error.
I have tried adjusting the formula to:

Excel Rounding of Formular results

Fri, 04 Dec 2009 05:05:03 UT

I am converting minutes to Hrs & Secs but having trouble!
I have the below forumla however the minutes keep rounding out to 10 or more decimal places.
=INT(V23/60)&"hrs "&MOD(V23,60)&"mins"
Cell V23 = 239
Cell W23 is using the above formular which keeps spitting out heaps of decimal places for minutes

show ink on an excel file.

j...@discussions.microsoft.com (Jean) — Fri, 04 Dec 2009 00:28:01 UT

How do I show ink on an excel file? It is not highlighted.

sum accross worksheets using names

sha...@discussions.microsoft.com (ShaunL) — Thu, 03 Dec 2009 23:58:01 UT

It is clear this is allowed and works:
=SUM(Sheet1:Sheet5!A1)
As the formula suggests the cell A1 in each sheet from Sheet1 through Sheet5
is summed.
This does not seem to work:
=SUM(Sheet1:Sheet5!Name_Ref1)
Where Name_Ref1 is a name of a cell defined in each worksheet.
More broadly I want to sum a given cell accross worksheets but that cell is

Using SUMIF with dates

yowz...@discussions.microsoft.com (yowzers) — Thu, 03 Dec 2009 23:42:01 UT

Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09,
etc in column A and corresponding data in column B. Then I have a table on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like
to put in a SUMIF function where I can get totals for column B on sheet 1 by

??? can u put a word doc into a excel worksheet

dummiehand...@yahoo.com (excelquester) — Thu, 03 Dec 2009 23:36:01 UT

?? can u put a word doc into a excel worksheet

Is autofilter & sort disabled in protected cells?

munch...@discussions.microsoft.com (Munchkin) — Thu, 03 Dec 2009 23:01:02 UT

My spreadsheet is protected, but I have checked off the boxes to allow users
to use autofilter & sort, but since my cells are protected (because I don't
want anybody to change any records in the document) the autofilter sort
feature does not work. Is there any way around this? Thanks.

CALCULATE BETWEEN TWO DATES EXCLUDING WEEKENDS

rita4...@aol.com (CYNTHIA) — Thu, 03 Dec 2009 22:51:01 UT

A) B)
11/20/09 11/23/09
11/2/09 11/5/09
When I use the Network formula it says 4 days which is incorrect for the
11.20.09 date.
I use the formula NETWORKDAYS(A1,B1)

Conditional formula with text

stag...@hotmail.com (Mike) — Thu, 03 Dec 2009 22:40:02 UT

Hello, I'm trying to put together a formula in one worksheet that is
dependent on text existing in another worksheet.
Sheet 1
A1=Kentucky B1=(formula)
Sheet 2
A1=1 B1=Oregon
A2=2 B2=Kentucky
A3=3 B3=Arizona
Basically, I need to calculate a formula in Sheet 1 where if Sheet 2
contains "Kentucky" it would return the value in the "A" column next to

using conditional formatting with dates

j...@discussions.microsoft.com (Josh) — Thu, 03 Dec 2009 21:54:01 UT

I have a spreadsheet using excel 2003. I need the row to be one color if a
box is filled in with specific text. I have that. I now have 2 conditions
left to use. I need the row to turn one color when the date in the box is
between now and 30 days from now. Then I need it to turn another color when

more than 15 numbers in a cell?

ju...@discussions.microsoft.com (Julie) — Thu, 03 Dec 2009 21:44:02 UT

I am working with account numbers that have more than 15 digits. I am
working with office 2003 and after the 15 numbers it automatically makes the
numbers I enter a zero. I can't format the column to text for another reason,
does anyone know how I can format that cell to show all 20 numbers that I

how to format a column to corespond with another

jere...@discussions.microsoft.com (JeremyC.) — Thu, 03 Dec 2009 21:09:22 UT

I want to make column "E" read "N/A" when column "B" reads "0". How do i do it?

I have a column of many many numbers how can i split them up auto

ke...@discussions.microsoft.com (Kevin) — Thu, 03 Dec 2009 20:23:03 UT

I have a group of numbered data that I can split into different columns using
text to column. I now need to place a blank row in between each row of
diffeerent data. Can this be done

Scroll Lock for selected multiple sheets

keyrookie.55dc...@excelbanter.com (Keyrookie) — Thu, 03 Dec 2009 14:28:46 UT

Help please,
I'm trying to limit the scroll area of several sheets but I also need
other sheets to scroll normally. I'm using this formula in the VBA
(ThisWorkbook):
Private Sub WorkBook_Open()
Sheets("Jan").ScrollArea = "A1:M52"
End Sub
This works fine for the "Jan" sheet but I have several others that I

Postive # converted to Negative #

2ms...@discussions.microsoft.com (2MShad) — Thu, 03 Dec 2009 19:43:01 UT

I want to be able to enter a positive # in a cell and have it display as a
negative? Help

macros vba question

dlotzuafc...@discussions.microsoft.com (dlotz @ uafc. com) — Thu, 03 Dec 2009 19:40:01 UT

is there a way that when I save as a file.
If a cell in a worksheet says Week 1 it will copy and paste with vaules a col
if if says week 2 is will copy and paste with values a different col in the
worksheet

Get part of field name for highest value

lornaj...@discussions.microsoft.com (Lorna_Jane) — Thu, 03 Dec 2009 19:31:01 UT

Hello, I have a spreadsheet which looks like the following:
AF AG AH
PercentRegion1 PercentRegion2 PercentRegion3 <-- Column header
100 0 0
25 75 0
1 0 99

Why are my numbers disappearing in excel yet it totals them?

donnastev...@discussions.microsoft.com (Donna Stevens) — Thu, 03 Dec 2009 19:26:01 UT

I have a spreadsheet that I have filled out the individual cells with number.
These cells are totaling correctly, however when I open the spreadsheet
the individual number I entered are showing blank.... I moved my mouse
around in the spreadhsheet and all of a sudden the numbers appeared and then

Have the need to sort text code-values within same Excel cell

a.x...@discussions.microsoft.com (A. X M. D) — Thu, 03 Dec 2009 18:30:01 UT

Got column with thousands of cells containg different codes, e.g., cell P357
--> {X, A, B2, T83, M19}; cell P425 --> {T83, A, X, M19, B2}, etc ... Need
a way to sort these individual cells in a similar order so I can aggregate
the number of cells containing the same set of codes/values ...
I am somehow familiar with the basic Excel functionality but above situation

Function that meets table requirement then multiplies by table fac

goo...@discussions.microsoft.com (goonie) — Thu, 03 Dec 2009 18:13:01 UT

I'm looking for a function that will reference a cell then lookup that cell
in a table and use the multiplier in the table. I'm not explaining very well
so Im hoping that my example will help you understand:
Column
A b c
Sector P&L w/o Admin P&L w/ Admin

If formula with table look up

goo...@discussions.microsoft.com (goonie) — Thu, 03 Dec 2009 18:01:01 UT

I have multiple data in colums and I want to have a formula that will let me
do an if multipler. I'm using excel 2003. Example:
A B C
Sector P&L w/o Admin P&L w/ Admin
PC 517.9
PC 0.0
PC 0.0
TD 1,993.6
TD (2,508.0)
TS 431.3
TS 355.7
Reference Table:

How toEmail a spreadsheet and preserve the Data List format

m...@discussions.microsoft.com (MWK) — Thu, 03 Dec 2009 16:41:02 UT

I have created a data list and want to email it to someone. When I try to do
this, the list reverts to the original spreadsheet and does not preserve the
list (sorted) format. I am using Excel 2003 and XP. Thanks for any
assistance.

Re: Extract names from string based on value list

demecha...@yahoo.com (Max) — Thu, 03 Dec 2009 15:21:01 UT

One way
Assume productnames running in A2 down
Assume D2:D10 contains all the brands text
In B2, normal ENTER:
=INDEX($D$2:$D$10,MATCH(TRUE,I NDEX(ISNUMBER(SEARCH($D$2:$D$1 0,A2)),),0))
Copy down to return required results
If you need an error trap to return neat looking blanks for unmatched cases
(if any), use this in B2, normal ENTER, copied down:

Excel spreadsheets

angele...@discussions.microsoft.com (AngelEyes) — Thu, 03 Dec 2009 14:38:01 UT

how do i copy data from one sheet to another so that if the info changes on
the first sheet it will automatically change on the second?

How to edit text for check boxes

zack3...@discussions.microsoft.com (zack3317) — Thu, 03 Dec 2009 14:27:01 UT

wondering how to edit the text of a check box and if there is a way to
increase the size of the box itself?

Stripping out characters

stepha...@discussions.microsoft.com (Stephanie) — Thu, 03 Dec 2009 14:26:01 UT

Hi-
I have a field that contains email addresses. I would like to strip the
address down to just the domain:
stepha...@gmail.com = gmail
victor.jo...@comcast.net = comcast
sally...@aol.com = aol
So essentially I want to capture anything after the "@", up to the "."
I'd appreciate your suggestions!

2007 not auto update 2003 links

carou...@discussions.microsoft.com (carourke) — Thu, 03 Dec 2009 13:41:01 UT

I have several workbooks that are linked. THe workbooks were created in
2003. Our office upgraded to 2007 and the worksheets run in compatibility
mode. The workbooks need to auto update. I have set the Excel trust Center
settings to automatically update all links in all workbooks. However I have

Can I use the NOW() function in a formula?

smith...@discussions.microsoft.com (Smith512) — Thu, 03 Dec 2009 13:40:01 UT

I have a cell B1 that has NOW() time and another cell B2 that has NOW() date.
I am trying to have data from another cell F1 brought in to the destination
cell B3,
when a given date and time occur.
=(IF(AND(B1="23:00:00",B2="12/ 03/2009"),F1," ")
This formulas does not work.
The Clock is continuously active / always changing.

Complete Tax Years

d...@discussions.microsoft.com (Dan) — Thu, 03 Dec 2009 13:01:01 UT

I need to calculate the number of complete UK tax years between two dates e.g.
To: 01/03/1998
= 2 complete tax years.
This is derived from:
01/08/1994 to 05/04/1995 - Not a complete year therefore ignored
06/04/1995 to 05/04/1996 - Complete tax year therefore counted
06/04/1996 to 05/04/1997 - Complete tax year therefore counted

Counting calendar days

mushabs...@discussions.microsoft.com (musha-bsuha) — Thu, 03 Dec 2009 10:44:04 UT

When I count calendar days, using =VALUE(B2-A2) or even just =(B2-A2), it
always omits one day. For instance 28/09/2009 to 30/09/2009 the formula
counts it as 2 days, while actually it's 3 days (28-29-30). How can I correct
this?

How to make list of Items displayed in AutoFilter? Any Function?

so...@discussions.microsoft.com (Soham) — Thu, 03 Dec 2009 10:19:01 UT

Hi,
Dear I am beginner in EXCEL,
Can anybody explain me, How to make list of Items displayed in AutoFilter?
Any Function?
Currently I am doing manually by each individual items using autofilter,then
Copy-paste. This consumes lot of time and hundreds of items are there. e.g.
Datasheet
Apple 1
Apple 2

Hyperlink problems

deputykjmcn...@discussions.microsoft.com (Deputy K. J. McNatt) — Thu, 03 Dec 2009 09:51:02 UT

I am running Excel 2007. I have a spreadsheet that I have hyperlinks to .BMP
files. I have my computer setting to use MS Picture Viewer as default
program, but when I click the link from within Excel it uses MS Paint to open
the file. How can I set it to be MS Picture Viewer?

Pivot Table and External Data

char...@discussions.microsoft.com (Charles) — Thu, 03 Dec 2009 09:33:01 UT

Hi
I am using excel 2002. Our company has just converted to SAP and I am
attempting to set up Templates containing Pivot Tables. I created a Template
using SAP data, cleared the data while leaving the Headers, located the
cursor in cell A2 that contained the SAP data and saved the Template.
When I run SAP and tell it to populate the Pivot Table, it correctly opens

How do I retrieve data from 1 hour ago?

dodg...@discussions.microsoft.com (Dodgers) — Thu, 03 Dec 2009 08:42:01 UT

I have created a large data base and was sorting into categories. I have
sorted some of the data without names and want to retrieve the data base from
1 hour prior to realising my mistake. Am I able to do this? I am using Vista
Basic exel.

Buy KING EDWARD CIGARILLOS Online

principal.rio...@gmail.com (Sasha) — Thu, 03 Dec 2009 08:04:15 UT

We have LOW prices on King Edward Cigarillos. This is a great
cigarillo top brand name with a great flavor. Interested in King
Edward Cigarillos, then visit our online cigarettes, tobacco and
cigarillos shop [link] and make your
choice. Quick and fast UK and Europe free shipping!

how to attach file

vmohan1...@discussions.microsoft.com (vmohan1978) — Thu, 03 Dec 2009 04:34:01 UT

Dear all ,
Please let me know how to attach the file , in that way i can explain my
problem in better way

Data validation dependant drop down on unsorted list

reache...@hotmale.nospam.com (Reacher21) — Thu, 03 Dec 2009 04:34:02 UT

I can't see a solution on contextures.com or here so hoping someone can put
me out of my misery!
I have a master list of vessel names on Sheet1 Column A. On Sheet2 is my
data repository where I record all voyages of vessels I'm tracking and some
vessels make multiple voyages differentiated by a different departure date.

Return last and second last "populated" cell in column

granttuc...@discussions.microsoft.com (Grant Tucker) — Thu, 03 Dec 2009 03:28:01 UT

Display last and second last values from "populated" cells in a column. Say
A1:A10 with results in A11 and A12.
This could apply where you want to generate the number of hours of work in
each of the last two working weeks in a 10 week period. Not all 10 weeks will
have hours; some will be blank. The formula should remain true in another 10

counting words

s...@discussions.microsoft.com (scot) — Thu, 03 Dec 2009 03:12:27 UT

I'm trying to count how many times the word, "false" appears in a coloumn
range of: F1:F2500
I've formated the "F" coloumn to analize and put either a, false or true, if
the coloumns, "A1:2500 and B1:2500" do or don't match.
Now I'd like to count, sort, find all the falses. Too many with a list of

Column header for Max value? Index/array function?

ceci...@discussions.microsoft.com (Cecilia) — Wed, 02 Dec 2009 23:33:01 UT

Hello--
I have a series of columns for which I'm trying to find the column header
for the max value. For example:
A B C D E F
Store 1 Store 2 Store 3 Store 4
Row 1 1 9 60 72 Store 4

Autofilter unavailable

juliee...@discussions.microsoft.com (Julieeeee) — Wed, 02 Dec 2009 22:53:01 UT

The Autofilter option is not available in the sheet I am working in. It is
grayed out. The file is not protected. Any ideas? (I'm using 2003)

Copying formulas in Columns and transpose to rows?

te...@discussions.microsoft.com (Terri) — Wed, 02 Dec 2009 22:05:01 UT

I have a 2007 worksheet that has formulas in a Column:
=Tally!D10
=Tally!D11
=Tally!D12
How do I transpose the formula columns to rows like:
==Tally!D10 =Tally!D11 =Tally!D12
Now when I copy and Paste special (Transpose) is translates like:
==Tally!D10 =Tally!E10 =Tally!F10
Which is not the data I need?

Distribution of a Value

a...@discussions.microsoft.com (AK) — Wed, 02 Dec 2009 21:53:01 UT

Need some direction...
I am trying to develop a bell curve that will spread a value over a number
of periods.
Example:
500 widgets across 62 days, how to determine the number of widgets on day 32
or 36, etc?
Thanks

Use Formula from another worksheet

cjwag...@discussions.microsoft.com (cjwagner) — Wed, 02 Dec 2009 21:49:01 UT

I have a workbook with 14 worksheets.
Sheet 1 Cell A5 C6*B7
Sheet 3 Cell C6 5
Sheet 3 Cell B7 9
Sheet 3 Cell D15 4
Sheet 3 Cell A5 = ?????
Want Sheet 3 Cell 5 to look at Sheet 1 Cell A5 get the formula (C6*B7) and
use the numbers from the cells C5 and B7 from Sheet 3 and end up with 45 in

Web Excel and Excel Functions

lwil...@discussions.microsoft.com (LWilson) — Wed, 02 Dec 2009 21:41:03 UT

I’m working with a worksheet using Web Excel or CPM (Everest). As such I’m
combining functions from excel and web excel. Web Excel recognizes dates in
the following formats:
2009.Nov, FY2010Total (This represents my fiscal year which ends on 6/30
each year), 2009.Nov.30, CY2010Total (normal calendar year), and of course

Outlook CPM and Excel

lwil...@discussions.microsoft.com (LWilson) — Wed, 02 Dec 2009 21:03:01 UT

Does anyone have knowledge of combining Outlook CPM (Everest) and Excel
functions together?

return value from range of worksheets

ag...@discussions.microsoft.com (aglen) — Wed, 02 Dec 2009 20:41:02 UT

I am trying to return the name of a person who worked the most hours in my
volunteer table. The columns include the person's name, date worked, number
of hours per shift and total hours worked. There are 100+ people that each
have their own worksheet. I would like to use a lookup to return the name of