00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very
well thanks to RD
All I need to do now is sort out the date this is copied from sheet1 onto
the totals page and works fine when the date is put into sheet1.
However if the cell is blank on sheet2 the date is put into the totals sheet
as 00/01/1900 when the date is put in sheet2 all is well with the right date
entered into the totals sheet.
So how do I get the totals date to stay blank until the weekly sheets have a
date put into the sheet please.
the format I am using is below
many thanks for all the help I ...How do use the if formula
How do I use an IF formula that displays PASSING if the average of the
project grades is at or above 36 points and UNSATISFACTORY if that is not the
if each score is in cells A1 to A4 then use =IF(AVERAGE(A1:A4)>=36,"PASSING
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get
Ice Hockey rules (especially the Wightlink Raiders)
> How do I use an IF formula that displays PASSING if the average of the
> project grades is at or above 36 points and UNSATIS...Need to reference a block of cells from another sheet.
apologies if this is probably a mundane question, but I didn't know what
'search terms' to put in google to return any results..
I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet)
to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet),
tabs 1-11 (sheets) are changed too.. the cells themselves will never move
location, just the data within them..
Is it possible?
On your sheet1 select the cell that should receive the data from Sheet12;
Hold down the Shift key and Click on Sheet11 << this shoul...Cell Value as Named Range Reference
Little bit of a quirky question...
Trying to use a cell value as a reference in a formula, where that
cell value is the name of a named range.
So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.
I want to get the correlation vale for A1:A3 and B1:B3
So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
formula: =correl(D1,D2). But I get an error. Have also tried using
Indirect to no avail.
Any help would be hugely appreciated. Thank you.
http://www.wimgielis.be = Excel/VBA, soccer and music
"ste...I need to increase a number which is held in a cell by 1
How do i increase a cell which has a number in it, eg, cell a8 has 4
i want to run a If statment that looks at another cell and if true i want
cell a8 to increse to 5
You need VBA.
The code is simple in itself (Range("A1").Value = Range("A1").Value + 1),
but what will trigger the addition to happen.
"Steve" <Steve@discussions.microsoft.com> wrote in message
> How do i increase a cell which has a number in it, eg, cell a8 has 4
> i want to run a If sta...refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could
apply to both
please could you take a look at the following post
Is there some reason you can't post your question here instead of asking us
to look elsewhere?
Microsoft MVP Excel
"Blinds Nottingham" <firstname.lastname@example.org> wrote in
news:email@example.com...Coping with missing year value in date data type field (Rephrased/Repost)
Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal with
this? I can think of two, but...
1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow
the use of a date/time data type and be fairly easy to implement, but it
sure feels like I would be breaking some kind of relational rule to enter
2. Create three text fields (txtDay, txtMonth, txtYear). This would allow
the use of...moving to next cell using "Enter"
With Excel XP (Windows EX also) Excel seems to have a problem
remembering what direction to move the focus when the Enter key is hit. Is
there a fix or a way of keeping it the same?
Go to Tools->Options and choose Edit tab. In that choose the direction
of the Enter key to the way you want.
Joseph Meehan wrote:
> With Excel XP (Windows EX also) Excel seems to have a problem
> remembering what direction to move the focus when the Enter key is hit. Is
> there a fix or a way of keeping it the same?
>...What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under
the Inventory account and Credit for COGS account in the Transaction Entry
Zoom. We're trying to figure out where that amount came from but we couldn't
zoom more than what it's currently shown. In both fields, Reference and
Distribution Reference, it's shown IVA#######... that is unknown to or setup
by us in anywhere, i.e. Audit Trail, Source Document.
Does this amount affect the dollar in the accounts mentioned?
And can anyone please help to where we could drill down the IVA details?...protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this?
select the cells for which you want to allow entries. After this goto
'Format - cells - Protection&...How to move cells to a another Cell?
I hope someone can help me on this. I am trying to move cells from a group
into a top cell, the initial view of column A (very simplistic) of the cell
looks like this:
What I want to do is to move 2 3 4 from the first group to the cell that
contains the 1 (first entry) and continue with the rest of the cells so at
the end columnA will look like this:
1 2 3 4
1 2 2 2
The cells vary in lenght and I have several spreasheets with LARGE amounts
of data that I have to processed, so I am hoping that there is a macro (VB)
that is able to do th...Image in a cell
Is it possible to place an image in a cell? If so, can you tell me how to do
No. Images are placed in the Drawing Layer "above" the worksheet. Cells
can contain formulae or data.
If you size an image to exactly cover a cell, you can set the Move and
Size with Cells property (right-click on the image, choose Format
Picture/Properties and select the appropriate radio button) so that the
image remains "on top" of the cell.
In article <6463B5FA-3D07-43B9-90EE-7A5143977C1B@microsoft.com>,
"Mark" <Mark@discussions.microsoft.com> wrote:
> Is ...Need a formula...
I have been given a task to collect info and create several spreadsheets
but I have one more formula to create. Here is it...
The formula has to callers receiving points for the amount of Western
Union sales they have for the month. Let say the entire department
AVERAGE (this number will change every month) was 161 sales; then 161
would be the median.
If the caller had 171 - 180 sales then he or she gets +1 point.
If the caller had 181 - 190 sales the caller gets 2 points, etc..
until the mark of 321-330 in which they get 16 points.
So, if I enter the number 255 in the cell then the an...how do I merge two adjacent cells and keep the data from both cell
To take it a step further, I also need the formats to remain the same.
ie...the left column data is Left-text-justified, and the right column is
=A1 & " " & B1
"johncaleb" <firstname.lastname@example.org> wrote in message
> To take it a step further, I also need the formats to remain the same.
> ie...the left column data is Left-text-justified, and the right column is
...more than 4 color choses for cell/colum
I an new to excess so please show me how this is to be done
I would like to put a letter in a cell but all in the same colum.
"A" and get a color ( dont matter what color )
"B" and get a color ( dont matter what color )
"C" and get a color ( dont matter what color )
"D" and get a color ( dont matter what color )
"E" and get a color ( dont matter what color )
Message posted from http://www.ExcelForum.com
only possible with VBA as conditional formating only supports 3
conditi...Date Formula #2
I am trying to write a formula that works off what is in two separate cells.
It has several parts and maybe not all of them can be done. It would be
great if someone could help me out. Here goes.
If A2 & A3 each have a "N" in it then I want cell A5 to show today's date
plus 20 weeks. However, if only 1 of the cells A2 or A3 has a "N" in I still
want A5 to show today's date plus 20 weeks.
Can this also be done? If both A2 & A3 have "Y" in them then I want A5 to
freeze with the date last show when 1 of the two cells had a "N" it ...macro button refers to prev. workbook?
When I create a macro in Excel, and assign a button to it, it works
fine the first time, but when I re-open the workbook for a second
time, and try to run the macro again, the macro seems to refer to the
previous workbook...For some reason, the macro saves the name of the
Dim name As String
Dim adress As String
name = ActiveSheet.Range("B6").Value
adress = ActiveSheet.Range("B4").Value
ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...Data Validation in cell
I have a sales order form with the equipment on the second sheet as "PartNo"
"Description" "Cost" selected each column of data then clicked in the
cellname box and called them "PartNo" "Description" "Cost"
On the sales order form I have the columns "Qty" PartNo" "Description"
"Serial No" "QtyDes" "Price" Total"
I have set up data validation for the description as a list with
=Description what I want it to do is display the part no in the part number
Column and the cost in...Change Value axis to category axis
Is there a way to change places of the category (x) axis and the value (y)
axis? If so, how? I am using Excel 2003.
You can only make this switch if you change a column chart to a bar chart.
But if you want a line chart that's rotated, you can fake it:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
"Ronnie" <Ronnie@discussions.microsoft.com> wrote in message
> Is there a...deleting duplicated cells in a list via advanced autofilter?
I have a list which I would like to filter and deleted duplicate
A1: A250= account numbers
C1:"" = loan balances
D1:"" = notes
I'd like to be able to delete the duplicate account numbers but stil
show the value, loan balances, and notes that belong to the accounts.
Can anyone help. Thanks. Pete:
petevang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2503
View this thread: http://www.excelforum.com/showthread.php?threadi...Formula #10
Formula in C3 is =IF(E3>D3/12,E3,D3/12).
I would like to have the cell (d3 or e3) where the data entered last control
the answer in c3.
Put this in C3 instead.....
Vaya con Dios,
"Val" <Val@discussions.microsoft.com> wrote in message
> Formula in C3 is =IF(E3>D3/12,E3,D3/12).
> I would like to have the cell (d3 or e3) where the data entered last
> the answer in c3.
...Empty Cells #3
I export data from a program called OpenView Service Desk into a
spreadsheet. This spreadsheet is then imported into Access and queries
performed on the imported data. This data is then copied and pasted
into a different spreadsheet where calculations are performed. In one
particular spreadsheet there are 2 columns, one complete with data (no
empty cells) and the other which may contain empty cells. I need to
count the number of "empty cells" however the problem is, these cells,
although they appear empty, are not. I need to but cannot find the
content(s) of this cell(s). How do I fi...i need halp with a formula
i am trying to make a formula that says "if text in cell A1 is "apple" then
add number in cell A3 to cell A2"
If Text in cell A1 is Orange then add number in cell A3 to Cell A2
That looks like the same action to me.
Where does the result go? If you want it in A2, you need code.
(remove nothere from the email address if mailing direct)
"detlghtpd" <email@example.com> wrote in message
> i am trying to make a formula that says "if text in cell A1 is "...Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references
Uncheck "row and column headers".
Good idea to spend a few minutes browsing through the various Tools>Options
tabs to see what else is available to toggle on/off.
Gord Dibben Excel MVP
On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com>
>How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...How do Macro to EDIT cells?
Am using MS Excel XP (2002). All I want to do is a short macro to, in
effect, press F2 (to EDIT the cell), then let's say delete the first
character of the cell and change it to let's say X, like this...
I just want to put the cursor on a given cell, click CNTL-A to run the
macro, and have it change one cell at a time. There are reasons why I can't
use the search and replace. Problem is... if I record the macro (turn it
on, press the key sequences, F2, Home, Delete, X, GoDownOneCell, ...