Free Microsoft Excel 2013 Quick Reference

Worksheet cell reference Results

I want to copy a cell reference from an irregularly spaced worksheet refering to a regularly incrementing worksheet.
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

Hello,

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?

Please read forum rules in Excel Help Forum. All code must be wrapped

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 hope this makes sense!!

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

Hi,

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.

I want to set one worksheet cell range reference to another worksheet using aspose.cells.For example i have two worksheet(wrk1 and wr2). I have some data on wrk1 and wrk2 is balnk.Now on wrk2 i want cells refrence of wrk1(like want cell reference of wrk1 sheet A1:A10 on wrk2 with same style as on wrk1) .

Please suggest.

Hi,

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'

Hi,

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

If you have a worksheet that contains straightforward cell references (where the contents of B1=A1), is there a way to have any cell formats made to cell A1 carry through to cell B1?

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?

On the attached workbook, the worksheet "Form" references cells in the worksheet "Spreadsheet". I need a macro that will perform advancing the cell reference one row down. For example, on "Form", cell B4 shows '=Spreadsheet!A2' and when the macro is executed, I need it to read '=Spreadsheet!A3'. Hope that is enough detail, and again, the file is attached. Thanks in advance.

Corby

Im really in need of some good advice. Ive used many hours making a test cell for a new worksheet, but then when i closed down the workbooks my test cell was refering to i got the #REF! error although the whole path to the workbook was there. I isolated the problem to a indirect formula im using because i want the reference file to be a variable.

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

Hey,

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:



I have a worksheet that contains the days of the week (Mon, Tues, etc...) in B4:B10. In C4:G10 these cells = a cell on other sheets.

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

Hi there - VBA newbie here! - i have a workbook with 5 worksheets.

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

Hi,

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?

I have a workbook with 10 worksheets all which I use a fomrula that references a page called data. How can I change a cell reference linked to another page on several same type formulas without having to go to each formula and change that specific reference to a cell? For example, the formula now reads =IF(+data!B16=3,C16,"") and I now want all the formulas to reference the column a in place of b instead? =IF(+data!A6=3,C16,"") do I have to do this individually ? Any help greatly appreciated...
JE

Hi, hopefully someone can help me with the following problem.
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

I have a worksheet that totals data form another sheet. The end user has to copy and paste new rows into this sheet on occasion. I want to do some conditional formatting to be sure the copied row are referencing the correct cells on the "data" sheet.The referenced cells are on the same row. Lets say the Cell references are Sheet1!Z11 and Sheet1!Z25. What I want to achieve is have the Second Cell on my total sheet change color if the cell reference in the second cell is not 14 rows to the right of the first cell reference.

Dunno if that made sense the title.

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.

Hi Please can you help a 2007 excel novice to solve a problem and save time?

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