Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

[Solved] Using Keystrokes for absolute referencing

I am wondering if there is an easier way to make a cell reference absolute than typing in Dollar signs. Is there a keystroke you can hit when you select a cell for a formula that will make its reference absolute? Or Must I go back through all my formulas placing dollar signs??

Thansk for your help,

JAG


Post your answer or comment

comments powered by Disqus
I normally do not post across groups, but this one impacts each of the 3
groups that this is posted to.

There are many times when I would formulas and charts to refer to only the
workbook that it resides in, not the workbook that it was originally created
in. That is cause I have several workbooks with the same structure, but the
data is specific to the area it covers, which in this case, it's machine
center by machine center. With 15 such files, when there's some sort of a
change, this makes it very long and tedious work, which I don't particularly
care to do.

Formula writing:

If formula refers to only cells within the same worksheet, when you do a
copy and then paste in a different workbook in the same location structure
wise, this is no problem as it keeps the same exact formula.

If formula refers to cells within other worksheets of the workbook it
resides in, when you copy and then paste in the same place structure wise,
but only in a different workbook, the formula refers to the workbook that it
was copied from, not the workbook that it was pasted to. This is a problem
as I don't like to have to do. To get around this issue, I have had to wrap
all such references within an INDIRECT function and double quotes.

Example:

Befo Sheet1!$A:$A

In the above case, it would cause the pasting into a different workbook to
refer to the workbook that it was copied from. In the below case, it would
keep the same exact formula, which then forces it to refer to the workbook
that the formula resides in.

After: INDIRECT("Sheet1!A:A")

Note, in this case, you could include the "$", but it's not needed when
using the INDIRECT function. If your formula is long enough as some of mine
tends to be, you can actually run into the issue of the formula being too
long (too many characters within the cell). That is also another reason why
I had to use the INDIRECT function to get around this issue as the formula
would otherwise include the full path to the workbook, when then would
truncate the formula to the maximum number of characters allow and even
through in some unreadable characters into the formula.

Charts:

This problem is much more prevalent in charting than it is in formula
writing

First, with charts, you can not use the above INDIRECT function method trick
to get around the issue as Excel won't allow it to take place within the
charts objects.

There are charts that you either don't want to plot empty cells, so you
setup formulas to return the NA() function to have the charts plot properly
for what you are wanting the charts to do. Reason for this, if you have
such cells plotted as 0, then the chart looks all off and wrong in many
cases. If you have the formula return empty strings (""), then the charts
plots them as 0's, which is not desireable, thus why for the #NA! error
value return with the formula. In addition to this, you also setup the
Conditional Formatting so as for those cells that do return the #NA! error
message, the font color is the same as the Interior Background color of the
cell to effectively hide that value when it's printed.

Even then, there's still one more trick that I have had up my sleeve to try
since I use a central file to run all of my production reports. That is to
use VBA to change the FormulaSeries property on each of the series of each
of the charts, which I named all of the relavent charts to a meaningful name
while still keeping the name relatively short (10 to 11 characters in all).
Well this trick started to work, but ran into an issue that is still
considered as a problem by Microsoft in Article 139327. The article is only
listed under XL97, but also exists in XL2002 as that's the version I'm
using.

The problem comes into play when none of the values within the series are
plotted rather it be via the fact it not plotting empty cells as 0's or the
values of the referecned range contains the "#NA!" error message (In the
watch window, it would show the value of the cell as "Error 2042" and within
the series collection object, there would be no value object within the
collection for that series).

Given the fact there is no Value Object within the Series Object, it's
rather obvious as to why one can't set anything on the value's object, but
this is the first time that I have seen one not be able to programmatically
set properties that resides on a Collection Object itself due to the fact
there is not one single individual object within that collection object. A
rather large number of properties on the Series Collection Object is like
this. The only thing that I have been able to come up with to get around
this sort of issue is to use a code something like the following after
copying and pasting the objects and worksheet range from one workbook to the
various other workbooks with the same structu

Dim wshChart as Worksheet, strSerFormula

Set wshChart = Workbooks(strShortFileName).Worksheets("Charts")
strSerFormula = wshChart.Range("C57").Formula
wshChart.Range("C57").Value = 0
wshChart.ChartObjects("chtSetupLine").Chart.Displa yBlankAs = xlZero
wshChart.ChartObjects("chtSetupLine").Chart.Series Collection(1).Formula =
"=SERIES(Charts!$B$57,,Charts!$C$57:$J$57,1)"
wshChart.ChartObjects("chtSetupLine").Chart.Displa yBlankAs = xlNotPlotted
wshChart.Range("C57").Formula = strSerFormula

It would be so much easier if there was a much better way of copying and
pasting charts and formulas with such references and have the option to
refer to either the workbook that it originally came from (Absolute
referencing) or to refer to the workbook that it's pasted into (Relative
referencing).

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

Hi

I would be very grateful for help in the following:
I am creating a spreadsheet which I want to update on a weekly basis. I
would like to be able to make a point have a value i.e.

1 point = £75.00

I would like to reference the cell so that when I enter increased points in
one cell
it will show the value in pounds in another cell and update whenever there
is a change.

I am sure that I have done this before (absolute referencing with interest
rates??)
Can anyone point me in the right direction? Thanks in advance

Hi

I would be very grateful for help in the following:
I am creating a spreadsheet which I want to update on a weekly basis. I
would like to be able to make a point have a value i.e.

1 point = £75.00

I would like to reference the cell so that when I enter increased points in
one cell
it will show the value in pounds in another cell and update whenever there
is a change.

I am sure that I have done this before (absolute referencing with interest
rates??)
Can anyone point me in the right direction? Thanks in advance

Hi All

I need to convert a large number of cells in a column
to absolute referencing. How can I do this without having to do it one by one thru the F4 function. As I am cross referencing with other sheets in the same workbook, I don’t want to disrupt the formulas that are already in place.

So, I am looking for a way of doing bulk absolute referencing.
This solution will be a life saver...especially on this Friday night

thanx

For example, let's say I have 'autofilter' at the top of my column called
'Date.' How do I use keystrokes instead of the mouse to activate the drop
down box so that I can select different dates? Thanks.
Leslie

Dear big brother and sister,

I have a tricky task. I hope you can give me reply. appreciate your help.

I have the following macro. However, sometimes, it doesn't work, it may be because it doesn't applied on specific sheet. May I ask how can I modify the following macro to let it run on specific sheet only.

Secondly, as you go to sheet "before macro", after I use macro for Sub Absolute() (all cells selected are absolute reference", it returns to VALUE. May I know why?

Thanks

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub


Hi,

I'm looking on how to use an IF statement for a referenced cell. For example, I attached a simple excel document and in it shows that cell A2=B2 and A3=B3. B2 contains a valid text when B3 doesn't. How do I get it to show for A3 that if the cell contains "0", it will change the cell text to something like "THANKS FOR EXCEL HELP". I know how to do this for non-referenced cells, but I'm just confused on how to do this for referenced cells, THANKS!

excel jr

XL98: Using "Most Recently Used" Category for Functions

... in the Formula Palette and in the Paste Function dialog box contains the last 10 functions ... Sum Average If Hyperlink Count Max Sin SumIf Pmt ...

I am trying to run a macro that will repeat several For statements within a For Statement. I have attached my macro. Maybe I shouldn't be using a For statement to repeat this macro. Any suggestions?

Thank you very much!

XL98: Using "Most Recently Used" Category for Functions

The most recently used function list is not updated if you are using a custom function. The list is updated only when you use the functions that are in Microsoft Excel by default

How can I read the values in Column B and assign new values multiplied by 4 in column D using the For loop.

For Example:
I have the follwing values in column B

2
3
4
5
6

In column D , I want to display the values like this :

8
12
16
20
24

The column B is dynamic.

Thanks.

im trying to set some error trapping in a staff planning rota and have been using a for each statement to check conditions over a range of cells if a cell doesnt meet a condition a msgbox is triggered and excel locks the user into the cell until they correct the error

one part of the code works lovely but unfortumately the second piece to validate the totals is driving me nuts as i cant think how to lock the user into the incorrect cell

sorry description is vague i have attatched a working version of document in question a few points as it is in a testing phase

first code only checks for valid start times not for error values

second code only checks for round numbers in the total column

just in case you spot these other errors

any help appreciated in advance

I am trying to remember how to do this.

I want to create a column of numbers, 10 rows deep, ranging from 1 to 10.

I have the below formula using a for each statement. How would I modify this just using a straight For loop:

Sub RangeofNumbers()
x = 1
For Each cell In Range("A:A10")
cell.Value = x
x = x + 1
Next
End Sub

I haven't used VBA in a while so it is somewhat fuzzy at the moment.

Thanks

I'm using the autofilter and would like to know how to check and uncheck boxes using keystrokes. I can make it through the drop downs using keystrokes but I can't figure out how to check and uncheck the boxes.

Thanks

Hi,

I want a useful code for detection of the peak and calculation of the its area or height in complex matrix....
any help???

regards,

Quick newbie question:

I have a spreadsheet with first name (column A), then last name (column B).

The idea is to have column C be the email address (which, at this place, is firstname.lastname@companyname.com)

So I managed to cobble together the following formula into C1:

=A1 & "." & B1 & "@companyname.com"

So, it works, but when I try to cut and paste into C2, I get the same name from C1. So I go back and change A1 to A2, B1 to B2..... you get the picture. Might as well type the email addresses.

Do I have to edit the formula 500 times? I'm sure there is a dirt simple way, and I apologize for wasting the experts time with a simple question like this, but I really don't use Excel and this will probably be the last time I bug anyone for help....

thanks.

(Read the rules: I searched, and tried several thing to no avail. I'm using Excel for Mac 11.3.5)

Hi,

I have a macro and it uses the for loop

For i=1 to 100
Call Macro2
Next i

Then in Macro2 there is another For loop.

Question = is it ok if the "For" loop in Macro2 uses the variable "i".
Should I declare the variable i in each macro, or declare a unique variable
in each macro.

Thanks for your help

I would like to adjust column widths using keystrokes instead of the mouse.
Is that possible? You know how you normally highlight a bunch of columns and
then double click at the top to automatically adjust all the widths? That's
what I'd like to do without using the mouse. Thanks.

IN Excel 97
Within the same workbook, I'm wanting to create a new worksheet, but I want
it to have the "exact" same values as an existing worksheet. An exact
duplicate, however, copying it won't help, because I want it to pick up any
changes made to the original worksheet. I want to simply change the rounding
for charting purposes, but need it to have same values, so all the cells need
to link to the original sheet.

Here's a sample of what I've run into.
When I tried to use the $ for absolute value (refering to the original
sheet), it doesn't pick up that the number is a calculation "and" is
converted to a percentage.
I'm miffed by it all.
Perhaps there's a way to copy, but tell it to link to the original sheet, at
the same time (I'm guessing).

Thanks for your help.

I'm working with Excel 2003 and trying to look for a way to create some sort
of dynamic references in functions between worksheets in the same workbook.
Let me try to explain -

Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...

On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a certain column (in this Example - Column C) from the worksheet
with the name in cell A2.

I was thinking that I could use a function that would look something like
this -

=max($A$2!C:C)
When Cell A2 will say - April, I'll get the heighest value from Column C on
Worksheet "April", when It'll say - June, from worksheet "June", etc...

I get an error message for this function and it will only work for me if I
use a constant value. for example -
=max(APRIL!C:C)

Is there a way to use variables for worksheet names in functions in Excel?
How should I write this function?

Can anyone help me with this simple problem? I would like to create a drop
down list with a "tick" mark and a "x" mark as the available values. These
are symbols from the wingdings font available from insert->symbols menu.

When I originally created the reference cells with the symbols to use as my
source for the drop down list, the symbols appear as characters such as "û"
or "ü" in the cell with the listbox. I tried to then adjust my list box
settings in the data->validation menu by typing in the actual character code
for the symbol in the source box; For example, by pressing alt+252 which is
the character code for the tick mark that I want to use. This still did not
show the tick mark.

Is there any functionality within Excel that would allow me to use symbols
for a drop down list?

Hi guys I was wondering what is the easiest way to let the user input the column (i.e "A") then use it for comparison
for something like


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i is my counter

Hi

I encountered some problems defining a range with variables. I checked some posts here in this forum as well as other web sources but couldn't get it to work.

Here's the situation: In column D are values going from 0-360,0-360,0-360 and so on. My program should locate where it switches from 360 back to 0 and separate the values into blocks. I used a For Next Loop to find the first value that is 360. Then I want to redefine the range using a variable (the variable is the row number of the first value + 1). So far I haven't had any luck.

Basically what I need is a piece of code that selects a range using variables (FirstRow, LastRow, Column).

Can anyone help me?

Thanks in advance

I would like to start learning to use VBA for Excel 2003. Can somebody help
me in starting? What are the limitations? For what kinf of applications can
we use it?
What's the difference betwwen ActiveXContrlos and Forms? When to use which
one?

Thanks

Kamil


No luck finding an answer? You could always try Google.