I have 'hours worked' as a row in a pivot table. I use the drop down arrow
to filter/deselect the data I don't want, everything is fine and the totals
are adjusted correctly.
Lets say I only want the PT to show 'hours worked' >10. So I deselect what
I don't need (everything <10). I now know this PT to be 'hours worked
greater than 10' but I don't want to see each instance of 'hours worked'
10.5, 11.75 etc etc , just the totals by department (dept is also in data).
But when I (right click and) hide the column it reverts back to t...Have user input converted to uppercase in same cell as input?
Noob Alert! Very new to spreadsheets! I have a simple form that requires
data selected from a text description to be entered in a cell to give us a
special product code.
The code is upper case in the description but ppl are entering it in lower
case, I need to convert the entry to uppercase to match our entry system, but
I need a function to convert and display the converted text in the original
I looked at =UPPER function but that displays converted text elsewhere, if I
use it in the same cell as input I want converted I get the circular error
Can someone perhaps ...Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells
G170 is a cell that contails a sum (if cells above are filled it it will add
I170 contains a cost
if G170 & i170 are blank then do nothing
If G170 is greater than 0 and I170 is greater than 0 then again do nothing
but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made"
Can anyone help all I get is FALSE
I have tried =if(g170<=0, and (i170=<0,No Charge made",""))
=IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...How to Move First Row?
Hi I�ve just created a spreadsheet in Excel which goes into around 2
different columns now in my first columns I have a list of names whic
I want to appear at the beginning of each page as I scroll across m
Spreadsheet I was wondering if this is possible and if so How man
chiefbrutalis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3530
View this thread: http://www.excelforum.com/showthread.php?threadid=55080
Check out Window>Freeze Panes feature for locking rows ...Summing Data based upon a condition
Trying to "Sum" data in one column (A) based upon a condition in another
column (B)....... Any help?
Take a look SUMIF function in help menu
> Trying to "Sum" data in one column (A) based upon a condition in another
> column (B)....... Any help?
Let's assume you want to sum column B if column A equals customer "A"
> Trying to &q...Create a cell with only PART of another cells data
I have a column (D) with the following formatted data
I want to create a cell (column) that ONLY contains the last 4 digits
Can some one please tell me how to accomplish this.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4443 (20090921) __________
The message was checked by ESET NOD32 Antivirus.
assuming the data starts on row 1. If you want it as a number, then
you can do this:
Copy down as required.
Hope this helps....How do I edit directly in a cell
In earlier versions pressing F2 enabled editing directly in a cell. How do I
do this in 2007
Office Button > Excel Options > Advanced > Allow editing directly in cells
Then F2 will work as desired.
Gary''s Student - gsnu201001
> In earlier versions pressing F2 enabled editing directly in a cell. How do I
> do this in 2007
The shortcut-key remains same for XL2007
> In earlier versions pressing F2 enabled editing directly in a cell. How do I
> do this in 2007
...When entering data into excel spreadsheet cell, the page just jump
I am trying to add data to an existing worksheet this morning. I can click
on the clel, but when I try to enter the data, the worksheet "jumps", am
unable to enter any data. What have I done wrong?
If you are making entries using the numeric keypad, it sounds like you turned
your Num Lock off. Try pressing NUM LOCK (there should be alight that comes
on above it when you do) & try again.
> I am trying to add data to an existing worksheet this morning. I can click
> on the clel, but when I try to enter the data, the worksheet &qu...optimising column width & row height
I have a table whose cells contain text. The lengths of the text strings are
unpredictable, and vary quite widely between different records.
To minimise the overall size of the table (for printing) I use 'wrap text'
, and I juggle column width and row height by double-clicking on the
row/column boundaries. This works, but I cannot seem to find an optimum, and
every time I add new records I have to start again.
Any ideas how to optimise the table size please ?
How many columns of data is involved and what restrictions do you want
to set on the number...cell color does not show
When I fill a cell with color the color does not show on
my worksheet but when I change to print preview I can see
the color. How can I fix this?
If the high contrast setting is turned on you won't see the fill colour.
There's information in the following MSKB article:
OFF: Changes to Fill Colour and Fill Pattern Are Not Displayed
> When I fill a cell with color the color does not show on
> my worksheet but when I change to print preview I can see
> the color. How can I fix this?
Excel FA...Cell formating
How do I format a cell such that I see a number say 40000000.00 as
IndianGuru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27382
View this thread: http://www.excelforum.com/showthread.php?threadid=469097
Click Format then Cells. Select the Number tab.
Click on Custom in the category box and in the Type box type #,###.00
"IndianGuru" <IndianGuru.email@example.com> wrote
in message news:IndianGuru.1vngya_11272...converting general cells format to text
I'm having a hard time converting already existing general cells into text
format. I'm trying to change it into text format so SAS will be able to
recognize it. Without the "green indicator", SAS, for some reason, will treat
it as a "general" cell even though I've saved it and changed the cells into
text. Any suggestions?
Sometimes when you type a number into a cell and then change the format to
text, it does not "set in" imediately. Hit F2 and then enter on each cell
and it should change it for you. Hope this helps.
"junkgrrl" wrot...Locking Named Cells
I have a lot of named cells in a workbook. Can I lock the name of th
cells but allow users to change the data in them?
I do not want them to be able to change the cell names becuase o
references to them elsewhere in the workbooks.
wjoc1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1528
View this thread: http://www.excelforum.com/showthread.php?threadid=47473
Are you sure that the users are changing the names? Maybe they're just adding
another name for that range...Compare cells (advanced)
I need to find a smart solution for comparing cells in an Excel file.
To make a long story short, we usually receive excel sheets with
thousands of rows. From a certain data there we make our own
extraction from our database and put that in the same sheet, next to
the other data.
Then we need to compare some data, to make sure that it is correct.
Lets say that we are comparing names. The problem is that VERY often
the two cells containing the names are not _exactly_ the same. There
might for example be one letter misspelled in one of the cells e.g.
(Mike Brown/Mike Bruwn) or in one ...Using a VLookup "type" reference but the result is the CELL not th
I'm using a VLOOKUP to help me select a date range from another work sheet.
ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10.
Any help would be greatly appreciated
try the following
=SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)...Distinct seems to use more CPU resources
I am trying to analyse the Profilers results whether taking out DISTINCT
from my SELECT statement (with 4 unions) averages the CPU a little higher
and the Reads a little lower. The Writes and Duration are about the same.
Here are some of the results. This is running about 10 times each.
In the Without Distinct, CPU was always 109 or 110 about the same
distribution and he Reads were mainly around 7583.
In the Wiith Distinct, CPU was had 3 @93 and the rest around 109 and the
Reads were mostly around 7610.
Not that much difference.
CPU ...How do I insert a dot/check mark in a cell in Excel?
This is very basic! And I am having a problem... I want to insert a dot or
check mark in a cell. Where can I find a dot/check mark? I thought they can
be easily found, but I can't find them. Can anybody help?
See if this helps, Sue:
"sue" <firstname.lastname@example.org> wrote in message
> This is very basic! And I am having a problem... I want to insert a dot
> ...Naming sheets from a cell value
Thanks for reading my question.
Is it possible to name a sheet from a cell value with in the sheet? i.e. I
have 12 sheets, each sheet represents a month and the month value is in cell
A1. Can I rename a sheet by refering to cell A1 instead of manually renaming
the sheets to January, February etc.
In article <127785E3-01B4-49B1-9966-8D79F795EFA4@microsoft.com>,
"Tony4X4" <Tony4X4@discussions.microsoft.com> wrote:
> Hi there,
> Thanks for reading my question.
>...Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I
select each time and then have the first step of the macro
select the 12 cells immediately to the right of the cell I
select each time.
Currently my macro runs perfectly except that the macro
will only begin in the exact same cell every time the
marco is run. This means that the same 13 cells are used
by the macro every time. Where as I need the marco to
begin in a different cell that I more or less randomly
select and then the macro should select the 12 cells
immediately to the right of that cell.
I know someone out there...Deleting Cells Option doesn't appear
I have a workbook with several tabs. Each sheet has command buttons and
macros -- but the format is identical.
I want to delete a few cells (not rows, just cells)
On some of the tabs, I can select the cells, Edit / Delete / Shift Up
However, on some of the tabs, I can select the cells and I only get the
option to "Delete Row". I have compared the Options and other settings, and
can't find what would be doing this.
Nevermind -- I finally found it -- I had a Filter that was on. Apparently,
even if the filter is not in the area that you want to delete, it still
...Summary in the last row
I'm need to make a vba macro where the macro automaticly shall find
the last row and make a summary of the total column D. I cant get
It right, can someone please help?
So far I only has typed following.
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lngLastRow + 1) = _
dim mc as long
Dim lr As Long
mc = 4 'col D
lr = Cells(Rows.Count, mc).End(xlUp).Row
Cells(lr + 1, mc) = _
Application.Sum(Range(Cells(1, mc), Cells(lr, mc)))
Don Guill...How to specify a fixed cell in a formula
Perhaps I am explaining myself in the wrong termonology but when I drag a
cell into multiple cells below it
the formula changes cells. How do I make it from changing cells.
Dave, you need to use a absolute, or mixed reference, form excel help
Switch between relative, absolute, and mixed references
Select the cell that contains the formula.
In the formula bar , select the reference you want to change.
Press F4 to toggle through the combinations. The "Changes To" column
reflects how a reference type updates if a formula containing the reference
is copied two cells down an...Freezing cells #4
I have some cells with numbers that rely on formulas with random numbers. Is
there a way to save those values and make the cells independent of the
formulas used to make them so that they do not continue to recalculate every
time I type something?
You can convert them into numbers.
Copy the cells with the formulas.
Then choose Paste Special on the Edit menu. Click Values and click OK.
> I have some cells with numbers that rely on formulas with random numbers. Is
> there a way to save those values and make the cells independent of the
> formul...Cells that won't Lock!
I am using Excel 2007 and I need to lock two cells to prevent anyone
removing the formulae.
I highlight the two cells and on the Home tab / Cells / Format, I click on
'Lock' in the drop-down menu.
However, having done all this I can still over-type in those cells and the
What am I doing wrong?
Cells are locked by default. So, locking the cells does nothing until you
protect the sheet. Right click sheet tab>protection.
Microsoft MVP Excel
"Noel S Pamfree" <Noel.spamfree@hotm...Sum formula #3
Im trying to sum a column, but I only want to sum values
were there is a certain value in another column, ie I
want to sum each value in one column where it does not =
R in the other column.
(When sending e-mail, use address email@example.com)
"MRG" <firstname.lastname@example.org> wrote in message
> Im trying to sum a column, but I only want to sum values
> were there is a certain value in another column, ie I
> want to sum each valu...