Free Microsoft Excel 2013 Quick Reference

Anchoring Cell References

Situation: I have one column in worksheet (eg worksheet 1) linked to another column in worksheet (worksheet 2). The whole column is linked, like below:

=Sheet2!A1
=Sheet2!A2
=Sheet2!A3
=Sheet2!A4
.........
.........

How do I keep these links from moving when a row is inserted in worksheet 2? So for example, if a row is inserted (in sheet 2) between rows 1 and 2 now, the cell references in sheet 1 would change to

=Sheet2!A1
=Sheet2!A3
=Sheet2!A4
......

How do I keep the references from moving - so they remain as

=Sheet2!A1
=Sheet2!A2
=Sheet2!A3
=Sheet2!A4
.........
.........


To greatly simplify this, cell A1 says "=B1" and cell A2 says "=A1"
If I move cell B1 to cell C1, cell A1 now says "=C1" and of course, cell
A2=cell A1
Next problem... if i move data into cell B1, cell A1 gives me a #REF!
How can I anchor the reference in cell A1 to always say "=B1"?
How can I move data into a referenced cell without getting a #REF!?

To greatly simplify this, cell A1 says "=B1" and cell A2 says "=A1"
If I move cell B1 to cell C1, cell A1 now says "=C1" and of course, cell
A2=cell A1
Next problem... if i move data into cell B1, cell A1 gives me a #REF!
How can I anchor the reference in cell A1 to always say "=B1"?
How can I move data into a referenced cell without getting a #REF!?

I have a simple spreadsheet which records daily exchange rates on various countries. Days down the left Countries across the top. Each day or so i will add daily exchange rates to the bottom of the spreadsheet by copying a row or rows from the internet site providing the data and inserting those copied rows to the bottom of my spreadsheet.

What I would like to be able to make happen automatically is calculate the average exchange rate for the last 7, 14, 28 and 90 days for a column of data, then the maximum rate for the last 7, 14, 28 and 90 days and similarly the minimum rate.

To date I have been able to do this using AVG, MIN and MAX but I would like to be able to anchor the first (bottom) cell in each of the 7, 14, 28 and 90 day ranges and have a formula to work out when to stop the calculation at 7 days ago, 14 days ago, 28 days ago and 90 days ago.

I guess there must be a way of doing this, any help or direction would be appreciated.

Thanks Greg H

i have limited knowledge of formulas and I have looked all over and haven't found a way to do this.

What I am trying to do is simply somehow use the autofill feature to fill a column with references to a specific cell in consecutive sheets.

For example, if I wanted sheet 1 to list in the first column from top to bottom, cell D1 in sheet 2 [named "Week 01"], cell D1 in sheet 3 [Named "Week 02"] , cell D1 in sheet 4 [Named Week 03, etc...

I figured if I made the cell reference absolute bysetting cell A1 in sheet 1 to
"=Sheet2!$D$1", I could then use the autofill drag down to set A2 to "=Sheet3!$D$1",
A3 to "=Sheet4!$D$1", A4 to "=Sheet5!$D$1", etc...

But I couldn't get that to work as the individual sheets are named as Week 01 - Week 52 and when i tried to reference the week no's the formula didnt work. If there is any way to get the autofill to work it would make things extremely faster, as there are hundreds of sheets so manually entering the references takes little while.

Thanks in advance.

This is my first time and I am new to VBA so please bear with me.

I have a worksheet with 1500 records. I already have a code which asks me to input a search string, goes through each row of the workshet and if Column C matches the search string, copies the entire row to a second sheet on the row next to the last row. The macro also counts the number of instances found so at the end of the search, we know that there were for example 28 records matching the string i.e. 28 new rows in the second sheet. The new set of records (28 rows in this case) are separated from the previous records by a gap of two rows.

I have also written code to name the new 28 cells in F column to lets say MyRange. Then the macro goes to the 29th cell in F row and needs to put in a formula to sum the above 28 rows. I can't use xlUp because there may be blank cells in between.

I had originally used
ActiveCell.FormulaR1C1 = "=sum(MyRange)"
 but at the end of the
macro I need to delete name reference so that the code can run next time for a different search string without any
problem.

My problem is how do I convert MyRange to the actual cell reference from what we know which is:
(i) The cells to be summed are always in F column.
(ii) The Active Cell is already one row below the range to be summed.
(iii) The number of rows is stored in a variable called Counter

Basically what I need is Sum(R-[Counter]C[0]:R-[1]C[0])

I know this must be very simple for you veterans but I am stumped. Thank you for your help.

Hi,

I just noticed that in one workbook that I use the cells references in the formula are changed whenever I use a filter .

E.G. : =VLOOKUP(A4,Sheet1!$B$7:$K$996,1,FALSE) gets the row reference changed to =VLOOKUP(A25,Sheet1!$B$7:$K$996,1,FALSE) when I use a filter.

Any idea what is causing this or what should I do to prevent this?

I would like to pick up just a piece of a cell reference from another cell.

My workbook has something like 26 tabs. The first 24 tabs are "2011-1 Checking", "2011-1 Visa", "2011-2 Checking", "2011-2 Visa", etc. and contain activity downloads from my checking account and my visa account.

I have a summary worksheet that looks basically like this:
(row number-not a real col.) January February March 11 *DOMINION VA* 12 *WASH GAS* 13 14 15 16

On this summary worksheet, I had worked out the following formula to go through each worksheet one at a time to collect utilities data for each month. It should basically be saying "In the January 2011 checking spreadsheet, sum the amounts if the description contains the string DOMINION VA (which is in A11) and the category column says 5221-UTILITES."

=SUMIFS('2011-01 Checking'!$D$2:$D$86, '2011-01 Checking'!C2:C86,'Utility Summary'!$A11, '2011-01 Checking'!$E$2:$E$86, "5221-UTILITIES")

This formula works, but I would have to manually change 2011-01 to 2011-02 and then 2011-03, etc. all the way across for the next months. I thought it would be better to have those year-month numbers just one place and reference them somehow. In other words, underneath the JANUARY column header I would have "2011-01" and my formula would be some variation of "=SUMIFS(B11&" CHECKING"!$D$2:$D$86...etc" So later I would change the years in one place and all the formulas would change.

I've tried a variety of CONCATENATE or INDIRECT combinations, but just get "There is an error in your formula." Is there a way to do this?

Thanks for your help.

I'm looking for something like the code below, except the final result contains only numbers and is devoid of cell references:


	VB:
	
 
    CellFormula = Rng.Formula 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Ideally, if a referenced cell, itself, contains cell references, it would pull those raw numbers along with their algebraic relationship--and so on, and so on, until all cell references and their mathematical relationships are encompassed into a single formula with no cell references.

Does anyone have the solution? Your help is greatly appreciated.

I'm trying to get VBA code to print labels in worksheet "Batch Labels" that reference data from another worksheet "DECANTING" in the same workbook. I want to print the label (x) amount of times depending on the qty needed in specified cell (first time from cell: DECANTING!AB4). After those labels are printed, I want to change the cell references to the next row of data, and repeat the process until all labels are printed.

label.jpg

The highlighted cells in yellow from worksheet "Batch Labels" are the cells that reference data from worksheet "Decanting".

DATA.jpg

Above is the data from worksheet "Decanting".

Please help!

Is there any way to lock cell references down to their EXACT location. Absolute references are useless when rows are added and deleted. They may as well be relative references for all intents and purposes. Creating a named range doesn't help at all. Those ranges "adapt" as if the dollar signs for absolute references weren't even there. Any silver bullets for this? Thanks in advance for your help.

DB

Hello, I'm trying to run a macro on multiple selected sheets. The sheet names are in a cell reference on a particular sheet. Based on reading some other threads here, I came up with the following but it needs all the sheet names to be manually typed into the macro. I'm wondering if it can be modified to pick up the sheet names from a cell reference instead of typing them in, as more sheets might be added to the file later.


	VB:
	
 Macrotest() 
     
     
    For Each ws In Worksheets 
         
        Select Case ws.Name 
        Case "Sheet4", "Sheet1", "Sheet5" 
            ws.Activate 
            Range("A1").Select 
            Selection.ClearContents 
             
        Case Else 
             
        End Select 
    Next ws 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Assuming the sheet names are in Sheet3, A4:A8, is there a way to have the macro pick up the sheet names from there? Thanks.

Hi,

Newbie question here, Hope you guys can help (as soon as possible thanks)

Seems a simple question but not working.

I have an excel worksheet containing different shapes as buttons.

These buttons are used to hyperlink to certain cell references within the worksheet.

I want to have the referenced cell showing at the TOP of the screen, so the relevant info is shown on the screen below it

I've created the hyperlink with the required reference cell but found that, if the cell is already visible on screen it doesn't do anything or sometimes shows it at the bottom of the screen?

After some googling I found code for a macro as below:

	VB:
	
 Scroller() 
    Dim RowNum As Long 
    RowNum = ActiveCell.Row 
    Range("A1").Activate 
    ActiveWindow.ScrollRow = RowNum 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then assigned the macro to the button, (which still has the original Cell reference hyperlink), but this still doesn't work?

If I click on any cell Eg A80, then run the macro manually, it homes to A1 then scrolls to A80 and shows as top cell perfectly.

But when I assign the macro to a 'button' and run it, it doesnt work? It's as if the macro is running first, and then the hyperlink, whereas I think I need the macro to run after the hyperlink

The post where I got the abve code did say "try adding the following procedure and call it at the end of your hyperlink code:", so this may be the issue

How can I do this? Your help is much appreciated.

I want to know how to copy the formula without changing cell references

Hi,
I just found a thread here related to Match and return cell reference, which works perfectly for Match with in a column as match function gives values in numbers. But as I am searching within a row and need column names (A,B,C, D etc), is there anyway that match function could return Alphabetic answers? Or I need to make a helping column which gives 1=A, 2=B, 3=C and so on. (but I dont want to create a new column if there is any other way possible to do the same directly)
Thank you
Best Regards,

Hi all,

I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations.

So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?

Thanks all,

B

Hi all,

I have a simple question, i tried a search but I didn't know how to phrase the question properly.

I was hoping someone knew the formula which will return the cell reference of the cell that contains a specified value

eg

if column A has values 1-10 in order, b1 has the value 5, then what formula can i input in c1 that will give me the cell reference in the range in column A which contains the value in column B? (the result of the formula in c1 would be A5 in this example)

Appreciate the help, cheers!

-shaun

New to the forum, so apologies for mistakes.
I need a forumla that will always refer to the cell directly above without using a cell reference. VBA would work but I would rather an Excel formula if possible.
Thank you

Ok im trying to see if there is any way that i can get a sum for a colum with out actually selecting a specific cell. this way no matter how many cells are in the sheet it goes to the last cell and sum's that column. Here is the code I have been able to get rid of most of the specific cell references that are at the bottem of the page excetp this one. Here is the code i have so far.

	VB:
	
 Macro1() 
     '
     ' Macro1 Macro
     ' Macro recorded 9/18/2006 by Administrator
     '
    Rows("1:1").Select 
    Selection.Insert Shift:=xlDown 
    Range("A1").Select 
    Rows("1:1").Select 
    With Selection 
        .HorizontalAlignment = xlGeneral 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .ShrinkToFit = False 
        .MergeCells = False 
    End With 
    Range("A1").Select 
    ActiveCell.FormulaR1C1 = "CO" 
    With ActiveCell.Characters(Start:=1, Length:=2).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "ACCT#" 
    With ActiveCell.Characters(Start:=1, Length:=5).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("C1").Select 
    ActiveCell.FormulaR1C1 = "CUSTOMER" 
    With ActiveCell.Characters(Start:=1, Length:=8).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("D1").Select 
    ActiveCell.FormulaR1C1 = "INV#" 
    With ActiveCell.Characters(Start:=1, Length:=4).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("E1").Select 
    ActiveCell.FormulaR1C1 = "INV DATE" 
    With ActiveCell.Characters(Start:=1, Length:=8).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("F1").Select 
    ActiveCell.FormulaR1C1 = "CURRENT" 
    With ActiveCell.Characters(Start:=1, Length:=7).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("G1").Select 
    ActiveCell.FormulaR1C1 = "OVER 30 DAYS" 
    With ActiveCell.Characters(Start:=1, Length:=12).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("H1").Select 
    ActiveCell.FormulaR1C1 = "OVER 60 DAYS" 
    With ActiveCell.Characters(Start:=1, Length:=12).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("I1").Select 
    ActiveCell.FormulaR1C1 = "OVER 90 DAYS" 
    With ActiveCell.Characters(Start:=1, Length:=12).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("J1").Select 
    ActiveCell.FormulaR1C1 = "OVER 120 DAYS" 
    With ActiveCell.Characters(Start:=1, Length:=13).Font 
        .Name = "Arial" 
        .FontStyle = "Regular" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Range("K1").Select 
    Range("A1:K1").Select 
    Range("K1").Activate 
    Selection.Font.Bold = True 
    Cells.Select 
    Cells.EntireColumn.AutoFit 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .ShrinkToFit = False 
        .MergeCells = False 
    End With 
    Range("A2").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    With Selection 
        .HorizontalAlignment = xlLeft 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .MergeCells = False 
    End With 
    Range("F:J").Select 
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" 
    Range("A1").Select 
    Cells.Select 
    Selection.Sort Key1:=Range("J2"), Order1:=xlDescending, Key2:=Range("I2") _ 
    , Order2:=xlDescending, Key3:=Range("H2"), Order3:=xlDescending, Header _ 
    :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    Range("A1").Select 
    Columns("G:G").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("I:I").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("K:K").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("M:M").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("O:O").Select 
    Selection.Insert Shift:=xlToRight 
    Range("G2").Select 
    ActiveCell.FormulaR1C1 = _ 
    "=IF(RIGHT(RC[-1],1)""-"",RC[-1],LEFT(RC[-1],LEN(RC[-1])-1)*-1)" 
    Range("G2").Select 
    Selection.Copy 
    Range("I2").Select 
    ActiveSheet.Paste 
    Range("K2").Select 
    ActiveSheet.Paste 
    Range("M2").Select 
    ActiveSheet.Paste 
    Range("O2").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Selection.End(xlToLeft).Select 
    Selection.End(xlDown).Select 
    Selection.End(xlToRight).Select 
    ActiveCell.Offset(0, 1).Select 
    ActiveCell.FormulaR1C1 = "1" 
    ActiveCell.Offset(0, 2).Select 
    ActiveCell.FormulaR1C1 = "1" 
    ActiveCell.Offset(0, 2).Select 
    ActiveCell.FormulaR1C1 = "1" 
    ActiveCell.Offset(0, 2).Select 
    ActiveCell.FormulaR1C1 = "1" 
    ActiveCell.Offset(0, 2).Select 
    ActiveCell.FormulaR1C1 = "1" 
    Selection.End(xlUp).Select 
    Range("G2").Select 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Range("I2").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Range("K2").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Range("M2").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Range("O2").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Range("O1").Select 
    Selection.End(xlDown).Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(1, 0).Select 
    [COLOR="Red"]    ActiveCell.FormulaR1C1 = "=SUM(R[-1427]C:R[-1]C)" 
    Range("O1429").Select[/COLOR] 
    Selection.Copy 
    ActiveCell.Offset(0, -2).Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(0, -2).Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(0, -2).Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(0, -2).Select 
    ActiveSheet.Paste 
    Range("F1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("G1").Select 
    ActiveSheet.Paste 
    Range("H1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("I1").Select 
    ActiveSheet.Paste 
    Range("J1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("K1").Select 
    ActiveSheet.Paste 
    Range("L1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("M1").Select 
    ActiveSheet.Paste 
    Range("N1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("O1").Select 
    ActiveSheet.Paste 
    Columns("F:F").Select 
    Application.CutCopyMode = False 
    Range("A1").Select 
    Cells.Select 
    Cells.EntireColumn.AutoFit 
    Columns("F:F").Select 
    Selection.EntireColumn.Hidden = True 
    Columns("H:H").Select 
    Selection.EntireColumn.Hidden = True 
    Columns("J:J").Select 
    Selection.EntireColumn.Hidden = True 
    Columns("L:L").Select 
    Selection.EntireColumn.Hidden = True 
    Columns("N:N").Select 
    Selection.EntireColumn.Hidden = True 
    Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _ 
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
    False, Orientation:=xlTopToBottom 
    Range("A1").Select 
    Selection.End(xlDown).Select 
    Range("A1429").Select 
    Selection.End(xlToRight).Select 
    Range("G1429:O1429").Select 
    Selection.Font.Bold = True 
     '
End Sub 

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


I have a question about dynamic cell references that I can´t find any info about anywhere else.

This is what I want to do:

I need to create a sentence that changes depending on user choices. Example:

Cell A1 =Sheet!AX
Displayed text Dear Mr. "name"

Where the X is a number that will change depending on other things. I would love to do something like:
=Sheet!A(E1)
Where the value in E1 is for example 4. The result would then be =Sheet!A4
and if I changed the value in E1 the reference =Sheet!A(E1) would also change. I don´t know the syntax for doing this or if it is even possible.

Hope I explained myself...

/Fredrik

Heres my situation:

I have a cell which references another cell (i.e. =A2). When I insert a cell above A2 the new formula becomes =A3. How do I stop it from changing?
I want to insert as many cells as I want above A2 and the cell reference will always be =A2. I played around with $'s but to no avail.
Any help would be appreciated.
Cheers
Leigh

I have a column of data in column and a column of data in column F.

The data in column F is exactly the same as column C but in a different order.

Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H?

i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27.

All of the values are unique (I hope).

Hi

Please help, my vb is very limited and I know what i want this script to do it's just i can't seem to structure it correctly!

I am trying to get the script to select the cell refered to in cell D65536, cell D65536 contains the text "A495" and the value of D65536 changes on a daily basis. This value is calculate using a =counta(A:A) function.

Any help would be geatly appreciated, this is what i have got so far!!

Thanks in advance!

James


	VB:
	
 Test() 
     '
     ' Test Macro
     ' Macro recorded 14/11/2005 by James Newton
     '
     
     '
     
    cellref = Range("D65536").Value 
     
    Range("cellref").Select 
     
     
End Sub 

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


I am using Excel 2003.

Row #1 has labels from A to Z
the next 10 rows have data
Row 15 has the Max of each column
Cell B16 has a formula =Max(a15:Z15)
I get a Value back

My questions are:
1. Can I identify the Cell location that has the max value in Row A15:Z15?
2. Can I identify the label for that column?

I could not do it using VLOOKUP or HLOOKUP or others
I can do it using Find (CTRL + F)

If Excel is correctly idntified the cell by running the formula, it must have the Cell reference as well as the value stored some where. I think.

Is there a simpler way?

Thank you for your help.

Hi,

Looking for a way to have the cell reference in a formula dynamically change
depending on where the workbooks are copied to.

Two folders (A and B). Folder A has a workbook called a.xls and folder B has a workbook called b.xls.

The MAIN folder is located at C:MAIN
The two folders (A and B) are sub folders of a common main folder (MAIN).
so

C:MAINAa.xls and C:MAINBb.xls

In worksheet a.xls (C:MAINAa.xls) there is a formula, part of the formula refers to a cell A1 in worksheet b.
'C:MAINB[b.xls]Sheet1'!$A$1

If I copy the MAIN folder with the sub folders to another location, the formula stiil refers back to
'C:MAINB[b.xls]Sheet1'!$A$1.

Is there a way of writing the formula so that the cell referred to in the formula will always have the same relative path as worksheet a.

So If the MAIN folder is copied to say C:OZGRID
the cell reference in the formula will now read
(or some equivalent giving the same result)
='C:OZGRIDMAINB[b.xls]Sheet1'!$A$1

instead of the original
='C:MAINB[b.xls]Sheet1'!$A$1.

Thanks for your help,

Glenn