Formula For Cell Value Based On Reference Or Position
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
ADVERTISEMENT
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
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
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
Oct 13, 2013
I want to resize a shape and set the height to a specific cell. How can I achieve that?
View 7 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
Jan 23, 2009
Right now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.
Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.
ww = Work Week.
In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.
View 3 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 11, 2008
This seems really basic, but I can't seem to find it...
Using Visual Basic, if I have a named range, lets say like "NAMEDRANGE", how can I refer to cells in that range by their position in the range? For example, if I want to refer to the cell in the 2nd row and 3rd column of the range.
Also is their an easy way to refer to the first(top-left) cell in a named range?
View 3 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
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 9, 2014
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
View 3 Replies
View Related
Mar 6, 2014
i need a macro which puts the formulas into the cells as per attached.
the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable
my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this
beam macro.xlsx
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
May 12, 2014
I have several worksheets and I want to summarize all of their data on the main page. The second worksheet is called Grady, the third worksheet is called Stella and the forth worksheet is called Westminster.
On the Main Page cell B1 is, "Grady", C1 is, "Stella" and D1 is, "Westminster". On B5 I need the value of cell C5 on Grady but I don't want to have to type it in as this formula will have to be carried over for several columns.
Currently I have:
Code:
=Grady!C5
In cell B5 on the main page but I want:
Code:
=B1!C5
But this only gives me #REF!
Can this be done without using VBA?
View 5 Replies
View Related
Mar 6, 2014
I want to save my file as a pdf based on cell reference A4.
This is what i have
[Code] ....
View 2 Replies
View Related
Mar 21, 2014
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
View 9 Replies
View Related
Apr 22, 2009
How can I automatically hide rows based on a cell reference? For example, If A1=0 then hide row 1.
View 9 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
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
Mar 20, 2009
I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.
For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.
I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)
View 5 Replies
View Related
Dec 12, 2009
I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.
Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.
View 5 Replies
View Related
Nov 10, 2011
I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.
My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.
However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.
View 3 Replies
View Related
Jul 3, 2007
I'm trying to find out how can I fill a range of cells when clicking in a Command button in Excel. The process should be:
1) The user opens my XLS worksheet.
2) Goes to the next empty row and fill some specific fields
3) Then he should fill up 15 parameters on the right as Y or N
4) In case all of them are correct instead of going one by one typing "Y" I want he clicks in a button (with VBA code associated) and then 15 cells on the right should be filled with "Y" value
View 8 Replies
View Related
