changing many cells reference to absolute
I have a huge spread sheet and need to change the cells from relative to
absolute reference. In other words the cell reads =A2 and I need to change
it to =$A$2. I have hundreds of cells. Anyway to not do this manually,going
into each one? Thanks
You would need VBA to make global changes to cell references.
Here are four........
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
Dim cell As Range
...Different Cell Sizes in the Same Column?
Is it possible to have different cell sizes in the same column
If it is possible, how do you size the cells differently?
All cells in a column have the same width (they can have different
Cells in adjacent columns can be merged to form a wider cell unit. This
can cause problems with sorting/formatting/copy&pasting, so I rarely
In article <24D650A3-564F-440D-8926-480AF023D208@microsoft.com>,
Zaebos <firstname.lastname@example.org> wrote:
> Is it possible to have different cell sizes in the same column?
> If it is possible, how do you size t...How do I correct scroll bar range?
I have a worksheet where the scroll bar range is A through WVL where all I
need is A through W. Is there a way to correct this?
Debra Dalgleish shares a few ways here:
Tom Freeman wrote:
> I have a worksheet where the scroll bar range is A through WVL where all I
> need is A through W. Is there a way to correct this?
If there is nothing in the spreadsheet to the right of column W, then
highlight columns V through WVL and then Edit | Delete. Then Save the
worksheet. When you open it, th...Display ranges in a Pivot
I have a column called IncomeRange. I need to display the value in ranges
like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
display as ranges? Is this an option or do I need to do some kind of formulas
Thanks for all help
Just wanted to explain a little more.
My requirement is to create a pivot report in which a column called
indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.
The source of data for the pivot is a SQL Server query. I am not sure where
or how can I do it, so I am posting this in both g...Autofilling next empty cell in column?
Other than copying formulas manually to the next row of empty cells, can a
function within the preceding data entry cell do the work upon completion,
<enter>, or auto summing?
All cells within the range will have entries, so no isolated empty cells are
present to mess up the integrity. These are scientific, engineering functions
with no limit to range of entries.
Having searched through several manuals and this forum, I'm not too
optimistic for a solution, but keep trying...
You can create a macro that does this.
Otherwise, if you double click on th...Formatting cells for worksheet data transfer
Sheet 1 contains two columns. Each column lists a hockey roster. Column A
is the "White" team. Column B is the "Dark" team. There are 22 spots
consisting of regulars and numerous substitutes. Any one player may appear
on "White or "Dark" from week to week. At the bottom of the rosters will
appear the score from a given nights game. On Sheet 2 is a list of all
players (regulars and all subs) in column A. Column B is "Wins". Column C
is "Losses". Column D is "Ties".
My question is: how do I format this s...EXCEL can't sum cell with SPACE " "
I have imported data from some text file to do sum in EXCEL, as it is
always easy to SUM with EXCEL.
But here I surprise EXCEL can't do that simple sum,
B4 has value "339 " in cell seen as 339
B5 has value "2079 " in cell seen as 2079
Now i tried with =SUM(B4:B5), yield 0.000
I have checked what's wrong with in base cell, found excess space" �
after last digit.
I have billions of data to sum; I can't remove that space manually.
i am attaching that file for your ref.
i have also tried with TRIM() function but that also didn't work
Any function o...Concatenate text & number cell
I need to concatenate a word and a number cell. eg,
Concatenate("Limit = $",A1) where A1 = $100,000. It shows
up like this: "Limit = $100000". However I want the
number to format with the comma between the thousand,
ie. "Limit = $100,000"
Does any one know how to do this. Thanks in advance.
="Limit = " & TEXT(A1,"$#,##0")
In article <email@example.com>,
"John" <firstname.lastname@example.org> wrote:
> Hi all,
> I need to concatenate a wo...Link cell to another workbook
In Excel, how would I go about linking one cell to another cell in a
different workbook? I understand I can type = then point it to the cell in
the other workbook, however my problem is everytime the location of the file
changes, the links mess up.
For example, I have this command to link to a file called grid:
But when I want to move both the original file and the grid file to a
different location (for example on a memory stick), the link fails and tries
to point back to where the file was originally stored. Is it possible to use
an ...How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS
And when I export as Comma Delimited or Fixed Length Ascii, I need for it to
remain in that format.
Ditto for Date as YYYYMMDD
I've played around with both =TIME and =DATE but no luck.
Can anyone tell me how to do this?
AND, once the forumula is in the cell... how can I get it to update to the
current time or date?
thanks for any help
I formatted my times as hhmmss and my dates as yyyymmdd and then did a
I chose the comma separated values (*.csv) and I got what you wanted.
I opened it in notepad to ver...Deleting #N/A from cells...
I have values in cells C1-C162. However, some of the cells have #N/A in
them. Is there a quicker/better way to delete them all from the cells
instead of highlighting them all and deleting them? Thanks
"Jambruins" <Jambruins@discussions.microsoft.com> wrote in message
>I have values in cells C1-C162. However, some of the cells have #N/A in
> them. Is there a quicker/better way to delete them all from the cells
> instead of highlighting...Display last row entered
Hi, on a large Spreadsheet, with Data added daily, I would like the last
row of Data added to display on Row 1 at A1, B1 etc. Please advise on
the best option, is it by using the Vlookup formula??
tallpaul's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8453
View this thread: http://www.excelforum.com/showthread.php?threadid=496781
From my notes copied from another message I know that:
=LOOKUP(9.99999999999999E+307,Sheet1!A:A) will return the last numeric value
...Compare dates (one cell not in date format)
I have one column of cells in date format (6/14/2005) and anothe
column of cells which also contains a date pulled from a database bu
this second column is not in date format, it comes from the databas
like this 2005Jan20. I need to compare the two dates to work out th
number of days between the dates but I can't convert the second date t
Is there any way I can work out the number of days between the tw
Thanks in advance
craigcsb's Profile: http://www.excelforum.com/...Referenced cell displays formula, not data
I downloaded a form template and linked it to my data; when I use the = sign
to insert my data into the template, some – though not all – cells display
the formula of the cell reference rather than the data that is actually
contained in the cell
Cell D10 in datasheet contains text; instead of displaying the text in the
template, it shows the following formula that is the reference address:
Press CNTRL + TILDE Key whch is available above the Left Tab Key.
Tilde key (`) or (~)
Remember to Click Yes, if this post helps!
...I want to type a text in a cell, but I want that text to represen.
I want to type text into a cell (actually the checkmark symbol in winding2)
but I want that text to represent a number. So if I have 3 cells each with
the checkmark and each of those cells represents the number 1 when I auto sum
I will get a total of 3.
If they represent 1 you can just count them with countif
> I want to type text into a cell (actually the checkmark symbol in winding2)
> but I want that text to represent a number. So if I have 3 cells each with
> the checkmark and eac...how to split cells?
Can anyone help me?
wsijbesma's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35063
View this thread: http://www.excelforum.com/showthread.php?threadid=548078
on the tool bar...
data>text to columns.
follow the wizard. caution.... this deletes data to the right when the cell
you can also use formulas.
cell A1 has the word "text in it.
in cell B1...
=right(a1,2) will return "xt"
in cell C1...
=left(a1,2) will return "te"
in cell D1....Cells formated as text do not always display properly
I am using Office XP and Windows XP. I have formatted a column of cells as
text and word wrap is checked, horizontal alignment is Left, Vertical
alignment is Bottom. I use these cells to type a short narrative. The
problem is some of the cells will not display properly, all they show is
######. The row height will expand to hold the text sometimes but not always
but it will still only show ######## instead of the text I typed. If I change
the cell type to General all the text is visible. This does not happed to
evey cell, only some of them. I can type several rows of data with a
narr...Protect cells with formulas only
What is the best way to only protect cells with formulas in them?
Not sure if there is an easier way, but you can:
*Select the entire sheet (you can do this by clicking on the icon above row
1 and to the left of column A)
*Right click in the sheet and select FORMAT CELLS
*Go to the Protection tab and UNCHECK LOCKED
*Now, go back to your sheet, right click on the cell(s) you want to protect
and again select format cells
*Go to the protection tab and CHECK LOCKED
*When you are done selecting the cells to be locked go to the ribbon and
select t...Locked cells are still able to be selected with proper protection
An excel worksheet I'm developing has rows that alternates between locked and
unlocked cells. When I protect the worksheet with the option for users to
select unlocked cells and format cells checked I'm still able to select and
format locked cells by drag the cursor vertically from one unlocked cell to
another. Is there a way to prevent this from happening?
Any help would be appreciated!
Select the whole w/sheet < format < format cells < protection < uncheck then
check locked < ok
Select the areas you need to allow access
Format < format cel...Move to a specific cell on exit of current
I'd like to move to a specific cell on exit of the current cell. Can
jafo1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2459
View this thread: http://www.excelforum.com/showthread.php?threadid=38184
Private Sub Worksheet_Change(ByVal Target As Range)
Put this in the appropriate sheet module.
"jafo1" <jafo1.1r41ms_111958238...autoformat the column of a cell area
Is there any way to format one cell are so that that the width of a
cell area (something like format column width to contents) but without
formating the cells above or bellow the area I want to format
I have tried autoformat but it really does not give me what I want !
Somehow I cannot remove the autofilter that appears in the formated
Thank you, best
Personal.xls is located in the XLStart directory, and is used to store
macros and things that you want to be available to all workbooks, whenever
you start Excel.
By default, it is hidden in Excel, but if yo...Color a certain range of cell if a cell contain specified word
a b c d e
f g h
if a cell contain the word "Group" + a single char, i want the row which is
the same the cell containing "Group" to be autohighted
In tis case, it will be Cell A3-H3, and Cell A9-H9 to be auto-highlight!
Put cursor on col A>format>conditional format>formula
is>=left($a4,5)="group">format as desired>copy format (use format tool) to
col e then copy format the row down/up as desired.
I have inherited a spreadsheet that has a cell that whenever you put the cell
pointer over the cell a note pops up with a description of the cell and notes
about the cell that someone apparently entered. Its not a comment and can
not be edited by comments. I have never seen this before. Does anyone know
how to edit these notes or delete these notes?
Maybe it's from Data|Validation|Input message tab
> I have inherited a spreadsheet that has a cell that whenever you put the cell
> pointer over the cell a note pops up with a description of the ...Jumping to a hidden cell on enter
I've got 3 drop down lists in which the source cells are located i
hidden rows above the data cell. Each of these 3 data cells are righ
on top of the other (of the unhidden cells) and every time I use one o
them and hit enter, rather than going to the next unhidden cell down
it goes to a hidden cell. I can see it displaying the cell destinatio
and it's contents in the input window. I try to unhide then rehide an
it's always the same cell it goes to upon enter. Does anyone have an
ideas about what is causing this glitch?
---------------------------------------------...Data validation master cell
Is it possible to change what list is in a cell by selecting the name o
that list from a "master" cell?
For instance, say the master cell is A1, and it's validation lis
contains the list "cats, dogs, horses".
The list in B1 would be based on what list is selected in A1. Fo
instance, if A1 was set to "cats", then the choices in B1 would b
something like, "Calico, Tomcat, housecat, Siamese".
What I really want to do though, is only have one "master" cell an
have dependent cells that change depending on the master.
So, if A1 is the master,...