Read text as a cell for a formula
I have two formulas in two cells, one spitting out a value for a colum
(A) and the other a value for a row (2). In the a third cell I'm addin
the letter and number together to create a cell address (A2). I want t
use this information in a fourth cell for a formula (=A2*$B$10).
But, how do I get Excel to recognize the text "A2" as a value for
cell and then add A2 (or whatever the third cell may be) in to
Message posted from http://www.ExcelForum.com
where A4 stores your cell reference as text, e.g. 'A2&...Finding the last row in a spreadsheet
Hi all. I'm a relative newbie to using Excel and am stumped.
I'm trying to find the last (non-zero) value in a column. The cells
all have a function in them presently like =IF(C8>0,SUM(B8:C8)," ")
presently so that the cell truly is not empty. I've tried using
=LOOKUP(2,1/(D2:D350>0),D2:D350) but it displays nothing since all the
cells have a value (the function above).
How do I find the last cell having a numeric value (which is what the
first function provides)? Any help is greatly appreciated.
It's because you don't use empty as opposed to space &...Port number
This is a multi-part message in MIME format.
One of my frineds is able to use the Microsoft Office Outlook Live fine =
from his house. When he goes to his office and accesses it, he is =
getting an error message 0x80040126 while trying to send and receive =
emails in MOOL. The only reason why this could happen that i can think =
of is the firewall. Is there any other reason why this could not work =
Is there somebody here who knows about the...Subtract two numbers
I have a stock table with fields like qty rcvd, allocated and left.
i want to put in a java script which says left = rcvd - allocated.
these fields are type int in the crm entity.
below is what i have but doenst work. can someone please help
var QR = crmForm.all.new_quantityreceived.DataValue;
var A = crmForm.all.new_allocated.Datavalue;
var QL = crmForm.all.new_quantityleft.DataValue;
if (crmForm.all.new_quantityreceived.DataValue !=null)
QL = QR - A;
regards and thanks in advance
1. Typo: var A = crmForm.all.new_allocated.Datavalue;
This should be: var A = crmForm.a...Circular reference, Maximum number of formulas
I have come across a spreadsheet in Excel 2000 which exceeds the 65536
number of formulas that Excel's dependency table can keep track of. The
consequence is that 'Calculate' is permanently diplayed in the status bar,
AND (much more important), it cannot keep track of circular references.
By opening the file on my machine with Excel 2003 SP3, I was able to locate
the circular references and correct them.
What is the max number of formulas that Excel 2003 can track in its
What about 2007 does that increase the number of formulas that can be
Th...How do I fix this?
I used the Startup tool to setup my application but I unchecked the Menu Bar
and the Shortcut Menu Bar checkboxes. I need to make some changes but I can't
get the Tool menu back.
Hold down the Shift key when you open your database.
"Ayo" <Ayo@discussions.microsoft.com> wrote in message
>I used the Startup tool to setup my application but I unchecked the Menu
> and the Shortcut Menu Bar checkboxes. I need to make some changes but I
> get the Tool menu ba...Help with Rules: can't select Public Folders.
Does anyone know why it's sometimes possible, but not always, to use a rule
to move or copy a message to a public folder? I don't seem to be able to
select the public folder; I can select it, but it doesn't stick. The
inconsistency is a bit weird. I can't even make a, 'from scratch' exact
copy of a currently working rule that moves a message to a public folder.
Cut off: yourhead to respond
do you have full permission on the folder?
Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
Coauthor, OneNote 2003 for W...Need to convert 2002 back t0 while laptop is fixed
My laptop with Money 2002 died and had to be sent in for
service which will probably be about a month. The good
news is that I backup up all my data files the day before
and burned them to CD. My office machine is running
Windows 98 SE and Money 2001 but it will not read the
2002 files. I can't install XP because the machine will
not support it.
I used a friend's machine and can open my backup file but
I don't want to leave my financial data there. Is there
some way I can transfer the data to Excel or save it in a
backwardly compatible format? I tried to synchronis...Automatic resizing of cell
I use Excel for my job. When I enter a long text sentence
into a cell that is larger than the cell, it gets covered
up because the text wrapping is on. I want the sentence to
wrap around to the next line, but Excel doesn't
automatically adjust the size of the cell when it sees
that the text is longer. I end up printing out my work and
only half of the sentence is there. I can resize it
myself, but I want Excel to resize it when it sees that
it's covering it. I can't see why anyone would actually
WANT Excel to cover it up.
Which version of Excel are you using. I'm u...Page Numbers in Cells
This is what I am doing....
I have a monthly document (identical in appearance but not content) which
can expand or shrink dependant on the number of items which are added to it.
The top section is printed on each page and one of the cells needs to state
the page number… ie Page 1 of 2 etc.
Is there a formula that can be put in the cell that registers the printed
Why not put the page number in the header or footer. Got to Page Setup and
the Header/Footer Tab. Select the pull down menu for header or footer and
page # is an option.
"DC appleyards" <...Fixing column width
I have a spreadsheet in which I have set the widths of certain columns
using Autofit. The problem is that I often have to add to the
spreadsheet by importing data from a text file. Every time I do that,
the column width changes to the width of the new data (which is
usually narrower than what I wanted). Is there a way to prevent that
Assuming you're using the Data, Import Data command, when you get to the
final "Import Data" dialog there is a "Properties" button (in Excel 2003 at
least). One of those properties is Adjust column w...sum column based on single cell match?
This seems so simple and yet the solution eludes me...
In row 2 I want Excel to match a cell's contents (ex: find 3200). Once
finding the column that matches, I want it to sum rows 3:94.
Thank you in advance for your assistance.
Something like this:
In this example, you have columns A-G for your data.
"adodson" <email@example.com> wrote in message
> This seems so simple and yet the solution eludes me...
...can Excel randomly sort numbers
I would like to sort numbers in a random sequence. How can I do that in
Excel or any office application?
"gasmith" <firstname.lastname@example.org> wrote in message
> I would like to sort numbers in a random sequence. How can I do that in
> Excel or any office application?
Yes. Assuming that your numbers are in column A, insert =Random() in column
B. Drag this function downwads so that you have as many calls to Random as
you have numbers to sort randomly.
Select columns A and B
Select Tools|Sort and sor...Selecting a Range of Columns for Variable Rows
I want to do something like this:
Select a sheet
for row=3 to 17
select cells A:F
copy the cells
Select A2 on another sheet
Paste what I copied.
Shift down 1 row
I have verything down pretty well except selecting A:F for each row as it
I would appreciate any help you can give me.
It would be helpful if you post the code you have so far. It helps to fill
in the gaps in your narrative.
"BillR" <BillR@discussions.microsoft.com> wrote in message
>I want ...The great disappearing hot fix
Has anyone experienced anything like this?
Yesterday, one of our users complained to me about some quirk in GP eConnect
processing that I had thought we fixed in December of 2007 via a Hot Fix to
Well, when I checked our server it was as if the hot fix was never installed
(the eConnect release version was the basic 220.127.116.11, instead of 18.104.22.168). The
Hot Fix was also missing from Add/Remove Programs.
I went to reinstall the hot fix, and when the install program started it
gave me the option to either Repair or Remove the hot fix. So, something was
still present tha...Deleting row produces #REF error
I have a column with percentages.
The top number is based on the SUM
of the lower column cells. I have,
For example, I want to delete the row
containing 10.00% and have the calculation
still be correct, but I get a #REF error.
Is there some way to do this without getting
Try this: =1-SUM(F5:F7)
> Try this: =1-SUM(F5:F7)
Row numbers are not consecutive (jumps from row 3 to row 8). To the best of
my knowledge I've never done anything (like hiding) that would cause this
Do you have a data filter on?
Regardless of what caused it, did you try unhiding the rows?
"Richard" <Richard@discussions.microsoft.com> wrote in message
> Row numbers are not consecutive (jumps from row 3 to row 8). To the best
> my knowledge I've never done anything (like hiding) that would cause this...WHY select the first item didn't receive OnPaint in LISTCTRL!
I write a custom listctrl inherit from the CListCtrl( MFC ).
I don't use CUSTOMDRAW and OWNDRAW, and rewrite the OnPaint fucntion.
The OnPaint function like this:
CPaintDC dc(this); // device context for painting
GetClientRect( &rtList );
CDC* pDC = &dc;
CMemDC memDC( pDC, &rtList ); // double buffer.....
memDC.SetBkMode( TRANSPARENT );
// don't use the system paint, because it's too slow...
// if you resize the listctrl...Copy Only Visible Cell
Is it posible to copy a block of cels with combined hidden collumn and
row to another sheet or cells without those hidden field to be copied ?
When i try to copy this kind of cells to another sheets/cells, excel
copy all cells including the hidden cells.
I just want to copy cels that is visible not including those hidden
cells, Is there a way around this ?
Thanks in Advance
Select the desired range.
Click Edit - Go To.
Click Special then select Visible Cells Only then OK.
Copy the selected range.
In article <email@example.com...Cell Box - one of the 4 cell lines is open
What was the set of search "words" to find the answer... please? I have
spent an hour. ( I don't want to tell you that I spent more time -
mentioned). Oh yea!
The lines are call borders and are part of cell formatting.
Format > Cells... > Border > Outline > OK
Gary''s Student - gsnu200826
> What was the set of search "words" to find the answer... please? I have
> spent an hour. ( I don't want to tell you that I spent more time -
> mentioned). Oh yea!
No worky. Excel 2007 - home tab - cells (ins...Error importing dates from fixed width txt files
I'm trying to revise an existing Import Spec to include new fields added to a
fixed width text file. If I import without making any revisions the dates
import correctly. They are on the file as CCYYMMDD and the data field in the
Spec is Date/Time and I've deselected 4 digit year. My new date fields were
added to the text file as YYMMDD and I select Date/Time data field for the
new fields. However, with the revisions made adding the new fields none of
the date fields import now with the revised spec.
Another user created the spec and we're both using Access 2003 with th...Creating a chart with text not numbers
How do I create a chart that has text values (freshman, sophomore, junior,
senior) instead of numbers (1, 2, 3, 4)?
I have a column labeled "What is your class standing?" and below it is 166
rows where people selected "Freshman," "Sophomore," etc. I want to create a
bar chart that counts how many people selected each of the four classes.
First of all, you will have numbers to plot. The words are your category
Read how to turn a column of words like this into a histogram:
- Jon...How do I generate automatic invoice numbers in excel
I want to be able to generate the next sequential number of an invoice when I
open excel to type/print a new invoice.
some wise soul had shared this in some other post, i am posting it here
seemingly, it helps create automatic sequencing in cell A1 on Sheet1.
Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long
If .Text <&...Confusing output from cells
I have made a spreadsheet that is gathering details from the user an
then forming a string.
However, in the 6th section of 8 it is causing problems.
the formulas are all much the same. an example is shown below:-
Now, B268 is empty, so the "" criteria is met, but instead of bein
nothing, the spreadsheet is counting this as a space, so that when th
text is pasted into another program, extra spaces are added! However
the Len of the above cell is 0, meaning it has no value whatsoever.
Does anyone know anything about this?
Than...Insert rows with Formulas
I have a spreadsheet that starting at row 18 contains the following
B18= Datavalidation indirect ($M$2)