Reference A Cell As A Result Of Another Cell

May 26, 2009

In simple terms. Assume the formula in Cell F5 is =A1. I now want to reference F5 and use the =A1 ref, rather than the result of =A1. e.g. I want F6 to give me the result of A2. So want to refer to F5, add 1 to it (or refer to the cell below it).

I know there's the easy way of copying relative formulas, but my actual problem is a little more complicated. I'm using an IF formula in F5, along the lines of =IF(F4="Tomatoes",G1,A1) Therefore the result if F4 doesn't = "Tomatoes" is A1.

I now want to put a formula in F6 that gives me the contents of A2, specifically by reading the resulting reference from F5, and looking at the cell below it (A2).

View 2 Replies

Formula Result As Cell Reference

Oct 1, 2009

I need to use the result of a simple calculation in one cell on Sheet1 (=A1+2) as the row in a cell reference on a different sheet. eg =Sheet2!A"n" where "n" is the result of formula.

View 4 Replies View Related

Get Cell Reference As VLookup Result Of Different Sheet?

Dec 12, 2012

In workbook 1, I have summary sheet with columns

A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate

Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .

View 5 Replies View Related

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View 3 Replies View Related

VLOOKUP Formula Dragdown Copies Previous Cell Result Instead Of Unique Result

Jun 10, 2014

When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.

When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.

I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.

View 3 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

View 9 Replies View Related

IF Formula That References A Cell And Returns A Different Result Dependant On The Number In The Cell Being Referenced

Oct 2, 2009

I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.

For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.

What I wrote as my formula is as follows-

=IF(A1=3,"TEST")+IF(A1=4,"RESULT")

It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.

View 2 Replies View Related

Change Result Cell Format Based On Input Cell Value

Mar 24, 2007

Macro Based on an input cell�s (e.g. B12) setting of �yes� or �no�, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is �yes�, change to grey shading if input cell is �no�. I would like this macro to run automatically whenever the input cell value is changed.

View 2 Replies View Related

Refernceing Cells: Formula That Looks At A Cell On A Front Sheet, And Then Returns The Contents Of That Cell As The Result If It Meets The Criteria

Apr 5, 2009

I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")

This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?

View 2 Replies View Related

Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It

Jun 19, 2008

I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.

Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:

Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3

Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob

So it would work as follows.
Tab2 column B will take Tab2 column A�s data check Tab1 column A and display Tab1 column B�s result.

Problem 2

Weekly league rank table that shows position movements week by week
Example.

Week1
1Jon
2Bob
3Fred

Week2
1FredUp 2
2BobNot Moved
3JonDown 2

Can Excel calculate/show the actual movements of league positions?

View 9 Replies View Related

VBA - Find Cell Containing Variable - First Result Above Active Cell?

Dec 26, 2012

Need to find the first cell above the active cell containing any sort of variable (integer, string value, cell fill colour, named range, etc...).

For example, a column contains multiple integer values between 1 and 10. I would like the macro to "locate" a specified value, say 3. Ideally the macro will select the first cell matching this criteria, located above the active cell. Once located, the resulting cell will be added to a range to be copy-pasted to a new worksheet.

I am planning to use this code for various applications in a workbook I am creating. At present the code will be used to locate string values, and cell fill colours. It is also likely this will extend to other types of variables in the future.

I've found information relating to the find function, but nothing specifically to find the first result above the active cell.

View 5 Replies View Related

Formula For Cell Result Based On Other Cell Input

Sep 3, 2007

I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.

Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.

View 9 Replies View Related

Cell Colour Change From Result In Adjacent Cell

Jun 1, 2006

I am trying to make the colour of cell change depending on the result in an adjacent cell and have found and adapted the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("x4:x202")) Is Nothing Then
Select Case Target
Case 11
icolor = 43
Case 21
icolor = 43
Case 31
icolor = 27
Case 41
icolor = 27................

View 4 Replies View Related

Absolute Reference (cell Reference Behind The Table)

Mar 11, 2009

I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View 2 Replies View Related

Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies View Related

Convert Cell Reference To Worksheet Reference

Jul 20, 2014

I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:

Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say

AAB
ABC
CDE

And have a formula in column B that converts this to

=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)

I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?

View 2 Replies View Related

Looking For A Solution On How To Convert A Returned Cell Address To A Cell Reference

Dec 2, 2009

I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.

View 2 Replies View Related

Change A Macro To From And Absolute Cell Reference To Selected Cell.

Feb 5, 2010

i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?

View 2 Replies View Related

How To Automatically Update Cell Format In Cells That Reference Another Cell

Feb 22, 2012

Cell C3 has "Joe"

Cell X44 is a VLOOKUP that retrieves "Joe"

Let's say Joe goes on vacation. The workbook user goes in and puts a blue fill in C3. Cell X44 would also need to change automatically.

What's needed to make this happen?

View 1 Replies View Related

Use Text In A Cell For A Formula As Cell Reference To Create Range

Mar 10, 2013

I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.

Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items

How can i use the text in those cells to put in a formula to call that as a range?

If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell

View 4 Replies View Related

Excel 2007 :: Reference A Cell That Is 10 Rows Below The Cell That Is Being Referenced To In Row Above

Dec 28, 2013

If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?

I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.

Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don't even know how to change to that style on my Mac.

View 9 Replies View Related

Maintaining Cell Reference According To Its Physical Position From Formula Cell?

Jan 29, 2014

I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.

Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.

I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.

View 3 Replies View Related

How To Reference The Formula Not The Result

Jul 11, 2009

I am setting up a spreadsheet that we can use with EditGrid online so we can post it in Forums. These will be used to track contracts for NFL players. I have used multiple formulas to do this. The problem is that I need to do this for 32 teams over 10 years. So what I want to do is be able to have a master sheet and then just have the other 32 sheets pull those formulas from the master but use the info on the individual teams sheet. I'll give you some basics.

The first year starts out where you type in a players position, name, contract length and his rating. here is an example of one of my formulas. The very basic first one

=if(D13

View 9 Replies View Related

For Each Cell In Range Result In Another Other Cell

Aug 15, 2014

I wanna make a worksheet Event, so everytime my Active Cell inside of this Range("A1:D4") then In Range("E1:E4") will show the number of Row and Column of Active Cell.

View 5 Replies View Related

Can A Formula In One Cell Put Result In Another Cell

Feb 7, 2007

In one cell (ex: A1)i need to put this operation
Ex: 10+10 or 20*20 or 10*20*43 or something

And in other cell in need the result of the cell (A1)
Ex: 20 or ..........

View 3 Replies View Related

LOOK For The First Full Cell In Column That Is In Reference To Cell On The Left?

Sep 4, 2013

I have got this working so far:

=LOOKUP(D9,'Financial Snapshot'!$H$5:$I$95)
D9 = 1.01
D10 = 1.02 etc

[Code]....

View 3 Replies View Related

Cell To Reference Just The Month From Another Date-formatted Cell

Nov 11, 2009

I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.