cell buffer and lining dollar amounts up over the
Is there a way to set text in from the sides of a cell? I've tried the left
indent but I want a right indent. Sort of a cell padding.
What I also want is to format a column so that dollar amounts to line up on
the decimal. Is there a way to get dollar amounts to line up vertically on
the decimal points?
The basic number formatting codes are in Help (search for number
format). In this case:
will line your numbers up with two digits to the right of the
You can add space using the _ character. It leaves enough space for
whatever follows it, so
I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.
Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL
Can you elaborate more about it? You mean cells or rows?
"peterh" wrot...Count cells in range that restarts at intervals
I have a table like this:
Row Name Y/N? No. of Y
1 Adam Y 1
2 Adam Y 2
3 Adam N 2
4 Adam N 2
1 Brian Y 1
2 Brian N 1
3 Brian N 1
4 Brian Y 2
Is there a formula I can put for "No. of Y" that counts the no. of times "Y"
appears for Adam from the first "Adam" row up to the current row? The
counting should restart for Brian and not accumulate from Adam's total.
Note that the starting point for each person is when "Row" = 1.
Can a pivot table help?
The -- is a double unary minus which forces the Sumproduct function to
Convert True and False answ...cross references to cells in a worksheet
I use cross references (ref field) in my Word form templates to pull table
cell entries to other locations in the form or other pages.
I can't find anything in excel help pointing me in the right direction for
using cross references in an excel form that I am setting up.
Is there a similar method? I want to identify specific cells in sheet 1 and
have sheet 2 or 3 or 4 (however many additional sheets) pick up and reflect
the entries made in the sheet 1 cells...... assistance is really appreciated
Use a Named Range
Insert - Name - Define
---------...hiding cells #3
I wrote a very simple program showing my fuel oil consumption. It seems
to work ok. my problem is hiding cells that have not in use yet. Is it
possible to post my file? I know someone out there will laugh and do it
thanks for any help.
Each day I enter the date, the amount used and It shows how much is
remaing in the 'tank' and how much i used each day. my problem, the
sheet shows negative number where I haven't entered any data yet..
franbarb's Profile: http://www...Show leading 0s for binary formatted cell?
How does one format a cell to display the leading zeros of
binary data (or of data of any format other than decimal,
for that matter)? In my case the binary data is created
with "dec2bin" function. The decimal equivalent of the
data is in the range of [0,15]. I want to display all 4
bits, including any leading zeros. For decimal data I can
display leading 0s by: format cells->number->category-
>custom and creating custom format "0000" which causes 4
decimal characters to be displayed including any leading
zeros. When I apply the same format to binary data I...How do I remove the percentage % from a cell and keep the number .
I have already typed 3 workbooks with the cells formatted as a percentage and
now I need the cell displayed as a number but not calculated as a percentage,
in other words going from display 3.5% to 3.5
...Format cell to accept only specific character-reject others
I am trying to format a cell so that it will only accept the characters H or
F and reject all others.
Select the cell, then select "Validation" from the "Data" menu. Select
"Custom", then enter the following formula:
Replace A1 with whatever cell you are using.
> I am trying to format a cell so that it will only accept the characters H or
> F and reject all others.
Select the impacted cells
Does that h...Possible to use only minutes and seconds in cell time format?
I'm trying to figure out a way to format cells so that I only have to input
minutes and seconds only, rather than having to type in the hour as well.
Is there a way that Excel will do this automaticaly, or is there a way to
format without the hour being included?
If you enter a decimal point after the seconds, XL will interpret the
Alternatively, you might look at
In article <AE698335-6AC4-4DF6-A9CC-E5B96BF6D8B0@microsoft.com>,
"Garehead" <Garehead@discussions.microsoft.com&g...goto special blanks does not return any blank cells
I have a sheet that has a lot of blank cells in column A and data in colum
B. I want to automatically fill in theblanks in A with the information from
the cell above, so I can use the data in B in relation to A.
Try Debra Dalgleish's nice coverage on this at her:
where she lists 2 methods to do so
(one manual, the other programmatically*)
*Sub FillColBlanks by Dave Peterson
Please respond in thread
"Lobo" <Lobo@discussions.microsoft.com> wrote i...Copy cell with Row Insert
This is a multi-part message in MIME format.
Here's the scenario. I have a spreadsheet with formulas in each row. The =
formulas are relative only to the row they are in. I also protect the =
workbook so cells with formulas are protected, columns can not be added =
and hidden stuff can not be un-hidden. The Basic template for this =
workbook has about 20 rows. The jobs this will be used on will require a =
varying number of rows, up to several ...can't change cell format
I am using Excel 2002 on my PC (Vista is the OS).
There are a number of cells with the green tick in the upper left hand
corner. I believe these are numbers that are formatted as text.
I want them to be formatted as numbers but highliting them and using
either the right click method or taking the options from the top does
not change the format.
These are numbers that have been typed directly into the cell and
transferred from other worksheets.
How do I get Excel to accept the change to a number format?
To change the format, you can select...I want the results of a formula to show in cell, NOT THE FORMULA!
I have the new excel program and I can't figure out how to get the results of
a formula to show in the cell instead of the formula itself. The old program
displayed the results! That is what I want, how do I do that?
ie....=SUMPRODUCT(+D3+D4+D5+D7+D8) should be xyz dollars, not
I have had sinmilar issues - but forst make sure that in
Tools -> Options -> View Tab - you havent got
the "Formulas" check box ticked somehow. However if this
is not the case, then on occaision I have had this - I got
around it by copying ...Run-Time Error on Cell Range Select
I get a run-time error 1004 on the .Select line. Can someone suggest a fix?
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.
What are your variable values...name of tab into a cell
In excel I want to put the name of the tab into the cell onto the shee
(eg Sheet 2)
Can someone let me know a formula to do this
Andy B.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1476
View this thread: http://www.excelforum.com/showthread.php?threadid=27557
Andy, try this, will return sheet name:
file must be saved first
Always backup your data before trying so...Audit cell reference on another sheet
Would it be possible to audit a reference to a cell in another sheet with
the Tools Audit and get the exact cell location ie: Sheet2!C23.
Try double-clicking on the dotted line which points to a tiny ws icon.
This will surface the "Go To" dialog which shows you the cell location.
"scooper" <email@example.com> wrote in message
> Would it be possible to audit a reference to a cell in another sheet with
> the Tools Aud...2 Data Validations in a Cell
Is it possible to put 2 data Validations in a Cell?
I have posted previously on not allowing input in say, C5 unless there is a
value in A5 (i.e. A5=<>""), but if I also want whatever is input in C5 to
not exceed 20 characters, how would I do that in the same Validation
"John" <firstname.lastname@example.org> wrote in message
> Is it possible to put 2 data Validations in a Cell?
> I have posted previously on not allowing input in say, C5 unles...format cells
cells BP36-38 won't change when I try to format them with a white or "n
color" pattern. Pls. see attached file
Attachment filename: san francisco county training sched-updated.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=59412
Message posted from http://www.ExcelForum.com
They're not formatted in the 'regular' sense, but
conditionally. Go to menu--Format--Conditional format..
and delete the conditions.
>cells BP36-38 won't change when I try to format them
with a white or "n...Avoiding #n/a cells
I have a few lookup formulas in range K18:K173 that are returning #n/a
results. Those will disappear soon when data is entered in the data
source. I'm trying to run summary totals, but the formula
=SUMIF($K$18:K$173,B181,$J$18:$J$173) returns #n/a. How do I change
this so that it ignores #n/a values in the range? Thanks.
you should change your lookup formulas in K18:K173. e.g.
> I have a few lookup formulas in range K18:K173 that are returning
> results. Those will disappear soon whe...How to navigate within a formula without changing cell references?
I have the following function in G20:
I need to replace the second parameter (F20), with another IF
I go to G20 and press F2 to edit the formula.
I move the cursor to just before the "F20" using the arrow keys and
start typing in the new IF statement. When I have it entered, I need
to move the cursor over the old F20 to finish the new IF statement,
but Excel takes the arrow keys to mean I want to reference an adjacent
How can I get Excel to interpre...combining cells #4
how do i combine two cells in two rows into one cell without losing data
A1 information here needs
A2 to be combined with information here
using a helper cell
=A1 & A2
Copy and Edit/Paste Special/Values back into a1
Via macro, one way:
In article <E8557060-AE72-4C4E-9523-1F3C897B7289@microsoft.com>,
"lyneday" <email@example.com> wrote:
> how do i combine two cells in two rows into one cell without losing data
> for example
> A1 information here needs
&...Charts within a cell
Can a cell be used a chart ?
And show a simple range as a chart within a cell using a line or bars and
Only using values given?
a cell (bars) a cell (lines)
: - : : - :
: - : : - - :
: - - - : : - - - :
: - - - - : :- - - :
: - - - - - : : - - :
using range : ek7 is 4.2
em7 is 5.3
eo7 is 6.5
et7 is 3
ev7 is 5.4
Yes, hold...Changing the order of a name in various cells
I have a column with over 400 rows of names that most contain names reading last name first then a comma then a persons first name. Such as Sullivan, Mike. How can I change the order to read first name then last name without the comma? Such as Mike Sullivan.
Thank you for looking.
With entry in A1:
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1) & " " & LEFT(A1,FIND(",",A1)-1)
(Don't use my reply address - it's spam-trap)
"happydude" <firstname.lastname@example.org> wrote in message
news:9...how do i prevent a row from moving, like subheader that is visibl
In Excel, how do I prevent a row from moving, like a subheader that I want to
stay always visible at the top of my document?
If you want row 1 to stay fixed, select row 2 & use Windows/ Freeze Panes.
If you want to freeze both rows and columns, if you select cell C3, for
example, before selecting Windows/ Freeze Panes, then it will freeze rows 1
and 2 and columns A and B. In other words it will freeze rows above and
columns to the left of the selected cell.
"Binmaru" <Binmaru@discussions.microsoft.com> wrote in message
news:CCEF434B-24B4-429D-870...Jump over 10 cells in a row
If A1=1, the next 10 cells fill with 88. How can I jump to the 11th cell? I
tried highlighting the 10, Ctrl 1, unlocking & protecting, but that takes me
down to the next row. Ideas?
Don't quite understand exactly where you're trying to go.
Is the cell you want to "jump" to empty?
If so, this will get you right *next* to it.
Try selecting A1.
Hover the cursor over *that* border of the selection in the direction that
you wish to "jump", until the cursor changes to an arrow.
Double click, and you'll jumpt to the last cell of contiguous data...