Free Microsoft Excel 2013 Quick Reference

Copying formulas relative and absolute cell references Results

I am looking for a way to copy a range of cells somewhere else without
the formula references changing. For instance I want to select B1:B3
and copy to C1:C3. Say they contain the formulas =A1+$A$1, =A2+$A$1,
A3+$A$1 but I want C1:C3 to still contain =A1+$A$1, =A2+$A$1,
=A3+$A$1.

I don't want to change from relative to absolute.

The only ways I have found is

1) Create a new sheet, Copy B1:B3 into the new sheet at the same
position (B1:B3). Then select the cells in the new sheet and move them
(with drag and drop) to C1:C3, then copy them to the original sheet
into C1:C3.

2) There is another trick where you replace the = in the formula so
Excel is fooled into not changing it, but that is even more labor
intensive.

Anyone know a better way to do this?

Murphy.

Hi Everyone,

I would like a Macro that Inserts the Following Formula into an Excel
Sheet in the Cells "C31:J95" Please. It will have the Format of "%" and
be Two Decimal Places. I am Finding this Difficult Because of the
Relative and Absolute References etc. I know that if I Insert this
Formula into Cell "C31" and Copy Across and Down to Cell "J95" it
Works.

Here is the Formula :-
=IF(ISERROR((COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30)),"
",(COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30))

Thanks in Advance.
All the Best.
Paul

I have two worksheets that are linked and each is on a different computer. I have columns of cell values on one worksheet that just reference the content of the other worksheet. This was built using autofill which will not copy absolute cell references so everything is in relative cell reference. I now have to insert rows on the reference worksheet which in turn knocks out of alignment all other formulas on the second worksheet. I have many columns to insert absolute cell references into and need to know a shortcut. Any help would be apreciated.
Thanks, Wiz.

I am trying to create an Excel formula similar to "=Sheet3!B4" but would like the worksheet to be an absolute cell reference and the cell reference to be a relative reference.
I have tried "=INDIRECT($A$1&"!"&"B4")" where A1 contains "Sheet3" but when copied to the right one cell I still reference cell B4 on Sheet3 instead of C4 on Sheet3.
Can anyone help?
TIA

I've been testing the countif function and for some reason it isn't working for me.

I have the following text in cells A1:A3
"apple"
"banana"
"peach"

I have the following text in cells C1:C3
"apricot"
"banana"
"grape"

I put the following formula in B1 and copied down into B2 and B3.
=COUNTIF($C$1:$C$3,A1)

It is meant to count how many of each fruit in A1:A3 is also in C1:C3.
The banana is the only one that is in both lists so the formula in B2 should return 1.

But all three formula's return zero.

If I change the range in the countif formula to relative it works. eg., =COUNTIF(C1:C3,A1)

Can anyone see why this is?

How do I return an address reference that is relative and not absolute? When ever I use the following code;

Variable1 = ActiveCell.Address

I get an absolute reference (i.e. Variable1 = "$A$1")
How can I get it to return "A1" so I can use this variable to create a formula and then copy the cell formula to other cells and get a relative references.

Is there a VBA function that mimics the F4 key to change referencing?

Hi,

Wigi was kind enough to help me develop a code for a log template I'm creating. One closely related but different problem from my original request for help is how to use a paste function in relative mode inside a loop. I'm attaching the code below. The line in the code that reads "Selection.FillDown" only works if the cell above a newly created row contains a formula. If it gets replaced with a value then the functionality I need is destroyed. How could I instead copy and paste a formula I hid in cell F2 or (2,6). I know how to do this in absolute mode, but that won't work because I need the formula to reference the cells in the newly created row.


	VB:
	
 CommandButton1_Click() 
     
    Application.ScreenUpdating = False 
     
    Dim lastRow As Long 
    lastRow = Range("B65536").End(xlUp).Row 
     
    For i = 3 To lastRow 
        ActiveSheet.Cells(i, 6).Select 
        If ActiveSheet.Cells(i, 6).Value = "" Then 
            Selection.FillDown 
        Else 
        End If 
    Next i 
     
End Sub 

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


When setting a cell to reference another cell in an open workbook, I usually press or click the "+", then use mouse commands to get to the cell I want in the other workbook.

The problem is that when I do this, Excel assumes I want an absolute reference (i.e., $a$1). Actually, I want a relative reference so I can copy the formula to other places in the sheet and have the reference roll dynamically.

I'm sure there's a setting somewhere, but I can't find it.

Thanks,

JPF

Hi All and Happy New Year,

I want to write a formula like:

=INDIRECT(COLUMN()&"1")

but the obvious drawback is that column returns a numeric and not an alpha
reference.

The reason I want this is have a formula that can be copied down and across
that refers to the top cell in each column to which is copied.

Hopefully my header line makes it clear that playing around with "$" signs
is not the answer.

I could put in a table with 1 to 26 and A to Z in it and run a Vlookup over
it but that just seems too OTT.

Probably missing the bleeding obvious again - that and 2.5 weeks off with
primo SI NZ sunshine (and beer) rotting my brain.

Cheers,

Matt

Hello,
I am looking for a formula that will allow me to copy either text or
numerical data in sequence for every 8th cell. The problem that I have is
that excel uses the relative/absolute reference and copies cells in sequence.

Example:

A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3

A1=1 B1=1
A2=2 B2=””
A3=3….. B3=””…
A20=20 B8=2
B9=””
B10=””…
B16=3

Thank You,

Judd

I use multiplication tables to give new users a sense of the power of mixed
absolute and relative references; with a row of numbers along the top and a
column along the side, a single formula can easily produce the table.

But sometimes I don't want to copy/paste-special-transpose to produce the
source row from the source column. I'd like a new symbol, let's say "%", to
mean "transpose-relative" as "$" means "absolute". So if my source was in
A1:A10, I could put "=$A1*$A%1" into B1, fill right and down, and be done.

The "%" would mean that as the formula's _column_ changes, the _row_ of the
reference changes, or vice versa if the % precedes the column reference.

For extra credit, make double-clicking the grow-handle of such a cell expand
to the appropriate square and fill along both axes.

I would like to know how to copy a range of cells so that:

(1) None of the cell references are changed. That is, regadless of
whether the formulas use relative or absolute references, the formulas
that are copied to the target cells are exactly like the ones in the
source cells.

(2) Copy the results of the formulas. So if the cells shows "100", but
the call actually contains "=A3/B4", I would like the target cell to
get the "100", not the formula.

(2a) I would like it to carry the formatting with it.

(3) Is there a way to "convert" a cell from a formula to a result so
that, in the example above, the "=A3/B4" gets converted to "100" and
is no longer a formula? I am looking for the equivalent to Ctrl+6 in
Word that converts a field (like a date) into regular text.

Thanks

--
Email: Usenet-20031220 at spamex.com
(11/09/04)

I am an old quattro user who has emigrated to Excel 2007, and being
frustrated by Excel Range names.
My apologies if this has been asked before, but I couldnt find.
-Basically, I have two columns of figures(Say "Month Sales" & "Year To Date
Sales"
-I need to set up a report based on these figures with various
ratios/comparisions
-I have named the 1st column cells so that I can keep track of what I am
adding/dividing etc
-Having completed Analysis of the "Month Sales", I now need to perform exact
same analysis of "Y-T-D Sales.
-In Quattro(for Dos), I could now simply copy my formulae into the next
column, and formula would look at 2nd column of data, s long as I hadnt put a
"$" in front of the range name in my formula. In Excel, the range name
appears to be "absolute" by definition, and formulae continue to refer back
to column 1

Any suggestions? The report in question is about 60 pages long, and I cant
see any other way of doing it other than retyping all the formula!? The only
reference in the Help files I can find is a note "that Range names are by
DEFAULT Absolute". How can I change this?

eg
A1 = 100 {Rangename = ABCSales}
B1 = 657 {not named}

Report:
H10 = "=ABCSales" [Heading MONTH SALES]
I 10 = " =B1" [Heading YTD SALES] after copying

I have Excel 2003 SP2
Usually if enter a formula like =B3-B2 into a cell, and copy and paste
it,
I got relative reference in the destination, e.g. cell below would
become
B4-B3
Now when I paste, every cell gets =B3-B2, therefore the same value.
I tried selecting the source cell and pressing F4 key to cycle through
absolute and relative reference - that didn't help.
I am using control-c control-v not paste special.
Fortunately I have Open Office as well to get me out of a pickle,
but I wish to know:
What has happened to Excel?

how do i reference a sheet relatively and not as an absolute? Everytime I
copy a sheet the cell always refers back to the original reference.

ie. I make a formula that takes the difference between cells in sheets a and
b. I create sheet c and expect the formula to figure the difference between
b and c. This doesn't happen.

Here is the scenario. There is an Excel Workbook with multiple
worksheets (we're talking 35+ and growing). The client wants to create
a summary sheet that lists the key information on the different
worksheets.

Of course the data on the individual worksheets is in columns and the
summary sheet is in rows.

I know how to create a 3D cell reference to different cells in a
workbook but to do it for all the individual cells is not acceptable.

Is there a way to make this formula in a cell:

=WorksheetA!B1

And have it copy down so the formula automatically populates with the
next worksheets like this?

=WorksheetB!B1
=WorksheetC!B1
=WorksheetD!B1
=WorksheetE!B1

I tried the substitute function but I would have to put in the name of
each worksheet. I guess what I am trying to ask is if there is a way to
make the worksheet references absolute.

I did find something called the SheetOffSet feature that works EXCEPT
when I copy the formula, it does NOT make the worksheet reference
relative (the 2 in the formula below) -- even when I make the cell
reference absolute.

=SHEETOFFSET(2,B2)

http://www.j-walk.com/ss/excel/tips/tip63.htm

I would be most grateful for any suggestions/ideas you can toss my way.

Hoping that someone can offer some advice on this....

I'm creating a complex excel sheets with lots of relative, mixed, and
absolute references. Once I'm done, I'd like to copy a block within the
sheet to create a new block. The trouble is that all the absolute
references within the block are pointing to reference within the old block.
I can fix them manually, but I want to copy the block several times, and
it's a lot of work to change each one

What I'd like to to do is, once things are as I want them, convert all of
the Absolute references in the completed block to relative so that when I
past a new block, the addressing will be correct. One trick I've found is
that I can convert cells formulas from Absolute to relative one by one using
the F4 key, but that's not ideal either (although, it's easier than editing
each cell). Anyone know if there is a way to convert a block of cells to
relative mode in one shot? Thanks for any advice.

I'm stumped!!

In Excel 2000:

One workbook, starting with 2 "master" sheets (used as templates for
additional sheets)

TEMPlocation
TEMPsummary (with formulas that reference TEMPlocation!)

These sheets are normally hidden.

Upon user opening for for the first time:

I have a macro that allows user to input 4 char "location" code, then
the macro copies and renames two NEW sheets (concatenating the code)
as follows:

locCode = inputbox...
Sheets("TEMPlocation").Visible = True
Sheets("TEMPlocation").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "location"

Sheets("TEMPsummary").Visible = True
Sheets("TEMPsummary").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "summary"

Upon reviewing the new location sheets, I notice the formulas in
LOC1summary still reference the original "TEMPlocation" sheet instead
of the "LOC1location" sheet.

I understand if I RENAME the sheets, Excel will update the formulas,
but I'll lose my template files for future additions.

I've attepted many different scenarios of copying, then renaming but I
can't seem to save the original sheets, and make new sheets that Excel
will update references to each other.

My closest solution has been by "hardcoding a reference" by creating a
named range (cell) that stores the text location code, then reference
that cell using the INDIRECT function to create the formula sheet
reference (as follows):

Cell A1 (value): LOC1

Formula cell B2 on same sheet: =INDIRECT(TRIM($A$1) & "location!L73")

resulting in (formula): =LOC1location!L73

This works - sort of....

The concern here is that the cell reference on the target sheet is
absolute, and if rows/columns are added/deleted this will kill the
formula.

Is there a way to make the target cell reference (which is on a
different sheet) relative?

OR

Is there a better way to copy / rename the sheets originally so that
Excel simply updates the formula references?

Thanks in advance for you consideration!

Regards,
Glenn

Say I have the following relative reference formulas in Col A:
+D1
+D2
+D3

I want exactly the same formulas in Col B:
+D1
+D2
+D3

I can change the formulas in Col A to absolute reference, copy/paste them,
and then change all formulas back to relative. Along similar lines I could
add an apostrophe in front of each formula before copying it, thereby
changing it to text, then removing it after the copy/paste.

But these methods are manual and cell by cell.

Is there a quicker way to do this, short of writing VBA code?

Art

Hello again to all masters of microsoft!

I am having problems (again), i need to copy the formula below to alot of cells..... but i want the values in bold to increase by 1 when i copy, otherwise i'm having to change each one by hand, which is a pain!
I know there must be a way to do this, i've tried using the absolute column and relative row reference on the first value in the formula but it doesn't seem to work. As for the second value......well lets just say i'm stuck!

=IF('Lisa Flexi Input'!$D247=0,"",VLOOKUP(39569,'Lisa Flexi Input'!$A$126:$D$506,4,'Lisa Flexi Input'!$D$4:$D$506))

Any ideas, please make my life easier!!!

G