Locking a cell reference in a formula
I have a worksheet that has formulas built within 6 columns. I want to drag
the formula in each column down 100 rows, but as I am calculating a date that
references only 1 cell (A1) when I drag the formula, it picks up A2, A3, A4
etc. as the formula progresses down the column. Is there a way to lock the
cell reference to A1 so that when I drag the formula down the column, that
cell reference doesn't change?
Add a $ sign in front of the cell reference.
$A1 Locks the column but allows the rows to change
A$1 locks the row but allows the column to change
$A$1 loc...Complicated pickle
I have a complicated (for me, anyway) problem that I have solved in a
way that I'm not happy with. If anybody can offer suggestions for
improvement to my methods, I'd very much appreciate it.
The problem is that I've ended up with a set of formulas which fall
apart if I insert a row. This is because I'm using the offset()
function, where I suspect I should be using something else.
The goal is to have 2 worksheets, named D (for data) and R (for
report). The data is organized such that each column is a distinct
entity. That is, the data points are rows and there can be hund...2007 and 2 monitors
I have a question for you. I just received a new computer with Office 2007
and I cannot figure out how to do a screen print of a single screen with a
dual monitor. In the prior version I would use Ctrl Alt Prt Sc - but it
does not work with this new version. Do you by chance know how to do this?
Have you tried the Snipping tool in Accessories? This is only available in Vista
and Windows 7.
Mary Sauer MVP
"Ron D" <Ron D@discussions.microsoft.com> wrote in message
>I h...What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula?
I have written a macro that allows a user to select a
specific series on an embedded chart, then specify a change
to the series limit ranges. It works very well, EXCEPT I
have not figured out how to write a statement that will
return the index number of the selected series. I
mistakenly believed that the series index number is the
last number on the right end of the series formula. It's
not. That's the plotting order sequence number, and it can
change. I need the series index number, which I think is
unique within the workbook. I tried using SELECTION.NAME
after selecting the se...Allow 2 or more referencial fields to same entity
When defining a custom entity it is currently not possible to define 2
separate relationships from this entity to another entity. For example,
consider a custom "Project" Entity - it is not possible to create two
relationships to Contact - one for a "Lead Engineer" and another for
"Inspector". To do this, you would currently need to set up a many-to-many
relationship using a 3rd entity and this makes things very difficult for the
end user data entry, search and for reporting purposes.
This post is a suggestion for Microsoft, and Microsoft ...Last cell in new formula
Can anyone please help?
Dim x As Range
Set x = Cells(Rows.Count, "N").End(xlUp)
I would like to find the last cell in column N and instead of the result
being displayed in A msgbox, I would like to use the cell address in a
formula after the code above e.g.:
range("b2").formula = _
Dim LASTCELLADDRESS As Long
LASTCELLADDRESS = Cells(Cells.Rows.Count, "...Formula Problem #13
I seem to have trouble w/ a particular formula. I am attempting to
round a number to the nearest tenth. No big deal...simple stuff if it
were to stop there. I have rounded the number which returns as a
decimal. I don't have trouble with any other number except when
dealing with a zero in the 10ths spot. When I add text to the formula
the decimal is not shown. Is there a way to have Excel first
determine whether I have a zero in the tenths column and if so, have
it show the decimal? Here's an example of the formula...
...password problem #2
hi... earlier tonight i was using outlook express 6 to
check my mail.. when i went to write mail and send one my
user name and password came up on the screen. i have been
using this for months and never had a problem.. the
username and password came up and i pressed ok.. and it
keeps popping up.. but it is only for outgoing mail..now
i think i did something to the password... how can i
change it or whatever?
nevermind i fixed it......
>hi... earlier tonight i was using outlook express 6 to
>check my mail.. when i went to write mail an...Viewing formulas
I need to find a formula that's on the sheet I'm working on - I don't know
what it is or where it is and it's causing problems. How can I display all
formulas on the sheet I'm working on?
Touching CNTRL ~ will toggle the display to shows formulas as opposed to
values in the cells.
"Lynne Mawson" wrote:
> I need to find a formula that's on the sheet I'm working on - I don't know
> what it is or where it is and it's causing problems. How can I display all
> formulas on the sheet I...Formulas do not refresh anymore
Has anynone encounter this problem in MS Excel in Office XP??
I use a spreadsheet that basically adds up numbers and gives me a tota
on the bottom. Today it stopped doing that. I change numbers and th
total stays unchanged unless I highlight the total's cell, put th
cursor in the formula bar on top at the end of the formula and hi
ENTER. It won't do it on its own anymore. This happens in ever
spreadsheet I use so it's not just that one file. Can someone help?
Message posted from http://www.ExcelForum.com
Tools>Options>Calculation, check "Aut...Complicated
In a sheet named "Confirmed" I have a spreadsheet which contains the
Column F (a list of languages)
Column G (a list of languages)
Column H (a fee per 1000 words)
Column I (a fee per 1000 words)
Now, I have set up a data validation series in a new sheet, so that when a
language is selected, say for example, Arabic which is in F1 of the sheet
"Confirmed", the languages listed next to it in G1 appears. If Arabic
appears in F1, F2 and F3, then the languages which are listed in G1,G2 and
Now what I want to do, is for in a new sheet in cell A1, I want t...Urgently need help with creating formulas
My worksheet is full of thousands of firstnames, middle names
surnames, email addresses etc etc. How do I create a formula that wil
result in having each persons *first name, initial of middle name
surname and email address * ?
I would really appreciate some help.
Kat Hughes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1674
View this thread: http://www.excelforum.com/showthread.php?threadid=31952
You didn't bother to tell us your setup so try thi...Changing Inventory Item Types #2
In GP7.5, is there any way to change inventory
item types once they have been used? Can the item
be deleted and then recreated at the end of a
year? Are there any utility programs that can do
this? In particular, I've got some "sales
inventory" items that I want to convert to "kits"
and others that need to be "services"
We are on 8.0, but I don't think it is possible to change the item type - not
even with Professional System Tools. We have changed the item name (via
tools) to include the word Discontinued. Then we set up a new item to
repl...How do i Select an A3 Publication to print on 2 A4's???
I was just wondering how i would tell Publisher that i want an A3
publication but i want it to print on 2 A4's.
currently, instead of using two A4s which would be perfect size, its using 4
A4's and only printing in the middle orners of the pages. how would i change
that so it only used 2 A4s
> I was just wondering how i would tell Publisher that i want an A3
> publication but i want it to print on 2 A4's.
> currently, instead of using two A4s which would be perfect size, its using 4
> A4's and only printing in the middle o...Help me #2
I have a MFC dll, in this dll I have a function. This is function's
prototype: int GetCellAt(double left, double top, LPDISPATCH* lpCell).
In this function I asign a valid pointer to lpCell. In the programe that
uses this dll I call this function but after calling this function lpCell is
When I change the function declaration like this : LPDISPATCH
GetCellAt(double,double) and in this function I return the pointer that I
asigned to lpCell. After calling this function I get an valid pointer.
Please tell me why?
In your first function, you have declared a &q...What is correct formula?
Can someone please explain me what is correct formula i excel.....I
need to markup or increase A1 for 30%
=A1*1.3 and that is 20931.75 (B1)
when I go to check =1-(A1/B1) i get 23%
=A1/0.7 and that is 23001.93 (B1)
when I go to check =1-(A1/B1) i get 30%
So what is correct?
so the problem is with your checking.
If you use a formula like =(B1-A1)/A1, that will give you 0.3 (or 30%) for the first calculation, and 0.42857... (or 42.857%) for
the secon...Subtotaling with formulas?
I have a large sheet, so I need some automated method to subtotal it.
There are a few different GFS CODES in one column and many different
BRANCH CODES in another. At the bottom of the sheet is a table of
various COUNTIF calculations on the GFS CODES. What I do right now is
autofilter the BRANCH CODES and load each branch individually. I then
update the named range to only the GFS CODES in that particular branch,
and finally print out the page with the updated COUNTIF calculations.
The SUBTOTAL tool can do one COUNTIF calculation, but is there a way I
can have a "table of GFS CODE COUNTI...What does Microsoft Project Levels 1, 2, 3, 4, schedules mean?
I'm a MSP new user and I want a detailed explanation to the following
What is MS Project 'Level 1' schedule?
What is MS Project 'Level 2' schedule?
What is MS Project 'Level 3' schedule? and
What is MS Project 'Level 4' schedule?
AFAIK, I never heard of such nomenclature in the context of the product
as provided by Microsoft. Are these words used on your project? Where
did you see these words written, or who uses them?
On 03/01/10 07:30, Alfred wrote:
> I'm a MSP new user and I want ...football formula Fixture table help
This is a multi-part message in MIME format.
Looking for a formula to locate a score from a results table place it =
next to the fixture list.
(backward sounding I know but from fixture list I can ascertain current =
form, but I cant from a table)
Created a fixture list I.e. Arsenal vs. Aston Villa is the first =
fixture. and I want to pull the result for each team from the table of =
Results Table Sheet
a bc...office:mac #2
I just purchased office:mac 2004 and was wondering what the seal is
supposed to look like. Mine looks like someone left off a small piece
of the seal in the middle with a silver line through it and red
writing on the silver line. Does that make any since? The guy at
Staples said it is supposed to look like that. Help.....thanks!
On 17/10/07 11:14 AM, in article
"firstname.lastname@example.org" <email@example.com> wrote:
> I just purchased office:mac 2004 and was wondering what the seal is
> supposed to look like. ...link formula to sheet.
I need a formula that will link to the same cell in
different worksheets. for example a1 will be c2 in
worksheet 1. b2 will be c2 in worksheet 2 etc.......
thanks for you help in advance.
"Tom" <firstname.lastname@example.org> schrieb im Newsbeitrag
> Hi all
> I need a formula that will link to the same cell in
> different worksheets. for example a1 will be c2 in
> worksheet 1. b2 will be c2 in worksheet 2 etc.......
>...send as public folder permissions #2
I am trying to give a user rights to send on behalf of the email folder. I went to Exchange Admin properties of the folder and under permission "directory rights" and "administrative rights" I've added the user and given all rights including "send as
Replicated all CD and login again but still getting an error "you do not have permission to send on behalf
any help will be greatly appreciated
Thank you L
...PST files #2
Is there a size limit for pst files?
Depends. In versions of Outlook prior to 2003 the limit was 2GB. Outlook
2003 can create Unicode PST files, however, which can grow quite a lot
larger if necessary.
Ben M. Schorr, OneNote-MVP
**I apologize but I am unable to respond to direct requests for assistance.
Please post questions and replies here in the newsgroup. Mahalo!
"John" <email@example.com> wrote in message
> Is there a size limit for pst files?
...Formula to Text to Formula
Hi there! Using E02 on XP. Have zillions of formulas to
create and if I can get my template going, it will be much
easier. Only problem is this: I used Replace to change all
my ='1'!'s with '=1'1! so I can see my formulas and use
copy/replace to create my boiler templates on multiple
worksheets. I have done this in the past and then just
replace in reverse and voila! I have formulas linked to
the correct worksheet locations. This time I keep getting
the Excel can't find anything to replace, check your
What am I doing wrong? I really do not want...asp.net page with framework 2.0 andajax
I initially made an ASP.NET page and after I added
AJAX code by entering the page is the initial declaration
<% @ Register
Assembly = "System.Web.Extensions, Version = 1.0.61025.0, Culture = neutral,
PublicKeyToken = 31bf3856ad364e35 "
Namespace = "System.Web.UI" TagPrefix = "asp"%>
then I have write the ScriptManager in this page.
In the web.config page i have write the tag
<add assembly = "System.Web.Extensions,
Version = 1.0.61025.0, Culture = neutral,
PublicKeyToken = 31bf3856ad364e35 "/></ assemblies>.