Use Value From A Cell Immediately Adjacent To Referenced Cell In A Formula

Feb 25, 2014

I need to create a cell reference which will allow me to specify a cell in a table by value (using indirect) and pull the value from a cell immediately one row over for use in a formula. Say I have a table Containing column A and B; I want to reference the value of a cell in column A (e.g. "Large Widget") and receive (in the formula) the value in column B (e.g. "41 cm^2").

View 4 Replies


ADVERTISEMENT

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

Go Immediately To A Particular Cell After Inputting Data In Another Cell And Hitting Enter Options

Feb 20, 2010

I'm inputting data in E3 and after I hit enter I want B2 to be
selected. But only for E3 and only in one worksheet. And possibly a
further step... Sometimes the formula in B2 has picked up data from
another place depending on what value was entered in E3. If it's done
this then I won't need to go to B2, I'd want to go to A8 after E3
instead of B2.

View 9 Replies View Related

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

View 9 Replies View Related

Prevent #REF! When Moving Cell Referenced By Formula

May 14, 2008

I have an IF formula on one worksheet that refers to another worksheet (ex: =IF(Page1!$A13="";"";Page1!$A13). The problem is when I move data from one cell to another the formula becomes #REF. (ex: =IF(Page1!#REF="";"";Page1!#REF)).

View 6 Replies View Related

VBA To Follow Formula To Referenced Range And Edit Source Cell

Feb 23, 2014

I have tried numerous versions of macros I found. Most get the same error of; Range object error or script error.

Here is the formula: VLOOKUP(C$13,VESSELS_DATA,ROW(30:30)-12)

What I would like to do:

1) double click to on the cell with the formula

2) goto the worksheet "DATA_TABLES" that contains the Named Range "VESSELS_DATA"

3) go down the rows until the c13 is matched

4) offset column the same as in the above formula "Row(30:30)-12"

View 9 Replies View Related

Formula That Returns Result & Font Color Of Referenced Cell

Apr 3, 2008

I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?

BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326

Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)

View 9 Replies View Related

How To Enter The Value Of The Cell Immediately Above If That Cell = 0

Jan 19, 2010

I am in the last stages of setting up a spreadsheet which will help calculate scores in a game we play and need help with the following:

If, within a column of random numbers (calculated elsewhere) there is a 0, what is the value of the number in the cell immediately above the 0, and if more than one 0 in the column, the greatest of these.

View 11 Replies View Related

Incorporate Adjacent Cell In Formula W/o Naming Cell Address?

Oct 2, 2009

I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple.

Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col).

I know that you can just write a formula in each cell like...

b1 will be =sum(a1+5)
b2 will be =sum(a2+5)

... but is there a shortcut so that you don't have to write out the actual cell address for each one?

Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like...

b1 will be =sum(left+5)
b2 will be =sum(left+5)

... where "left" represents whatever value is in the cell to the left of that particular cell?

View 9 Replies View Related

Apply Format Cell Immediately When Pasting Data

Dec 2, 2013

I have a column filled with time data that I want to copy in to a column formatted to a time format (hh:mm:ss), the thing is when i copy the data to it the format does not apply unless i double click each of the brackets, there are 14000 lines so doing it manually is a hastle to say the least, is there a way to apply the time format immediately?

View 2 Replies View Related

Text In Cell Then Formula To Put Particular Number In Adjacent Cell

Feb 16, 2014

I have text in column A of either "xxx" or "yyy"

I'm looking for a formula so that If

A1: "xxx" then B1: $100
Or if
A1: "yyy" then B1: $150

So when I write "xxx" or "yyy" in a cell of Col A

then $100 or $150 shows up in adjacent cell in Col B automatically.

I will need to sum the numbers later so Col B will need to be in number/accounting format.

View 3 Replies View Related

Formula To Add Adjacent Cell

Apr 22, 2014

I am wanting to add the value of an adjacent cell and keep the SUM in another cell... depending on that cell value.

For example: H10 value is "JW" .. so that number adjacent (G10) would be added to the cell G4 (next to JW), and same with "NK". If it says "NK/JW" then I need it to be divided equally.

N528MW Revised.xlsx

View 3 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

Display Formula In Adjacent Cell

Mar 19, 2009

I want to display the formula in the adjacent box to where it is actually used. Would anyone happen to know how to get that done? Example:

If cell A1 contains the following formula: A1+ A2
I want cell A2 to show : '= A1+A2'

View 4 Replies View Related

Referencing Cell And Filtering Causes Cell Referenced To Change

Jul 10, 2014

Warhammer Quick Reference Desktop.xlsx

Its on book 2.

I'm trying to reference the Cryptek total that I got in the previous area, and put that result into the grand total army point formula. When I reference the solution cell, or even copy and paste the very same equation into it, and then proceed to filter the referenced cells change...

The obvious solution that i thought would work is write =D45 next to Cryptek in the point value column, then if I filter it SHOULD maintain the information from D45 except it doesn't.

View 11 Replies View Related

Display Contents(text) Of A Cell Referenced By A Given Cell

Feb 9, 2009

I want to display the contents of cell B5 in A7 by using cell C7 to show the location of the cell to be displayed. Cell C7 has a formula that shows the cell location of information (text) that needs to be displayed. How can I show in A7 the contents of cell reference shown in C7? In figure attached I show an example to better understand. Want in A7 to show the contents of B5 by reading what is in C7. So A7 cell should show word Road.

View 3 Replies View Related

Formula To Search For One Word In Adjacent Cell?

Jul 8, 2014

I've got a long list of company names, i'm trying to create a formula to see if they match but the problem is the names won't match exactly (e.g it would be Joe Bloggs Limited & Joe blogg's ltd). I've thought about it and think the best way to see if they match would be to take the first word from the first cell (in this case Joe) and then search for it in the next cell and return TRUE if it exists anywhere in the cell and FALSE if not.

View 3 Replies View Related

Formula To Check Adjacent Cell Value, And Report.

Jan 5, 2009

Ok. I am creating item lists in excel for every product that my company sells. I am in need of a formula that does the following:

I want the cell to check to see if there is a value in another cell, and if there is anything at all, i want to display the word Yes as the value, or leave the cell empty.

So I want the cells in Column A to Check if the adjacent cells in Column B have a value. If the column b cells have a value I want the cells in Column A to display the word "Inventory Part". If there is no value I want the cell left blank.

View 7 Replies View Related

Find And Replace By Adjacent Cell Formula

Feb 4, 2014

Is there a formula that can find a keyword within a cell then replace it with a formula that will add the adjacent cell value up.

A1=103 B1=104
A2=104 B2=105
A3=105 B3=Out-1p-(1)
A4=110 B4=111
A5=111 B5=112
A6=112 B6=113
A7=113 B7=114
A8=114 B8=Out-3p-(23)

I am looking for a formula that can automatically calc (B3) to 106 (based upon cell A3) if the cell has a value of (*out*).

View 1 Replies View Related

Auto-Fill Formula When Adjacent Cell Changes

Jan 6, 2007

what is prompting this formula to auto fill down when a value in an adjacent cell is entered.

In column A I enter an unformatted fax number.
In column B I use the following Text Formula:
"="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4)

Someone in my office copied the workbook and now any time a value is
entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office.

There are no macros embedded in this workbook so no event is firing to cause this.

I have attached a small copy of the workbook called "Auto format1.xls"

How to use:
-Select cell B20 and not nothing is in it.
-Enter any 9 digits in A20 and B20 somehow
copies down the formula from B19.

how to duplicate this.

View 9 Replies View Related

Formula To Keep Adjacent Cell Blank When No Date Is Added

Jan 23, 2014

I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.

To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))

I tried adding as an extra part, for example:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)

It always brings back the answer of 366, and does not make the cell blank...

View 2 Replies View Related

Macro - Fill Formula Down Until Adjacent Cell Contains String

Jul 17, 2014

I've created the following Macro based on some of the posts on this great forum! It works great, but I'd like to tweak it for another purpose where it would fill the formula down until a cell in column AF had the value "#N/A" or stopped having the value "clean". The #N/A is from a formula result so I'm not sure if or how the Macro would treat this.

Code:
Sub Y_CleanUp3()
'
' Clean Up Bad Data Macro 3
Dim LR As Integer
Range("AH2").Formula = "=VLOOKUP(X2,'[Territory by Zip Code.xlsx]Sheet1'!$A$2:$B$135000,2,TRUE)"
LR = Range("AF" & Rows.Count).End(xlUp).Row
Range("AH2").AutoFill Destination:=Range("AH2:AH" & LR), Type:=xlFillDefault
With Range("AH2:AH" & LR)
End With
End Sub

View 2 Replies View Related

Macro To Populate Cells With Formula Based On Adjacent Cell?

Mar 31, 2014

I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.

View 2 Replies View Related

Cell Referenced By Two Numbers

Feb 15, 2009

In cell A1 I have a number representing a column and in cell A2 I have a number representing a row. In A3 I wish to obtain the value from the cell referenced by these two numbers.

ie
A1 = 3
A2 = 4
A3 = value from cell C4 (column 3, row 4). How do I write a formula in A3 to look this value up?? I know about the range function in visual basic but I just want to be able to do this using a spreadsheet formula.

View 3 Replies View Related

File Name To Be Added In Referenced Cell

Mar 27, 2013

I have 15 files in a folder and need vba code which would open every file from the specified folder and add that file name in the row starting cell "D1" in the "Master File". Below is the code which opens the file from the folder, but need the updated code which will add the opened file name. Also can u pls update this code to not open the "Master file" which is in the same folder when it loops..

VB:
Sub Login_summary()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long

[Code] ....

View 5 Replies View Related

Putting Text In A Referenced Cell

Jul 10, 2008

In my excel sheet I have a list of where they go, which are basically excel cells.. i.e. A1 is the top left corner.

What I am trying to figure out how to do is, is there a way that I can get excel to see "A1" and then put it in A1 of another sheet on the bottom. Thus I get the same grid I have on the wall, making a map of my things.

Also, I could just cut and paste, but the problem is I'm working on organizing the stuff on the wall, and if I do it the way I want to, when I move things, they should automatically be moved on the chart.

So basically I need it to look at a cell for the reference, choose text from another cell (which will be concaternated(sp) text), and then place it in the referenced cell in the chart sheet.

View 13 Replies View Related

Date Recognition In Referenced Cell

Nov 11, 2009

I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.

My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....

View 9 Replies View Related

Lookup File Referenced From Other Cell

Jan 2, 2008

I have a basic blank file which takes production volumes of a product as an input and breaks the figure down to give individual component usage as an output. It then uses this figure to tell the user the best configuration to hold stock.

This file is 'save as' each month as the month, year and filename (ie: 11.2007MPS.xls, 01.2008MPS.xls) and then the monthly production figures are entered.

Within this file there are 'comparison' worksheets, cells on this page look into the previous months file and tell the user where changes have occurred; showing the physical changes that should be made. This lookup is referenced to a cell that gives the previous month ('MPS INPUT'$D$13), this is filled in by the user.

At current I am using the Vlookup(Indirect formula:
=(VLOOKUP(B4,INDIRECT("'I:SCIPMPS["&'MPS INPUT'!$D$13&"MPSCOPY.xls]Row 2'!$A$2:$N$100"),5,FALSE))

Yet, this requires all the previous months’ files to be open which isn't really feasible in the long run. Solving this issue using custom 'add-ins' (as i believe have) is not feasible either since the file will be used by many users who are not amazingly I.T literate.

View 6 Replies View Related

Bring Referenced Cell Values To Top Of Worksheet

Apr 18, 2014

ATTACHMENT: Order Form.xlsx

I am creating an Order form for my job in Excel. On this form, it displays the Top 100 items for sale. All the basic functionality of the form is working fine, my real issue is with the order summary tab.

On this tab, I would like for any Items with a Quantity on the first Sheet to transfer to a summary on the Second sheet.

So far, I am able to get the data onto the second spreadsheet. My final question is, can make the items on the summary sheet go to the top available row? Basically if I have items in row 3, row 7 and row 9 - I would like for the items to automatically fill in rows 3,4,5 respectively. (on the second worksheet)

I have seen people make this happen (on this board even) but for some reason, when I try using those formulas I do not get the results I need. I have attached the file to this post.

On the Tab entitled "Adv Piano" this the product list. On that tab, customers can put a Quantity in column G for the items they want to order and that item's details, price, and quantity will automatically appear on the 2nd tab, "Order Details" in columns I - L in the corresponding row (this is already done)

I need the information in the columns I - L on the "Order Details" sheet to appear in columns C - G on the same sheet, in the same order but to fill in towards the top with the corresponding quantities, item details and price remaining in the same row.

Order Form.xlsx

View 10 Replies View Related

Sumproduct Exact Match To Referenced Cell

Jan 16, 2009

I have the following formula in a cell that gives me a sum of items matching certain criteria.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved