if cell = 0 then hide the row?
My spreadsheet is a list of names and hours and charges, but if name X, say
D14, has zero hours, say F14 = 0, then I do not need the row - otherwise my
spreadsheet will have so many unused rows - and so want to hide it.
To complicate matters, my spreadsheet has titles, etc, and clearly such a
row has no value for hours in any cell, and these rows are not to be hidden.
I see something that only conisders the "data rows" and hides them if the
hours cell, F14, is zero.
Here is one way which assumes headings in rows 1 and 2. Just chan ge the F3
to the first real data ro...Total cells of one column based on the values in another column?
One of the sheets in my ledger workbook is for annual cash receipts.
Column D is for the amount (formatted to number-accounting)and Column E
is for who it's from (formatted to number-text). I've got my cells
elsewhere for month, quarter, & year totals.
The problem is I have to manually, with my adding machine, go thru and
add up the total from each source. (How much total income for the year
is/was from Co.A, Co.B, Co.C, etc..)
I've figured out how to use conditional formatting so that if the text
in Column E cells is say, Co.A, the text will be red, if Co.B, text will
...Column Labels on Views
Hi, does anybody know if I can change a the Display Name
of an existing column field?
I checked Deployment manager and noticed the Display Name
is prompted during creation of the field, is there a way
to change the label? we reused a field for the accounts
table and would like to change the display name on the
Simple log in the CRM web page with administrator level and go to
Start/Configuration/Custom System, choose the entity who you want custom and
click in custom form. In this page you can choose the displayed fields and
After custom the disired fie...Excel Rows and Columns Issue
I am an excel 2002 user, and I am having problem putting in th
formulas. The formulas are simple, average, add, subtract, an
multiplying cells, but the real problem is in "viewing" the rows an
Normally, in excel, rows are numbered (1,2,3,4...) and columns ar
alphabatized (A,B,C,D...) In my case, both columns and rows ar
"numbered" , so putting in a formula is a pain in my neck. How d
resolve or change the view of the excel sheet so it displays th
"Letters" on the top columns part of the sheet going from left t
right, and the "numbers" rows (wh...Column Headers/headings
Is there any way that you can get the top row to stay visible no matter
where you are on the spread sheet - ie: to be able to see what columns you
are typing in (eg cost) when further down the spread sheet.
Thanks for any help,
Click on A2
Click Window > Freeze Panes
Use xdemechanik <at>yahoo<dot>com for email
"The Stoat" <firstname.lastname@example.org> wrote in message
&...My "Do While...Loop" wont loop and delete the spaces between my digits.
I am trying to eliminate the spaces between digits in Australian
Business Numbers which have the format "NN NNN NNN NNN" to produce an
eleven digit number with no spaces. I have written a function to do
this that appears to work. I now want to call this function from a
subroutine to change about 3000 numbers. The subroutine is causing me
headaches. Here is my space-eliminating function:
Function RemoveSpaces(CellContents As String) As String
Do While InStr(1, CellContents, " ") > 0
CellContents = Replace(CellContents, " ", "")
Re...Holding the view of column one
I want to hold the view of column one while working on other columns. What
is the best approach to my need?
Thank you, Mark
Microsoft MVP - Excel
"Mark S." <Mark S.@discussions.microsoft.com> wrote in message
> Good morning,
> I want to hold the view of column one while working on other columns. What
> is the best approach to my need?
> Thank you, Mark
...auto comlete a row of data
in exel say for instance g1 is a lisence plate number (variable) how can i
get A1-f1 to be filled with spesific data dedicated to each licence number
You could use a lookup table and VLookup formulas or a macro. More info OR
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
Microsoft MVP Excel
SalesAid Soft...Repost: Loop through files
I posted this earlier, but it never made it to the screen
so I'm reposting - hope it doesn't duplicate the earlier
I need to loop through all currently open workbooks and
display a message box that tells me the file name and
whether or not the workbook is visible.
Your example code is what I need. Thanks in advance for
Dim oBook As Workbook
For Each oBook In Workbooks
MsgBox "Book " & vbNewLine & oBook.FullName _
& vbNewLine & "is " & _
IIf(oBook.Windows(1).Visible...Ordering Columns in a Cross Tab Query & Dividing a Value
First question, is there a way to specify what order the columns appear in a
cross tab query?
Second, is there a way to distribute a value in one column of a cross tab
query across new columns? For example, if I have a total value of a
contract, but I needed to recognize revenue equally in each of the three
months of a quarter, is there a way to distribute that value across columns
in a cross tab?
>> First question, is there a way to specify what order the columns appear in
a cross tab query?
In the SQL view edit the PIVOT line to something like this (Make sure...Thick column lines
I want to create thick column lines but don't know how. Thick lines would
enable me to separate the data from one column to next more clearly. Please
help. Thank you.
Format / Cells / Borders / Select thick border and click on diagram to tell Excel where to apply
it on the cell(s).
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
There's no 'I' in 'Team'
I really am having a bad week...
I have a workbook with two sheets; 'main' & 'not complete'. On the main
sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may
What I need to do is: for each row, if column I = 1 and J = "" then I need
to copy some of the data from that row (column A,B,F & G) to the sheet called
'not complete'. the cell in column J will then be changed to 1. Repeat for
each row making sure the sheet called 'not complete' does not have any blank
Hope that makes sense a...Money 2003
What is the column in the register that has the heading of
a red exclamation mark? Some of my entries have an image
of an envelope and a question mark in that column. What
does this image mean?
This is Money 2003 Deluxe running under Windows 2K.
Thanks in advance.
See http://umpmfaq.info/faqdb.php?q=6 and
http://umpmfaq.info/faqdb.php?cat=6. From online help:
There are two icons that indicate different conditions for downloaded
When you see ? in your account register, no match has been found for this
downloaded transaction. It is marked as a possible new transaction.
A ...How do I set end-of-range to #rows from beginning
Specifically, I am creating a chart that will have a breadth (# data points)
based on a variable (value of a cell). Start point is always the same. For
instance, a table is created based on a formula. The chart should stop when
values in the table reach a certain value, but this could be 20 or 200
datapoints (20-200 rows). I know how many datapoints should be included, but
not how to terminate the graph based on this number. I would prefer to do
this without VBA, as I am not particularly proficient, but will if the only
It would be nice to see some sample data, with a...Unhide a column among columns.
There are 10 columns which are hidden between two columns(A and X). I want
to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do
Thanks and regards
With the help of a Macro.
The command look like this:
Columns("E:G").EntireColumn.Hidden = False
*** Check the VBA Help ***
> There are 10 columns which are hidden between two columns(A and X). I want
> to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do
> Thanks and regards
>...Columns and rows are numbered
For some really strange reason lately, when I'm working in Excel both my rows
and columns are numbered 1,2,3,4,5....This makes it really difficult to deal
with . How do I get excel to stop doing this I want my columns to go back to
being lettered A,b,c,d, etc....Anyone else ever have this happen or know how
to fix it. It's annoying. Thanks
Tools - options - general- R1C1 style- UNTICK.
>For some really strange reason lately, when I'm working
in Excel both my rows
>and columns are numbered 1,2,3,4,5....This makes it
really difficu...Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
A spreadsheeI that allows me to enter hours is not adding up correctly
in the totals column. It adds up correct up to but not including 24
hours but after that the total deletes that 24 hoursl It seems Excel
is mistaking this for the 24 hours clock system and doesn't realize
that it must show the total. i.e., after deleting 3 rows above the
totals column I get a total of 23h16m but then after the next row, it
shows less than 1 hour with the final total of all the rows showing
01h31m. That should actually show 25h31m.
Is there a way to get Excel to actually add up the hours instead of
...flip columns to rows
Is there any way to 'flip' a worksheet with one command, so that columns become
rows (or rows become columns)?
Have you tried the Transpose function?
"captain fantastic" wrote:
> Is there any way to 'flip' a worksheet with one command, so that columns become
> rows (or rows become columns)?
captain fantastic wrote:
>Is there any way to 'flip' a worksheet with one command, so that columns become
>rows (or rows become columns)?
Not one command, but you could write a macro.
Paste Special tick "Transpose"
I need to run code while the record is displayed on a form, and must run
this for all records.
I have on the Click event of a button on the form:
DoCmd.GoToRecord , "", acFirst
It is MyFunction I need help with:
DoCmd 'Run some code and queries'
DoCmd.GoToRecord , "", acNext
Need help here to repeat/loop until the last record.
Thanks in advance.
Is there anyway on exch 5.5 to limit the number of times a mail is resent ie
when the user sets up a forwarding rule to a home account and screws up the
address a mail loop is formed.
I would like to set the number of retries to say 6.
Rules are set to fire when the e-mail is received. Only OOF is set to fire
only once per recipient. As far as the rule is concerned, each message is
separate and must be forwarded on to the home account.
Do yourself a favor and disable forwarding to the internet. Give them
another option to get e-mails while they are home.
If you need ...How cancel $ in column?
Operating System: Mac OS X 10.6 (Snow Leopard)
I mistakenly selected a column and hit the dollar sign ($), changing the numeric codes in the column to dollar and cents values. How can I cancel this? Thanks.
If you haven't done anything significant since & haven't closed the file,
how about Undo?
Otherwise, select the column again then go to Format> Cells to select either
General (default formatting for new sheets), Number, or whatever is
<William_Richards@officeform...Inserting row takes 1
I'm working with an Excel 2003 workbook with six worksheets (that I've opened
in Excel 2007), one per year. There are only 14 columns with a very simple
SUM formula in column 14. This is a spreadsheet that keeps track of auction
sales by week with a grand total at the bottom of each yearly worksheet.
Since I've opened this workbook in Excel 2007, it is taking from 1 to 3
minutes to insert a blank row in the middle of other blank rows. This would
be instantaneous with the same workbook in Excel 2003. I'm not a programmer,
just a user and this is ver...reference cell value from fixed column with variable row
template wizard wont let me select a form control (combo box) value to
associate a field with but provides a reference cell value which gives me the
row number in the other workbook of the value. how do i make a valid cell
reference from this reference? i.e. col of referenced value is "A", row is
variable depending on what value i pick in combo box. how do i make a
reference out of "A" plus the value to equal "A5" ? (so that i can associate
a field with it using the template wizard)
My Column names are not showing-up as A, B, C, D...
They are showing-up as 1, 2, 3, 4, 5...
I do not understand why this is happening??
I have tried running: Excel.exe /regserver
In xl2003 and below:
Tools|Options|General|Uncheck R1C1 reference style
> My Column names are not showing-up as A, B, C, D...
> They are showing-up as 1, 2, 3, 4, 5...
> I do not understand why this is happening??
> I have tried running: Excel.exe /regserver
> ...Loop a
I am programming up a macro in Excel, I was just wondering how to loop
the range function.
Whereby I would like to somehow put a variable in the function Range,
for a set column, eg.
dim i as integer
dim c(0) as string
for i =117 to 200
c(0) = Range("C" & i) ' This is the line that doesn't work
msgbox c(0) 'for simplicity
No need for a loop
c = Application.Transpose(Range("C117:C200"))
(there's no email, no snail mail, but somewhere should be gmail in my addy)