Free Microsoft Excel 2013 Quick Reference

- Regular cell reference increment from irregular worksheet
- Cell reference from other worksheet in footer
- Unknown cell referances in a macro
- Using a cell reference to select a worksheet within a macro
- Cell reference using macro
- Setting one worksheet cell range reference to another worksheet
- Lock a cell reference to a different worksheet from where the cell is located
- Formulas changing cell references for not apparent reason
- Formatting and Cell References
- Macro/VBA to advance cell reference one row
- Cell reference within a indirect..
- VBA: add SUMIFS formula with cell reference as criteria
- Increment Cell Reference
- VBA to rename worksheet based on cell reference on another worksheet
- Trim down on cell references
- Changing a cell referent in a formula several at a time...
- Changing cell references automatically
- Conditional Formatting based on Cell References not Values
- Cell reference to previous sheet.
- Copying relative cell references to multiple worksheets

Ie. The copied cell will be a random number of rows below the original, but will refer to a cell in another worksheet that always increments by 8.

I've tried OFFSET and INDIRECT but I can't get around the 'random' auto-increment.

(Does anyone understand that?)

Cheers

Mark

I'm trying to get a cell reference into my footer, specifically 'Pg 1!A5'. I want this to be in the middle column of my footers for each page, but I don't know how to get it in there and I heard you have to use macros. Can someone assist?

Ok, this is what I am trying to do.

Part 1.) Get sum of an unknown cell range

example:

the worksheet always has a different amount of rows. I want to add all the rows in column E at the bottom of the last cell.

Part 2.) In a totaly different cell I am trying to calculate a sumif with unknown range values. Here is the code I have tryed, but all I keep getting instead of the range values are trues.

Range("Al14").Select ActiveCell.FormulaR1C1 = "=sumif(" & _ ActiveCell.Offset(-5, -18).Activate & _ Range(Selection, Selection.End(xlUp)).Select & _ "," & _ ActiveCell.Offset(0, -2).Select & _ "," & _ ActiveCell.Offset(-5, 0).Activate & _ Range(Selection, Selection.End(xlUp)).Select & _ ")"

How do I write this so that it actually contains the cell referances instead of boolean?

I have a spreadsheet for data collection. This has 4 teams (1,2,3 and 4)

which currently gets merged onto 1 huge spreadsheet. I want to be able to

run a macro which splits the data onto 4 separate sheets.

Team 1

Team 2

Team 3

Team 4

I have tried using Sheets(AC8).Select - did not work. AC8 being the cell

referred to from the drop down box where the team number is selected.

Any ideas?

Regards

Richard

I am using Excel 2002 and I want to do a macro that will cell reference

from one worksheet to another. For example I want Sheet 1 cell B10:C17 to

be able to reference from Sheet 2 F10:G17. I don't want to do a copy and

paste, because the user may change the data in Sheet 2. Any tips or

suggestion will be appreciated. Thank you in advance.

Please suggest.

New to this so here goes. An admitted newbie but prepared to 'do the yards'.

Have a workbook with a number of worksheets.

One of the worksheets is titled 'TEMPLATE' - this is a calculation sheet that is used to determine pricing - on a quarterly basis. A number of cells in this worksheet reference cells in another worksheet titled 'CURRENT RATE' in the same workbook.

Every quarter, we are provided a new 'CURRENT RATE' worksheet.

At the moment, we have to manually rename the existing worksheet 'CURRENT RATE' to a title referencing the quarter that it was applied to ie Rates DEC2011-FEB2012. Then insert the new version worksheet , rename it 'CURRENT RATE'.

The problem is that the cells in the 'TEMPLATE' worksheet that reference the original 'CURRENT RATE' worksheet changes to the new name of the original worksheet. This results in me having to track through the 'TEMPLATE' worksheet to re-reference the affected cells back to 'CURRENT RATE' once I have inserted the new quarterly worksheet (and renamed it to 'CURRENT RATE').

I've looked at absolute values but am getting an alert. I have tried a number of methods ie =ABS('CURRENT RATES'!$D$3), =$'CURRENT RATES'!$D$3 & ='CURRENT RATES'!$D$3

Alert states 'a user has restricted values that can be entered in this cell'

I have a spreadsheet with the following formula used in one column.

=((C10*3)+(E10*2)+(J10*5)+(F10*3)+(G10*3)+(I10*3))/('Projected Time'!AA6/100)

'Projected Time' is another worksheet in the same workbook. For some reason, some of the cell references change for no apparent reason. For example, today I opened the spreadsheet and the cell L15 read:

=((C15*3)+(E15*2)+(J15*5)+(F15*3)+(G15*3)+(I15*3))/('Projected Time'!AA11/100)

After I had finished entering data into the workbook, the cell had changed to:

=((C15*3)+(E15*2)+(J15*5)+(F15*3)+(G16*3)+(I15*3))/('Projected Time'!AA11/100)

This then replicated from cell L15 down to the end of the formulas in the column (L32). Cells L1 to L14 were still correct (had not changed).

I had not deleted any cells on any of the worksheets of the workbook so I have no idea why it would have changed.

I am using Excel 2003 (version 11.5612.5606).

Has anyone encountered this problem before?

Does anyone have any idea on how I can prevent this from happening?

Thanks

Psycho_chook

I have 3 identical lists, just sorted based on different criteria. The lists are over 300 items long. When one of the items becomes unavailable, I want to use the "Strike-through" font on one list and have it automatically "Strike-through" that same item on the other two lists.

Any ideas?

Corby

How do i solve the problem that excel isnt able to use a cell reference in a workbook that isnt open if that reference is within a "=indirect()" code..?

got a problem with this piece of code. I'm trying to insert SUMIFS formula to worksheet cell and I'm missing one quote marks of the last criteria. The last part of the code (underlined & bold) is not working. If I have the code like this:

Then when checking the formula from worksheet this last criteria (underlined & bold) brings the correct text string to worksheet formula but it doesn't have quote marks around the text string (like the hardcoded Text1 has). If I have the formula like this:Then the formula won't have the correct text string as criteria but this cell reference is showing up in the worksheet formula. So how this cell reference criteria should be handled...?

edit. Just after posting got it, needs to be like this:

Each week I have to go in and manually change each cell reference to look at the next row down on the sheet they pull from. Is there a way that I can set it so that when I push a command button, it will just increment the cell reference row by one?

Example: C4 is ='3rd'!B23

I would like to push a button so that C4 is ='3rd'!B24

Worksheet Names are:

- Sheet5

- Sheet4

- Sheet3

- Sheet2

- Summary

I am looking for a VBA that will do the following:

- rename Sheet5 with cell reference (A5) on Summary sheet

- rename Sheet4 with cell reference (A4) on Summary sheet

- rename Sheet3 with cell reference (A3) on Summary sheet

- rename Sheet2 with cell reference (A2) on Summary sheet

Would appreciate any assistance you can provide!!

I have a formula that is quite long, and is made a lot longer because it

refers to cells in another workbook, so that the names of the workbook and

worksheet are repeated for each cell reference to the workbook. Is there any

way to tackle this issue?

JE

I have a workbook which has 5 worksheets of salesmans figures for the

current month. I compare this data with year to date figures, last years

figures for the same month, and last years year to date figures. This is done

on each worksheet, each sheet representing a different salesman. All

comparison figures are on a different workbook, and a formula is used to

insert comparison figures into the current months sheet, eg if current month

is Feb 05, and I need to refer to Feb 04:

=X:KMFMonthly ReportYE 2005[Monthly Report KMF Feb 05.xls]Customers'!B8

At the moment, I have to change the formula which refers to the the other

workbooks manually on each worksheet, ie if current month is Mar 05, I need

to change cell references for previous years figures to Mar 04. This is

because I open last months workbook, and save it as current month. However,

because of this the cell references still relate to the previous months

comparison figures. Is there any way that I can just type into a cell which

month Excel should refer to for the comparison figures? Excel can then change

the formulas automatically by referring to this cell to see which months

figures are being compared against.

All help most appreciated

Thanks

Bigweed

We have a sizeable Excel workbook that contains many worksheets for various things, everything works smoothly except for one minor niggle.

I am looking for a formula that references to a cell on a previous worksheet, the worksheet could be named anything (eg A1, B, 2, 3.4, etc). Then to add 1, ideally the first worksheet inserted must equal, say, 0 BUT can reference the sheet previous as this is a constant; so something along the lines of...

Cell A1 =magic formula

Cell A2 =A1+1

This is to get a vlookup to work consistently.

OR another option is, for the worksheet to reference an already populated worksheet and read down a table by adding one to reference the cells. Eg first sheet reads ='schedule'!A13, the next sheet added would read A14 then A15 etc...

OR a worksheet (like page numbers) count into a cell onto each worksheet inserted, eg on worksheet 6 in cell A1 the number 6 would be inserted then worksheet 7 would have 7 inserted into A1 etc..

I hope this makes some sense, we have quite a complex workbook and this is the final hurdle to overcome.

I have a workbook containing 40 worksheets. In Sheet1 I have a cell, say C1 containing the following formula:

=SOW!$D$8+(SOW!A20*7)-7.

The formula represents a date, 23 August 2010. I want to copy the cell containing the formula to the other 39 worksheets so the relative cell reference, A20, incrementally changes to A21, ..... A22 etc to change the date respectively to 30 August 2010, 06 September 2010 ................. etc. I can/could copy the formula to the 39 worksheets and then change the relative cell reference A20 manually but this will take time. Is there a method of copying the formula to multiple worksheets, which automatically increments the cell reference A20?

Mant thanks