Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Formula Reference Cell Address In Cell

Hi All,
I am a newbie in VBA and need some help. I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?

I am attaching here the sample file for your reference. Thank you very much. Would really appreciate any kind of help.


Post your answer or comment

comments powered by Disqus
:) Hello All,
I would like to know how I could change the cell address in a formula without having to edit the address.
Example:
Month listed in range C4:N4 (one month per column)
In the cells listed below each month is data that I would like to reference into a summary page.
The existing addresses are
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$E$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$F$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$G$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$H$11
etc...

Each month I must manually change the cell address column letter to extract the data.
Is it possible to write a formula that will change as the month reference changes?

I have got as far a getting the cell reference column to change but it will not work as a formula should.
My attempt is

Final result:- =U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$E$11
Column reference:- =CHOOSE(MID($W$13,FIND(" ",$W$13,1),3),3,4,5,6,7,8,9,10,11,12,13,14)
Path to address:- U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!

Any advise would be appreciated or a better idea.

Thank you and best regards

Max

---|A B C
------------------------
1 |12 32 44
2 |14 5 19
3 |54 23 77
4 |23 2 25
5 |54 12 66
6 | 3 54 57
7 | 23 2 25
8 |
9 | ##
10|c5

Need a formula in ## so:

In the cell ##, can we get value of the cell, whose address is indicated in cell A10.

As currently in A10 we have written C5, so 66 should appear in place of ##.

If we change the cell address in A10 to, say, B3, 23 should appear in ##.

In other words, we want cell formula with a varible cell address and this variable's value(C5, here) is in other cell (A10).

I have 2 Sheets, "Template" and "Notes".

In the Notes Sheet I have 3 Columns:
- Column A contains hyperlinks that activate only if the corresponding cell in Column B has a cell address in it. The hyperlink links back to the "Template" Sheet and to the cell address displayed in the corresponding cell in Column B. Column C is simply the text associated with that cell address on the "Template" sheet.
- So, the "Notes" sheet, e.g., has a list of 10 lines of Hyperlinks, cell addresses, and notes in their respective columns.
- The cell address in Column B and the Note in Column C are populated using a Macro ("Submit _Note" attached to a Command button on the "Template" sheet.
- Each time a user submits a note, a new line is created at the bottom of the existing list of hyperlinks and notes.

I would like to add code to the "Submit_Note" Macro that returns the user to the cell that that they just recorded the note for, e.g., user a writes a note in cell C3 of "Template" Sheet and clicks on the command button that runs the "Submit_Note" macro, which copies the note from C3 (and related cell address from C2**) to the "Notes" Tab.
** a separate macro records the cell address which is the subject of the note to cell C2

Thank you in advance.
Incuss

OS: MS Xp
Excel 2003

I am in say cell E127 and need t go to cell B2. I then need to return to
cell E127.
Problem is E127 is different each time. How do I read the cell address in
VBA so that I may return to it?
Thanks in advanced.
--
Richard

Using Excel 2003. I have a formula that obtains data from another sheet. I use several copies of this sheet within the same book and in every sheet the formula needs to point to a different row. Within the formula, could I use the content of a third cell to act as the cell reference inside the formula. The formula is ='Sheet 1'!a2. When I make a second copy of this sheet in the same book I want the formula to read ='Sheet 1'!b2. In this sheet there is a cell that has as content B2 (cell A10). Could I have the cell address used in the formula come from the content of cell A10? Each sheet has about 15 similar formulas and I may end up using over a hundred instances of this sheet. Manually changing each formula in each sheet would be tedious and time consuming.

when i am doing the coding

may i know if there is a way to replace the cell address $A9 below:


	VB:
	
Range("A1").Formula = "=IF($A9="""","""",SUMPRODUCT((LEFT(" & myFilePath & "!$C$2:$C$30,8)=$A9)*(" & myFilePath &
"!$D$2:$D$30=B$1)))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with a variable for use in VBA?
I have tried using string like below but it doesn't work

	VB:
	
 
CellAddr = ActiveCell.Address 

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


Is there a way to reference the value in a cell as part of a formula, like SUMIF?

Example:
Sumif in 1st worksheet in cell B5 sums colum A in 2nd worksheet (..,...,A1:A50).
On 1st worksheet, I'd like to enter "B" (or a range name) in cell A1.
The SUMIF formula in cell B5 referencing the "B" in cell A1 on the 1st worksheet changes the SUMIF formula to (...,...,B1:B50)

Any help would be greatly appreciated!

Hi All,

Is there an easy way to identify range or cell addresses in a formula. I want to find the alphanumeric part of the address.

For example in the SUM Formula =Sum(A4:A230), I wish to separate out A4:A230 part. Or at least I want to remove the number part in the address and just want to get Sum(A:A).

Is there a function that can do this? I don't want to do this using usual string/text operations as it becomes a laborious process and has to consider all possible cases of range addresses.

Thank you,

MG.

I have 2 related questions:

1) Using a formula is there a way I can pull the sheet name into a cell? So for example, if my sheet name is "SHAWN", can I write a formula that would insert "SHAWN" into cell A1. i.e.; in cell A1 of worksheet SHAWN, I enter "=sheetname()" and it returns SHAWN

OR, if I am trying to pull the sheet name off of another sheet. For example, I am on worksheet named SHAWN, and I have another worksheet named "BILL". On the sheet named SHAWN, can I reference the sheet named "BILL" to get and pull out the worksheet name. i.e; in cell A1 of sheetname SHAWN I enter "=sheetname(BILL!A1)", and it would return "BILL"

2) Description - Lookup a value out of an array on another sheet, and reference the sheetname by way of a formula:

I am on worksheet named SHAWN I have BILL entered into cell A1.
In cell A2 of worksheet SHAWN, I have the date "April 1st, 2005", which corresponds to a array on my worksheet named BILL (for illustration, lets say my array is located on worksheet BILL in cells A1:B10). Can I write a lookup formula that looks up my date in my array on worksheet BILL, and in the formula reference cell A1 to direct the vlookup to my worksheet named BILL
ie; =vlookup(A2,unknownformula(A1)!A1:B10,2,false)

thanks in advance. Im sorry if im not clear and easy to follow in these questions.

Hi, I'm sure my title is very ambiguous (and I'm not advanced with Excel terms), so I will just explain what I am trying to do.

Two worksheets datafile: I need to reference a cell in another datasheet, and then use OFFSET to get values from the referenced worksheet.

So...

Worksheet A
........A.............B.............C
1 SAMPLE.......100........500
2 EMPTY..........-.............-
3 SAMPLE2.....200........600

Worksheet B
Cell A1 will access either cell A1, A2, or A3 of wrkshet A (I will enter this formula manually each time, ex. ='Worksheet A'!A1)
Cells B1 and C1 in wrksht B would need to use OFFSET and reference whatever cell is referenced in Cell A1 of worksht B. (so, using our example above, Cells B1 and C1 would be 100, and 500 in worksheet B, respectively).

I know how to enter =OFFSET('Worksheet A'!A1,0,1) for cell B1 in Worksheet B, but again, I need to offset whatever cell A1 in Worksheet B is referencing. Formulas for B1 and C1 in worksheet B will remain the same, while A1 will change depending on what cell I am referencing.

Is there a way to do this?

Thanks!
Bill

Alright so what I'm trying to do is standardize a workbook. I want to reference a cell in sheet 21. The way the formula is currently set up, it looks at cell A100 in the current active sheet. I want the macro to reference cell C3 in sheet 21 or my input sheet.

Here's the code


	VB:
	
 CompanyHeaderChangeHorizontalSheet() 
     ' inserts the same header/footer in all worksheets
    Dim ws As Worksheet 
    Application.ScreenUpdating = False 
    For Each ws In ActiveWorkbook.Worksheets 
        Application.StatusBar = "Changing header/footer in " & ws.Name 
        With ws.PageSetup 
            .CenterHeader = _ 
            "&""Arial,Regular""&18&K000080                                                                                   
     &U&K000080" & Range("a100").Value 
        End With 
    Next ws 
    Set ws = Nothing 
    Application.StatusBar = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help!

Im filling in data in a table in a worksheet, but the data is coming from another worksheet. But the catch is that the data I'm using to fill, doesn't match the same layout as the table I'm filling. I've attached an example of what I'm talking about, but basically the issue is that if I do the first column by hand, I can't just "drag it over" or copy the formulas because as I move to the right, excel moves the reference cell to the right. Is there any way to tell excel that as I move to the right, the reference cell should move down?

Is that clear? Thanks in advance.

I did not mean to post that yet. The excel file is not useful. I'll repost once it's been fixed
-it should make sense now what I am trying to do

Please I need a formula to look for a string in a range of cells that can have text, numbers or both, and return the cell address. In the example below I need in B1 to have a formula that will look after this string "Sub-Total by Work Function:" in the range A1:A22. I tried =LEFT and SEARCH functions but did not work. I can't use as search string the whole phrase (Sub-Total by Work Function: Production, Sub-Total by Work Function: Studio) etc because I have a few hundreds Work functions. That's why I need to find the cells that have only the beginning of the string.

A1 Accnt Serv
A2 2.00
A3 3.55
A4 Editorial
A5 Sub-Total by employee
A6 Sub-Total by Work Function: Production
....

A10 Sub-Total by Work Function: Studio

A22 Sub-Total by Work Function: Managers

Thank you so much

Vera

Column A has not got a fixed number of rows (data pulled in from another sheet), and I am attempting to count the unique text for that column.

I'm using the formula =SUM(IF(LEN($A$1:$A$4)>0,1/COUNTIF($A$1:$A$4,$A$1:$A$4))) to count the unique ones, from cells 1-4, but I would like to change the '4' to whatever the row number is.

I have the formula =COUNTA(A:A) in cell B1, and this returns the number of rows that have data in them for the A column.

I want the value that B1 shows to replace the '4' in the first formula, so it adds from $A$1:$A$(whatever the B1 value is).

Any way to do this?

Thanks

Mxx

Here is my situation:

I created one worksheet called: Assumptions.
In this worksheet I have 6 different columns representing 6 different cases.

I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B colums for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.

Does anyone knows a quick way to do the changes?

My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.

I was thinking there was a way to highlight ALL the reference cells used in my
Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.

Thanks for your help.

Believe I have an error in my original set-up on this sheet. My goal is to be able to enter data in row 3 and the worksheet populate and total up the values. Which currently my sheet does, it populates and totals correctly, however, it does not play well with sorting.
Rows 1,2,3 should always stay the same.
Then when I try to sort rows 4 thru 48 by column BU from largest to smallest...
the formulas do not work since my reference cells used in the formula are being relocated after the sort.
It makes sense that I4 is no longer I4, etc. after the sort.
Is there a basic formula or a set-up I should be using?
Is there a way to reference a row location by the name in column A rather than row#4

Thanks for any guidance?

HRDerby2012-PickSheet.xlsx

I have a spreadsheet that I would like to use the created cell name (in Name Manager) (not cell address) in the "ISBLANK" function in an "IF" statement to return a null instead of "0" if the cell is blank.

The problem is I cannot figure out how to represent the cell name in the formula instead of the cell address, pulling from a list of cell names.

The cell referred to by the name "CDS_A0.A" is located in sheet1, cell address B1 and contains the data "2012"
The cell referred to by the name "CDS_A0.B" is located in sheet 2, cell address C3 and contains no data.

In sheet 3, I have a list of all the cells with created names in Name Manager (600+) and would like to return the contents (if any) to ColumnB, and if the is nothing in the named cell, return a null, not a zero ("0").

Cell_Name Cell_Content
CDS_A0.A 2012
CDS_A0.B
CDS_A0.C
CDS_A0.D
............

I have tried the following but it just returns the name:

Thank you all in advance for your time!

Hello all,

I am writing a UDF that needs to be aware of the cell address for which it will return a value. (For example, the UDF needs to be able to return one value if the cell is B12 and a different value if the cell is A3.) Short of passing in the cell's own address as an argument (lots of probs with that) or passing in a unique name where I can create a kind of lookup table (inelegant), I can't seem to find a way to do this. Ideally, I would love the UDF to simply be aware of the cell address where it is executing. Any thoughts?

Thanks in advance.

Hi everyone,

Can someone please guide me as to how would i store a cell address in a varaible


	VB:
	
If Not rngX Is Nothing Then 
    MsgBox "Found at " & rngX.Address ' the address it prints out when it finds it
    rngX1 = rngX.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with the above code im trying to find the month value in the cells and once it finds the location how do i store that location as in a varaible so that i can paste the copied values

Thanks

Hi Guys.
I am trying to record a Macro that can range cells, with the contents of another cell, in another workbook. For example:
In Workbook 1, I want to range the cells A1:D2
Range("A1:D2").Select
Can I replace the formula "A1" and "D2" with the contents of another cell in Workbook 2?
Workbook 2 has the specific ranges I need, and I would like to write a Macro, that can range with the contents specified in workbook 2.
"A1" is found on cell H4, and "D2" is found on cell F4 in Workbook 2.
Workbook 2 looks like this:
A1 D2
A3 D10
A11 D19
A20 D27
A28 D30
A31 D42
A43 D44
A45 D46
Can the contents of the formula, be replaced with the loacation of the cells in workbook 2
Is this possible?

Thanks in advance for any help offered.
Denis

can someone please tell me how to reference cell A1 in a macro?

Hi,

I have this WB: 3 sheets (red) have data (the number of columns changes); and one is to contain a resume.

I would like to go through each red sheet (the original file has 7 more of these sheets) and look for the value 0.

When that value is found, I would like to write the cell address in the resume sheet ("push") in the column A so that I can get the reference values of the column and row.

Thank you very much.

I have a macro that does part of what I need, but must be transformed...

I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this
..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4)

It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.

Can anyone show me a better way of doing this?

Thanks for your help.

Hi All Experts,

There are 3 sheets in my workbook. Sheet1 i have all datas, in Sheet2 A2 i have a left lookup formula that is

=OFFSET('Sheet'1!A4:J500,MATCH(A1,OFFSET('Sheet1'!A4:J500,0,8,ROWS('Sheet1'!A4:J500),1),0)-1,0,1,1)

Its working fine. I got a value in A2, but i need a it's cell Address in B2. This value in actually Sheet1A55. I need in B2 A55.

What is the formula?

Thank you
Raj


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