# 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
ADVERTISEMENT
Jun 23, 2009

HTML Imagine the following table (Ax, etc is the cell position):

A1=John C1=Name D1=Date E1=Version F1=IP

A2=10/2/2008 C2=A1 D2=A2 .... ....

A3=Client version 7.01 C3=A1+5pos D3=A2+5pos

A4=172.24.3.5 C4=A1+10pos D4=A2+10pos

A6=Michael

A7=10/2/2009

A8=Client version 7.03

A9=172.24.3.80

A11=Susan

A12=2/2/2007

A13=Client version 7.02

A14=172.24.3.200

I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;

Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;

C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :

View 4 Replies
View Related
Jan 10, 2014

I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.

Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)

The data looks like this:

A1 = formula resulting in 1.76%

B1 = formula resulting in 1.90%

C1 = formula resulting in 1.69%

D1 = C1*B1/A1 resulting in 1.8218147382920100%

The desired result is 1.82%.

View 4 Replies
View Related
Jan 29, 2009

I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.

I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.

I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.

The index column is used in some conditional formatting rules, so I would like to keep it anyway.

View 7 Replies
View Related
Nov 8, 2013

The above formula is not working as in A9, I have a formula although there is no physical text in the cell as the criteria applied is false. However, B9 still appears.

=if(istext(a9),b9,"")

View 3 Replies
View Related
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
Jul 8, 2008

I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.

I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?

View 9 Replies
View Related
Feb 2, 2007

if i am copying cell A and pasting it into cell B, and cell B is shaded gray and cell A was plain white, how do I make sure the color of cell B wont change?

View 2 Replies
View Related
Oct 1, 2011

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.

View 2 Replies
View Related
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
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
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
Jan 7, 2009

Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)

View 3 Replies
View Related
Jul 25, 2013

I have run an INDEX and MATCH formula to give me a resultant cell reference number. I now want to copy a cell to this specific cell reference using another formula.

For example the data I want to copy is in cell A1 and in cell A2 I have the result of the index formula (B1).

I know how to manually do a formula for this but wanting to know if there is a uniform formula that I can use that looks at the formula result of cell A2.

View 5 Replies
View Related
Aug 18, 2009

I am wanting to convert a cell reference text

"=$A$1"

to an actual cell reference

=$A$1

Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.

I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.

I'm NOT wanting an external cell to convert it for me

i.e. INDIRECT(CELL)

because I am wanting to copy the answer to another independent spreadsheet

I'm NOT wanting to paste values

i.e. return the answer from cell $A$1

because I want the cell reference to remain within the cell.

View 3 Replies
View Related
May 22, 2008

how can I use the cell function inside a NPV fuction as one argument of the start for the NPV string to calculate?

View 3 Replies
View Related
May 20, 2009

I've hit a problem when re-working some of my spreadsheets. Basically I have this bit of

View 9 Replies
View Related
Jan 15, 2008

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?

View 9 Replies
View Related
May 16, 2014

I pulled a set of financial statements from a database into Excel. All of the values are hard coded but I would like to make them dynamic, while maintaining the original content. For example, If cell C3 is a revenue figure for 2012 ($1,000) I would like to link it to cell C5, which will be a currency conversion rate (2.0). So I would like cell C3 to read =1000*C5, which would equal 2000, and for cell D3 to equal 2013 sales*D5, etc. I would also like this to go across a specified range so that I can also change operating expenses, taxes, etc over a number of columns (dates) that I define, all linking to the conversion rate for the given year. Having another tab where you link and anchor is an option but I would prefer something in VBA where I can qickly include this functionality with new workbooks that I download.

I am using Excel 2010.

View 5 Replies
View Related
Apr 8, 2014

I have a tracking sheet that is used to show where a specific project is within the lifecycle and would like to automatically set a summary value depending on the last data entry within a range of cells in a row and also set it to RAG status depending on the value.....

View 2 Replies
View Related
Nov 11, 2008

I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3

View 5 Replies
View Related
Jul 28, 2012

I am using the DOB formula of =DATEDIF(AA19,NOW(),"y") & " years, " & DATEDIF(AA19,NOW(),"ym") & " months, " & DATEDIF(AA19,NOW(),"md") & " days". In the next cell I want it to look at the previous cell and determine if "older than 2 years". I have tried the "search" and isnumber, but I keep getting the wrong answer.

View 1 Replies
View Related
Jul 26, 2009

Hello,

I am trying to create a formula in Excel to preform a "SUM" on cells that only have a letter Y (as in "yes") in the cell next to them.

For example

View 3 Replies
View Related
May 11, 2012

I need to display cell AV13 from sheet 2 on sheet 1. To do this I go to sheet one and in the cell I type =Sheet2!AV13 and voila I get my value. My problem is AV13 is determined by two list boxes, one finds the column AV (named the cell that holds this value as StageColumn) and the other finds the row (named SubCatRow). What I want to do is have a cell that gets this information from Sheet 2. I tried to concatenate the string, but all I get is the text.

The concatenation string was =concatenate("='Sheet2'!",F25)

I have F25 containing the formula =Address(StageColumn,SubCatRow)

View 1 Replies
View Related
Mar 24, 2013

i have a table of data and a formula at the right which gives the max of that data and would like to get the row header and column header of the cell that is giving me the maximum value

e.g on the below the maximum value is 24346.3 and i would like to have in another cell Australia Diversified REITs

Diversified REITs

Homebuilding

Industrial REITs

[Code].....

View 7 Replies
View Related
May 22, 2009

I am trying to figure out how to reference the formula from another cell. For example, say cell A1 has the formula =indirect(address(row(),2)). Assume B1 equals 10 and B2 equals 20. The formula in A1 would yield 10. How can I make A2 reference the formula in A1 so that A2 yields 20? I need it to actually reference the formula in A1, simply copying and pasting A1 or dragging A1 will not suffice.

View 9 Replies
View Related
Feb 18, 2010

I currently have the formula =+'Mar-Final'!$C10*M3/N3.

I would like to have the 'Mar part be input from a cell.

For instance, if I put Feb in cell A3, then the formula would look like ='Feb-Final'!$C10*M3/N3.

View 9 Replies
View Related
Jul 31, 2008

I have a column (A1:A26) with a series of numbers which I want to multiply by a second variable in another column (Sheetb:C1). So I write a formula for a new column - =A1*Sheetb!C1. I want to multiply each value in (A1:A26) with just the value from Sheetb:C1. However, when I drag the 'fill series' pointer down, it automatically increments the cell its is multiplying by i.e. 1st cell is A1 X SheetB!C1, 2nd cell is A2XSheetB!C2... However I want it all multiplied by SheetB!C1.

View 2 Replies
View Related
Apr 10, 2014

I have a formula to extract the worksheet name and add the "!" to it so it can be used in a copied formula... I'm just having trouble writing the formula to reference the text in the cell with the worksheet name. If it's easier, I also have the list of worksheets without the "!" - e.g., Sheet1, Sheet2, Sheet3, etc...

I want the formula to calculate, in my example, the sum of 2 cells on a different worksheet.

View 6 Replies
View Related
Mar 19, 2014

So finally got my sumrpod working using this formula

=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))

Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.

e.g A1 = South Pac Load Tracker - MAR14.xlsm

Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))

View 1 Replies
View Related