Free Microsoft Excel 2013 Quick Reference

- Copy without relative formulas changing
- Help with Coding a Formula Please
- Absolute values and how to copy multiple desending cells
- Reference cells from different worksheets with worksheet name in a cell
- Countif Error With Absolute Range Reference
- Returning address with a relative reference
- Paste in relative mode
- Excel assumes I want absolute reference!
- Something other than Absolute, Relative or Mixed references
- Formula for copying data for every 8th cell
- Transpose-relative cell references would be useful.
- Copying options: contents, results, formulas, etc.
- COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES
- Copy-paste started misbehaving (cell reference wrong)
- Relative reference
- Relative Sheet Reference (Summary Sheet)
- Help with converting a block of cells with Absolute and mixed references to relative references
- Relative sheet referencing - what's the secret?
- Copying a formula "literally"
- Can't get relative cell reference to change with this formula!

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.

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

Thanks, Wiz.

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 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?

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?

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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

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.

(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)

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

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?

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.

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.

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.

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

+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

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