Free Microsoft Excel 2013 Quick Reference

Absolute Worksheet References

Hi there,

I read that (O'Reilly Excel Hacks, page 192) if you use "absolute"
Worksheet references that if the actual worksheet name changes that the
application will continue to work. This is true but I am using Excel
2003 and keep getting a reference not valid message, although the macro
still appears to work. Is there any way to fix this so that the
message does not appear?

To make myself clear:

Normally to reference a worksheet you can use:

1)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets("Sheet1")

OR

2)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets(1)

However, if you change Sheet1 to another name, (1) above breaks. If
you change the worksheet position within the workbook, (2) above
breaks.

However you are supposedly able to just reference a sheet by the name
given to it in the properties window. So looking in your Project
Explorer under Microsoft Excel Objects if you use the Codename of the
worksheet rather than the name you have given it (i.e. Sheet1, etc.)
you can reference the sheet directly without going through the above,
wiht the added benefit of it being (supposedly) impervious to worksheet
name changes... Except that it gives you this annoying Reference not
valid message.

Any help on getting the message to go away?

Regards,

Ric


When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

I have a spreadsheet that is a weekly P&L that combines at the very end. So
it has 52 columns on it that are labled week ending **/**/****. I can export
the data from my accounting program into a spreadsheet but it is not in the
same format as the worksheet we currently keep the weekly tracker in. The
accounting program exports weekly to the same file name it never changes. I
wrote a macro in cell AD to pull the info from the named worksheet and all
the way down the column. Totals out and looks just like the printed P&L.
Problem is Next week is Column AE.. I run the macro and it just inserts over
AD. I need it to be an absolute cell reference for the exported worksheet
from the accounting program but an active cell paiste to whatever column I
put the cursor in before running macro.

I wanted to invert the worksheet so that I could just do current week in
Column 1 and when the macro runs insert column to move everything over.
Problem is the bosses have used this same worksheet forever and like the
current format.

Any help??

Hello:

I am working in Excel 2003. I have a worksheet designed to summarize data from previous worksheets in the same workbook. I am having trouble getting Excel to properly copy cell formulas as I expand the summary worksheet. Worksheet names are LOAN 1, LOAN 2, LOAN3, etc. Formulas in each column are as follows:

Column A =T('LOAN 1'!H49)
Column B =T('LOAN 2'!H49)
Column C =T('LOAN 3'!H49)

If I use Autofill or manuall copy and paste the formula in Column C to Column D, Excel inserts the formula: =T('LOAN 3'!G49).

The formula I WANT in Column D is =T('LOAN 4'!H49).

In other words, I want the worksheet reference to change, not the cell reference.

I know how to fix the cell reference by changing it to "absolute", but is there a way to get Excel to automatically change the worksheet reference automatically as I expand (i.e. as I add Column E, Column F, Column G, etc.)?

This will take forever if I re-type the formulas manually for each new column. Thanks!

Hello,

I have created a couple of userforms that will initially create a new customer worksheet with the customer's ID as the worksheet name and then another that can be used to add data to the last row of a customers sheet (as the customers details, discounts, etc. change)

I now need to create a dashboard sheet that shows the latest information for each customer (the customers 'current situation') I am able to do this manually by referencing the different sheets, however, as our customer base grows it is becoming a full time task to do this.

Does anybody have any ideas on how I can have the formulas required inserted onto the dashboard sheet with the worksheet reference for the formulas taken from a textbox in a userform (The same one used to name the new customers worksheet)

Ideally I would like this to be placed in the same macro used to create the new worksheet so the dashboard entries are created at the same time as a new customer's worksheet, but any way you can think of that would speed up the process would be a great help.

Many thanks,
Colin.

Here's the question:

I need to try to find a way to imbed a workbook/worksheet reference into a function referencing a workbook. That really doesn't make too much sense, so here is an example I am going to try to lay out.

I am working in Workbook A, Sheet a:

=vlookup($A1,'[Workbook B]Sheet abc'!,$A$2:$Z$200,FALSE)

Here is the problem, the worksheet "Sheet abc" changes so that I would need something along these lines, but this doesn't seem to work:

=vlookup($A1,'[Workbook B]'[Workbook A]$B1'!'!,$A$2:$Z$200,FALSE)

'[Workbook A]$B1'! would bring back the variable worksheet name necessary for the proper lookup reference.

Something that would allow me to lookup the sheet name that is in B1 and use it to lookup the value in Workbook B, with the variable name in B1, for the value in said range.

Is there any way to make this type of reference?

HI ,

I have a workbook of 100 worksheets , the 1st one with a table of about 10 columns & 100 rows , each cell within this table contains a formula .

all these formulas have worksheets references (some times for 4 or 5 times in each formula)

for sure I start with the 1st row formulas , then drag it below , then I manually edit all the formulas below . ( it is sooooo hard work to do , beside mistakes could easily occurred )

is there any way to make these references being auto filled ??

thank you very much

Does anyone have a Macro to convert vlookup formulas to absolute cell references?

I would like convert this formula to easily audit data (trace dependents / precedents) once the fuction is performed.

Hi. I'm new to this MS Excel '07 and I am in a business class and i'm trying to learn everything on here. But i'm facing some challenges. For Example, My book says In cell D6, type a formula that multiplies the absolute cell reference $B$3 with the percentage in cell C6. I don't know what type of formula i'm suppose to use and I dunno what the $b$3 is. If anyone could offer some feed back or help it would be much appreciated. Thanks. Have a wonderful day.

Hi Excel Gurus,

Does anyone know how to add to an absolute cell reference?

For example, I have the following entry in a cell for a count statement:
$P$2:$P$22

I want to be able to move this down by 11 rows by adding 11. The result would be:
$P$23:$P$43

Any and all help would be awesome.

Thanks,

The Novice

Hi,

I have a workbook containing approx 60 worksheets (15 stock categories x 4
worksheets). The 4 worksheets for each category are the same (replicated 15
times). One of the 4 worksheets contains a set of graphs (that graph data
from the other 3 worksheets) that I have replicated for each category.
Whilst I have been able to update the forumlae (worksheet references only-
cells references remain the same) in the other 3 worksheets for all 15
categories using find/replace, this doesn't seem to work for graphs. All the
cell references should be the same, it's just the worksheet references that
need changing to reflect the respective category.

Is there a way I can change the worksheet references in all the graphs to
reflect the related category of stock without going in to each graph for each
category of stock?

Any help gratefully received...!

K

Hi,

I have the following "minor" headache:

I have on one summary worksheet different company names in rows with
their corresponding key figures (formulas) in the columns. Every
company has a corresponding worksheet, that is named after the company
(done automatically with a macro.) My question would be if it is
possible to have the following formula/reference in the individual
colums:

company A =Worksheet of company A $A$1

company B = Worksheet of company B $A$!

that I could basically copy down every column and which would take the
worksheet reference from the column with the company names.

Cheers

Lars

--
lars22222
------------------------------------------------------------------------
lars22222's Profile: http://www.excelforum.com/member.php...o&userid=25603
View this thread: http://www.excelforum.com/showthread...hreadid=390167

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...

I have a large workbook with several difference sheets. The user can
make changes to the first sheet and information that is also on the
following sheets changes with it because I have absolute cell
references to the first sheet. However, when the user drags and moves
infromation from one cell to another, say from G13 to G15, then the
following sheets that are linked to those cells gives a reference
error because the formula has changed. I thought by doing the
absolute cell reference in the formula, =Sheet1!$G$13, that if the
user moved information to different cells on Sheet1 that formula would
not change so that the appropriate cells on the following sheets would
update as Sheet 1 was updated. Does anybody know of anyother method
that I can do so that I dont get a reference error?

I would like to suggest that, when copying a range with formulas containing
absolute cell references, Excel should copy the reference as absolute if the
referenced cell is OUTSIDE the copied range, but as a "relative" absolute
reference if the referenced cell is WITHIN the copied range.

For example, suppose a table (in range A5:G15) includes formulas which refer
to the address $D$10 (which is part of the table) and also to cell $B$2
(which is outside the table). To create a duplicate table, one would copy
the range A5:G15 to another location (for example, K5:Q15). As Excel
currently funtions, the formulas in the new table would still refer back to
cells $D$10 and $B$2. One has to manually change the formulas to refer to
cell $N$10 (in the new table) instead of $D$10 (in the old table).

I would suggest a feature (possibly user-selectable within Options?) that,
in above example, would copy the $B$2 reference normally, but would copy the
$D$10 references as $N$10. That is, absolute references to cells WITHIN the
copied range would copy as absolute references to the same RELATIVE cell in
the new pasted range. This would allow the copying of large blocks or tables
such that all references within that block function similarly to those in the
original block.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I have a several rows of formulae that refer to a constant at the upper left
corner of the range of formulae. I use an absolute cell reference to refer
to the constant, and many relative relative references in rows of formulae.
I want to copy this range of work and paste it several rows below the
original. However, I want to use a different constant for the second range
of formulae. Since I used an absolute reference in my original work, the
pasted formulae still refer to my original constant. How can I make the
reference to the original constant be absolute, yet relative to each range of
formulae so I can change the constant each time I paste the range of
formulae? For example:

A1=7
B2=100, C2=B2+A$1
B3=105, C3=B3+A$1
B4=110, C4=B4+A$1

Now, copy this range A1:C4 and paste down at A15, now we have:

A15=7
B16=100, C16=B16+A$1
B17=105, C17=B17+A$1
B18=110, C18=B18+A$1

However, what I really wanted was for A$15 to be my new constant (so I could
change the number in that cell) for this new range of formulae. My actual
formulae are much more numerous and complicated that this and I am trying not
to have to go in and manually change each cell reference for the constant on
my new range. It seems like I need some kind of formula in my original
constant cell reference (A$1) that will allow it to change when it is pasted
elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
make them work. Many thanks in advance for your thoughts.

I have 13 spreadsheets that contain 4 weekly salary information. I have
another sheet (YTD) which using absolute cell references from the other 13
sheets basically give a running total and then compares these to Budgets.

The problem I have is if I add/delete rows in any of the 13 main sheets,
then the totalling does not work due to the absolute cell references. I have
to subsequently change these references to pick up up the new absolute cell
references. Is there a way that will allow me to add/delete rows in the 13
sheets and automatically adjust the absolute cell references?

If the answer is No, then is there any other functionality within Excel that
can be used?

Any help given would be greatly appreciated.

This might be in the program right now, but I think having something like
ctrl+click (or anything like that) when entering a formula, make it an
absolute cell reference

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

When selecting a cell to enter into a formula, there is a key or key
combination that will automatically make the cell an absolute cell reference
when you point-and-click. You can also highlight the cell reference in the
formula and use the shortcut to make it an absolute cell reference. This
saves typing the "$" sign. Help! I can't remember it.

I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have a
column in the consolidation worksheet that pulls data from a source worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I can
get this to copy into adjacent, assending columns such that the worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9, ='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point but I'm
sure there must be a way to accomplish this.

Hi Java

Do you have F Lock key on that keyboard (next to F12)
Press it one time to get the normal behavior back

I don't like it also (I have a wireless Microsoft Natural Keyboard myself)

--
Regards Ron de Bruin
http://www.rondebruin.nl

"java" <java@discussions.microsoft.com> wrote in message news:A9D96C45-99D4-43EA-9F94-5B5F7F6A0418@microsoft.com...
>I am using Office 2003 SP3, and am also using the Microsoft Natural Keyboard
> Elite (which may be the problem). Using my F4 key to toggle between relative
> and absolute cell references does not toggle as expected; in fact there is no
> toggling performed. I had no trouble with a standard keyboard, but when I
> switched to the Microsoft Natural Keyboard, the F4 key no longer works as
> expect.
>
> Is anyone aware of a fix for this problem

When selecting a cell to enter into a formula, there is a key or key
combination that will automatically make the cell an absolute cell reference
when you point-and-click. You can also highlight the cell reference in the
formula and use the shortcut to make it an absolute cell reference. This
saves typing the "$" sign. Help! I can't remember it.

Hello everyone,

I ran into a problem trying to get a worksheet reference to work over the network. Here's the situation:

Spreadsheet A is doing the referencing
Spreadsheet B is the one being referenced

Spreadsheet A is public
Spreadsheet B is a private document requiring specific rights to access

If spreadsheet B is made public, the worksheet reference works, so it's definitely a rights issue.

How would I go about having Spreadsheet A perform a reference on a private document? I'm guessing this would involve polling the login/password at the spreadsheet level?

Thanks in advance,

Greg

Attempting to copy several Absolute cell references at the same time.
When I Copy one cell and Paste Special / Paste link, the cell is successfully copied as an absolute reference. However, when I attempt the same steps to copy more than one cell at a an time, It does not copy as an absolute reference.