Free Microsoft Excel 2013 Quick Reference

Excel: hold a reference to a single cell when copying formulas?

I used to use '$' signs to keep a refererence to a single cell in a formula
but that doesn't seem to work in the new version. How should I do that?


Post your answer or comment

comments powered by Disqus

Related Results

  1. Maintain reference to specific cell when filling formula down column
  2. In VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet?
  3. In VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet?
  4. Excel: hold a reference to a single cell when copying formulas?
  5. RE: Excel: hold a reference to a single cell when copying formulas?
  6. Refer to selected cell in custom function
  7. Storing a reference to a cell?
  8. Excel adds a "1" to my filename when it opens
  9. Reference within a reference???
  10. Reference to another cell on sheet two
  11. Macro to unmerge cells and copy
  12. How do you copy an exact value to another cell? (not copying formulas)
  13. XL2000: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  14. Type mismatch error 13: Transpose dynamic name range when range refers to single cell
  15. XL97: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  16. XL98: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  17. XL97: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  18. XL98: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  19. XL2000: COUNTIF Returns Incorrect Value When It Refers to Blank Cells
  20. Lock or freeze data in cells with have a reference to another worksheet
  21. Vlookup using a reference to a named range?(Excel 2000)
  22. Is there a way to animate cell(s) boards in Excel?
  23. Excel should let me use formulas that refer to other cells w/ form
  24. Excel should let me use formulas that refer to other cells w/ form
I'm trying to make a spreadsheet solving the relative error of experimental outcomes.

I have a single cell containing the exact value and I want excel to always point to this cell when I drag down the cell containing the relative error formula (REF).
***REF = abs(experimental.outcome.cell - exact.value.cell)/exact.value.cell***

Currently, when I drag the cell containing the REF the function points downward to each successive cell.

There are three variables in my spreadsheet an, bn, and Pn. As I progress down the table I'd like the n to increase by 1 with each variable i.e. (a1,a2,a3...an). How can I do that without typing it in each time?

in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to
the newly created copy of this sheet?

in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
the newly created copy of this sheet?

Did you remember to use the $ for both Column and Row?
ie $A$5 vs. $A5
HTH,
--
Gary Brown
I used to use '$' signs to keep a refererence to a single cell in a formula
> but that doesn't seem to work in the new version. How should I do that?
>
>

Did you remember to use the $ for both Column and Row?
ie $A$5 vs. $A5
HTH,
--
Gary Brown
gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com

"Jim in Texas" wrote:

> I used to use '$' signs to keep a refererence to a single cell in a formula
> but that doesn't seem to work in the new version. How should I do that?
>
>

When I use an Excel function I type =FunctionName( I then move the active
cell to the one on which I want to perform the calculation and then type )
and hit enter.

I want to create a custom function but can't figure out how to include a
reference to the cell on which the formula is to be performed. I've recorded
a macro using a built in Excel function thus:

Cell D2 is the active cell and I enter the formula =Weeknum(a2) which Excel
records as

ActiveCell.FormulaR1C1 = "=WEEKNUM(RC[-3])"

But this will only work if the cell to be referenced is 3 colums to the left
of the cell that holds the formula. I want to use the function in any cell
and perform it's calculation on any other cell i.e. I wnat to use my function
like any other Excel function.

How can I do this please?

When a user enters a value in a cell, my VBA code changes the cell to a text value.
for example, if the user enters '1', the VBA code changes the cell to 'Front Door', the problem I have is I need to store a reference to that cell that will have the value of '1'

So I need to now that 1 is Front Door and that Front Door is 1,

Thanks,
Bob

I'm getting one of 2 problems.

Excel adds a "1" to my file name upon opening, turning "Test.xls" to "Test1.xls".

I can fix this by taking off the "%1" at the end of the line in Appcliation in the Advanced File Type settings, but then I get error messages when I open documents...

It seems to choke on the file path at every space... so I get an error that it can't find:

"Documents.xls"
"and.xls"
"Settings.xls"

and on and on until I have to clear 10 messages, then it loads the file. Any clues?

Is it possible to use a reference to reference a reference? ...hopefully your not as confused as me lol.

Basically I want to know if you can include a reference to another cell when you are giving a cell a command to look elsewhere. This is sooo hard to explain......

say I want a sheet called 'Result' in cell C4 to read =Data!A1 but sometimes to read =Data!A2, =Data!A3 etc...
I want to have the cell references (A1, A2 etc...) somewhere else on the sheet

So I would like the formula to read =Data!.... and then look for the cell reference name.

The reason being i might have 100 cells that reference column x row x and then sometimes they might need to change by 1 or 2 columns/rows so rather than changing all individual formulas, I could just change 1 cell and the rest would read the reference change.

Knowledge of how to do it or a workaround would be much appreciated.

Thanks for all your help as always guys.

Ok I have in cell F5 display a reference to another cell on sheet two. I want cell C5 on sheet 1 to display the contents of the cell reference in F5 (from sheet 2)

For example, on sheet 1 F5 shows the reference A4. I then want cell C5 on sheet one to show me the contents of A4 on sheet two.

What formula would I write? I cant figure it out. Thanks,

Afternoon,

I am currently trying to establish a way to unmerge cells and copy the value into a different column. I appreciate this isn't very descriptive so I have attached an example of what I receive on a weekly basis, and included a more detailed explanation below.

Attached is the format that I will receive the report in;

A1 will show the title 'Server Space Disk Report' and the date of the report (in this instance '25/6/2012'). This can and will be removed from the report.Each server name (in the attached - 'Server 1' , 'Server 2' and 'Server 2') is in a merged cell.The servers Drive name, total capacity, used capacity and Free space is then shown below the server name with the titles of the column.What I would like to achieve, with a macro (or otherwise), is to have the Server Name unmerged and then inputted into a new column and put next to the relevant Disk drives. For example, in the attachment, Cell A2 will be removed and then a new column will be added into 'A' where 'Server 1' will be inputted into A4 and A5.

Hope that makes sense?

Thanks,
Dr4ke

What command do I enter so that a value in one cell is copied EXACTLY to another cell? For example, I have today's date in cell A4 and I want the exact date to be automatically copied to cell E5.

I can't seem to figure out the correct function to do this. I looked for help but there are just ways to transfer formulas over to other cells.

XL2000: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

The COUNTIF function references a criteria range with blank cells at the right or bottom ... workaround is applied, you must cause Excel to completely recalculate before the functions ...

I have a code that will transpose a single column range (which is a dynamic name range) to a 1D array. I get a type mismatch error 13 if the dynamic name range refers to a single cell range. Is this a limitation of the array or transpose function?

Many thanks
Asha

N.B. The code is still WIP - I have to yet incorporate error handling.

Option Base 1
Option Explicit

'This macro will create a third list by concatenating two lists
'---------------------------------------------------------------------------------------------
'Adapted: http://www.eggheadcafe.com/software/aspnet/33754898/how-to-combine-arrays.aspx (Joe)
'         http://www.ozgrid.com/forum/showthread.php?t=92426 (Post # 4)
'---------------------------------------------------------------------------------------------

Public Sub ConcatenateArray()
    Dim vArray1()               'List of companies in the data entry form
    Dim vArray2()               'List of companies in the database
    Dim vArray3()               'Concatenated list
    Dim i As Integer
    Dim iSizeA1 As Integer      'Number of elements in vArray1
    Dim iSizeA2 As Integer      'Number of elements in vArray2

    'Transpose the lists (single column range) to a one dimensional array
    
    vArray1 = WorksheetFunction.Transpose(Names("Test").RefersToRange)
        'Check if the list is blank OR
        'is a single cell range in the data entry form
        
    
    vArray2 = WorksheetFunction.Transpose(Names("DB").RefersToRange)
        
   'Determine the number of elements in vArray1 & vArray2
    iSizeA1 = UBound(vArray1)
    iSizeA2 = UBound(vArray2)
    
    'Set vArray3 = vArray1 & then add elements from vArray2 to this list
    vArray3 = vArray1
    ReDim Preserve vArray3(1 To iSizeA1 + iSizeA2)
    For i = 1 To iSizeA2
        vArray3(iSizeA1 + i) = vArray2(i)
    Next i
   
End Sub


XL97: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

The COUNTIF function may return an incorrect, smaller result when counting blank cells. ... workaround is applied, you must cause Excel to completely recalculate before the functions ...

XL98: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

The COUNTIF function may return an incorrect, smaller result when counting blank cells. ... workaround is applied, you must cause Excel to completely recalculate before the functions ...

XL97: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

... function may return an incorrect, smaller result when counting blank ... this problem, and the workaround is applied, you must cause Excel to completely recalculate before the functions ...

XL98: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

... function may return an incorrect, smaller result when counting blank ... this problem, and the workaround is applied, you must cause Excel to completely recalculate before the functions ...

XL2000: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

... function may return an incorrect, smaller result when counting blank ... this problem and the workaround is applied, you must cause Excel to completely recalculate before the functions ...

Fact: I have data in worksheet 1 (Sheet 1) which has a reference to worksheet 2 (Sheet 2) Example: Sheet 1 A1= =Sheet2!A1.
Sometimes I only want to distribute sheet 1 to some persons instead of the complete excel file because sheet 2 represent private date.

What I want:
I just want to freeze of lock the data in sheet 1; copy sheet 1 to another excel file by copy and insert copied cells and sent this out.

Is there a solution for this?
Kind regards
Ellerd Beijert

When inputting the table_array information in a Vlookup formula you can
either input a range of cells or named range.

My named range changes monthly so I tried to use a cell reference in the
Vlookup formula to capture the new named range but the formula returns N/A#
error?

i.e. cell A1 "Mth_1"

formula: =Vlookup("Sales",A1,2,false)

The formula works is if the named range "Mth_1" is included as part of the
formula instead of reference to a cell that contains the name of the range to
lookup.

Any suggestions? I am trying to make the update process quicker than
performing a fine and replace on multiple tabs.

Thanks

Is there a way to animate cell(s) boards in Excel? I think I saw a
spreadsheet in a meeting (overhead projector on a laptop) that had an
animated border in an area of cells of intrest. Please note that this was
NOT a copy, cut, etc command or operation. It was a stand alone feature that
was there when the file opened. Is this possible. Could it be possible that
this was another spreadsheet program?

Currently, formulas in MS Excel only let you call on cells that have constant
values. This has made it extremely difficult for me to manipulate
spreadsheets effectively in my company. I'm not sure if it is possible to
perform these tasks in Visual Basic Editor, but I strongly suggest that a
future version of Excel allow formulas to refer to other cells with formulas
and funtions (whose values could change). It doesn't seem too hard to
accomplish because whenever a formula refers to other cells with formulas,
following the line of cell references always ends with constants. In other
words, if you were to combine all formulas of this nature into one huge
formula, it would in fact refer only to constants.

----------------
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...et.f unctions

Currently, formulas in MS Excel only let you call on cells that have constant
values. This has made it extremely difficult for me to manipulate
spreadsheets effectively in my company. I'm not sure if it is possible to
perform these tasks in Visual Basic Editor, but I strongly suggest that a
future version of Excel allow formulas to refer to other cells with formulas
and funtions (whose values could change). It doesn't seem too hard to
accomplish because whenever a formula refers to other cells with formulas,
following the line of cell references always ends with constants. In other
words, if you were to combine all formulas of this nature into one huge
formula, it would in fact refer only to constants.

----------------
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...heet.functions


No luck finding an answer? You could always try Google.