detect a filled out cell
I would like to check a column.
If a cell has text, i want to increase a counter.
could it be done using SUMIF ?
something like SUMIF(Page1!A:A;cell <>"";total=total+1)
i don't want to use VBA
It will count all non-empty cells, also numbers
Microsoft MVP - Excel
"Alain R." <email@example.com> wrote in message
> I would like to check a column.
> If a cell has text, i want to increase a coun...Hyperlink via indirect cell reference
I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.
For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
In another sh...Set a range from a variable location
I am using a loop function that will eventually select and activate a
specific cell known as "TheCurrentCell." Once I reach that location I want
to use that coordinate as the upper left area of a new range. Supposing that
the end cell of my range is D1000, the Range selection would be something
like Range(TheCurrentCell:D1000).select, however, I don't know the correct
syntax to make this happen.
Please help. Thank you.
Use something like Range(Selection.Address & ":D1000").Select.
"Troubled User" wrote:
> I am using a loop function that will...How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the
cells that head up the first page. I've done it before in school but can't
remember what it is called or how to do it...that's why I'm doing this.
Anyway, I want these cells to print off on each new page without having to
type them on each page. I hope that makes sense and I hope that someone can
If you mean for printing do file>page setup>sheet and select rows to repeat
otherwise for viewing you can select a2 if the headers start in row 1 and do
window> freeze panes
How can I rename column A to read "bills" instead of the letter A?
You can't. The closest you will get is to hide column headings, via Excel
Option, and then create your own.
"shoe" <firstname.lastname@example.org> wrote in message
> How can I rename column A to read "bills" instead of the letter A?
you cant change the headers or row labels but you can define you data as a
list (or table) and the headings can then be used to refer...merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell?
Not possible I'm afraid. Try placing the dat from both cells into one
and use "Center across selection" under Format>Cells>Alignment
Merge cells always end up causing grief. they are best avoided.
***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
"bob" <email@example.com> wrote in message
> How can I merge 2 cells without losing data from the other...Create static text from cell reference
I have two columns of text which I'm combining in a third column using the
formula (for C1, for example) =A1 & char(10) & B1
This gives me the contents of A1 on a line above the contents of B1 and
What I NEED to do is somehow create column C as TEXT, not as a REFERENCED
data from columns A and B. How do I create a cell that contains the actual
TEXT content of another cell instead of a REFERENCE to the other cell?
Select all the cells in "C" that have content. R-click them and select
"Copy" then r-click again, sele...Cell Format #4
Is there a way to have a cell format based on contents of an i
if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma
I want the If statement to test a condition, return contents of th
correct cell and format automatically.
Any help is appreciated
bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177
View this thread: http://www.excelforum.com/showthread.php?threadid=26133
You can't change the fo...Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How
do I do that?
x-axis y1-axis radius(y1/2)
0 0.00 8.0000
1 0.25 8.0242
2 0.50 8.0691
3 0.75 8.1281
4 1.00 8.1989
5 1.25 8.2803
6 1.50 8.3716
7 1.75 8.4729
8 2.00 8.5832
divide the y1-axis by what?
2 as an guess with y1-axis in column c
in the y1/2 column(d?), enter
> I want to divide the y1-axis column and save it to radius (y1/2) column. How
> do I do that?
> x-axis y1-axis radius(y1/2)
> 0 ...Turn off automatic dialing
I recently reloaded Office 97 on my XP computer. Now the
computer is automatically dialing into the internet to
download email. I only want to dial manually.
I've run into this problem before but can not remember how
to set it to manual dialing only. Any ideas?
Check your mail profile - under the General tab: the
default connection state might be online and might be
checking for dialup networking. Set this to manual.
>I recently reloaded Office 97 on my XP computer. Now
>computer is automatically dialing into the internet to
>down...cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop
selecting cells. Have tried double clicking, playing with the Function keys,
all sorts of things, but to no avail... don't want to force quit.
Any clues? TIA, Geri
See David McRitchie's notes at:
"Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in
> All of a sudden the mouse is acting like it is held down, a...re post range check
i have a worksheet with a lot of data on it
column A contains the date
column B contains an area
column C contains the fault
column D contain a quantity
A B C
1/4/04 stores damaged
7/4/04 bins rust
-- -- ---
-- --- - --
31-12-04 floor ...Average of logic cells
I used a logic test to determine some levels from raw scores. For EG
>120 =5, 119-110 = 4, etc. I now want to dtermine an average score of
several of the the results from the logic tests but it doesnt seem to
work. (AVG does not recognise cells with logic tests) Can anyone help,
ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245
View this thread: http://www.excelforum.com/showthread.php?threadid=489704
hi, ckdkvk !
> I used a logic test to determine so...Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet.
(Don't use my reply address - it's spam-trap)
"MT" <MT@discussions.microsoft.com> wrote in message
> I have a project to do. I have to create an input ...Cell References..
I have a 12 month rolling report with a seperate worksheet within th
workbook which refers to the column containing current month's Numbers
When I "Cut" Column C (which contains the oldest Month) and "insert
column C between N & O it shifts my cells left and all I need to do i
input all of the current Month Data into Column N. The formulas al
remain intact and everything is peachy. Until I goto the Workshee
that refers to the Current Month on the 12 month rolling report.
My problem is that when I shift the columns on the "Report" workshee
it chages the cell...Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format.
its been helpful to me so maybe it will do good for you too:
how to create a simple macro within Microsoft Excel, and then how to use =
that macro to calculate a single cell value.
&l...Changing named range reference depending on a cell's content
Where to start?!
I've got the following formula pulling data in from a secon
spreadsheet within the same workbook:
We have 8 different locations ("MICH" being one of them) that we nee
to be able to access. I can write a nested IF formula that looks a
cell I7 (that contains a list of all 8 locations) and, depending o
I7's content, brings back the desired values.
I was hoping someone in the forum could help me write a simpler formul
that would not have 7 IF statements embedded in it.
Any help w...Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in
a range to each cell in another range.
example. the first range would have vales of 1,2,3 and the second
range would have values of 5,6,7 Thus the macro should return 9
possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7,
3 & 5, 3 & 6, 3 & 7,
Can anybody help me with this??
Just hazarding some thoughts here ..
Perhaps using formulas would suffice ?
Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then
Compare 1st range against 2nd range
I...Range Names with Charts
I would like to use rangenames to specify the data sources for a chart, but
Excel does not seem to like this. I have tried entering the rangename in the
.... and various other formats.
Surely there is a way to do this ....?
try either depending on whether the named range is local to the sheet.
> I would like to use rangenames to specify the data sources for a chart, but
> Excel does not seem to like this. I have tried en...Cdn Payroll July 1 errors
Since the tax update was installed for July 1 for Canadian Payroll, our
Detail Range Reports do not have the proper data. I noticed that CPY30260 has
not been updated!!! this is not good. I contacted our vendor to see if this
is a known issue.
There is a round2 tax update that does not affect our province so I have not
installed it. I was waiting for round3 which has not been released yet.
Is anyone else aware of this? We have not seen any errors during Update
Masters. Our YTD amounts and T4 amounts appear to be correct.
> Since the...Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location.
The code looks like this:
Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut
However, when it reaches this point, I get an error saying "Cut method of
Range class failed". I've tried changing the destination range so that it
reads similar to the origin range, but that didn't help. Can someone help me
get the syntax on this line correct? I've got several similar lines further
down in the code. If I can fix thi...Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or
higher.either text or cell shade. just so it catches the users attention.
im running excel 2000. and i have this currently in the cell that i want to
aplly this to:
have you tried conditional formating?
>I need a cell to change color if the value inside the
cell reaches 50 or
>higher.either text or cell shade. just so it catches the
>im running excel 2000. and i ...How did I break the cell display limit? #2
If there is anyone out there who can help with this, I would be most
most grateful. Here's the facts:
- I'm working in a MS Excel worksheet.
- I am aware of Excel's 255-character-per-cell display limit. (As
understand it, each cell can hold up to 32,000 characters, but wil
only display/print 255 characters.) This is a major pain in th
petunski, as most of my cells contain more than 255 characters
Therefore, most of my spreadsheet shows cells that look like this
###############. I have to look in the formula bar to see the actua
contents of each cell.
- In cell C35, I have 1,05...unable to link from Access to excel named range error 3011
I have a list of xl workbook names in an Access table.
My code cycles through these names and finds them in a directory. This works
flawlessly. Now I want to find and name ranges in the workbooks, and link to
them from my Access db.
The process works for the first 2, then fails on the 3rd. I don't see what's
different about the 3rd
Active and Approved Pending Stores*.xls xlStores Location Code
Annualized Rent Batch*.xls xlExtras Store Number
...Error 553 "sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1)"
Using outlook 2002 and mail returned as follows. Please
"553 sorry, that domain isn't in my list of allowed
You may need to authenticate to your outgoing server. To set this up, click
on "More Settings..." on your account page, then go to the "Outgoing Server"
This posting is provided "AS IS" with no warranties, and confers no rights
"Anthea" <firstname.lastname@example.org> wrote in message
> Using outlook 2002 and ma...