Cell Reference Or Adjacent Cell Return

Apr 5, 2008

I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine!

Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2.

But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.

View 9 Replies


ADVERTISEMENT

Find Cell Value In Column & Return Text In Adjacent Cell

Jun 10, 2008

I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.

I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.

I want the user to be able to choose from the list or type the drug name in.

View 8 Replies View Related

Find Cell Value In Another Workbook & Return Adjacent Cell

Aug 12, 2008

I am trying to complete these steps:

Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls"

Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab

Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59"

My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...

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

How To Return Value Adjacent To Cell From Another Sheet?

May 18, 2009

I have one column of about 10 names (each of which is unique). The cell adjacent to these names is empty and I want to insert a value into this cell.

The value I'd like to insert into this cell is located in another sheet attached to the same file. This sheet has the same layout, except the unique name has the value next to it and the order of the names is different.

I'd like the formula to determine where the name from the first sheet matches the name from the second sheet. I'd like it to then display the data associated with that unique name in the first sheet, adjacent to the name in the blank cell.

View 6 Replies View Related

Function To Return Value Of Adjacent Cell

Sep 18, 2008

I am trying to write a function that will return the value in cell B based on its position adjacent to cell A (above). Cell A contains its own formula and may the values may change.

View 9 Replies View Related

Determine Date Return A Value From Adjacent Cell

May 2, 2014

column 1 is vouchers
column 2 is date

vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.

I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.

View 1 Replies View Related

Return Value Of One Cell And The Adjacent Cell As Well

Mar 18, 2009

I have some VBA which I am using to search though a worksheet for data matching an input box. I would like the search to produce a messagebox with the imputbox search result, as well as the adjacent cell. does anyone know an easy way of doing this? Below is the code I have so far:

View 2 Replies View Related

If Cell1 Reads Text1 Return The Value Of Adjacent Cell

Feb 25, 2014

A workbook containing 2 sheets. Sheet1 is a data entry sheet containing 8 columns and many 10s of rows of client data. The columns are (in this order) Client, Date, Code, Duration, CaseNo, Description, Staff. I also have a second sheet. Sheet2 takes the data from Sheet1 and displays the results of certain calculations, such as amt of cases a particular Staff person has and of what Code type.

Example:
- Total amt of P02 cases = 25
- # of cases Homer Simpson has = 10

So here's what I'm trying to accomplish:
Is it possible to have Excel return the total amt of time spent on a particular Code type? It would have to search through the Code column for a particular Code type (let's say "P03") and return the SUM of the Duration which resides in a different column.

Calculate by Billing Code.xlsx

View 7 Replies View Related

Find Date In Column & Return Adjacent Cell Value

Mar 22, 2008

I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.

View 3 Replies View Related

Lookup Nth Occurrence Of Date & Return Adjacent Cell

Jun 5, 2008

I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:

Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.

View 2 Replies View Related

How To Reference A Cell In Another Workbook And Return Cell Value And Comment

Nov 2, 2012

What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.

Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?

View 2 Replies View Related

Excel 2013 :: Check Cell For Text And Return Corresponding Text In Adjacent Cell?

Jan 28, 2014

I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).

What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.

View 9 Replies View Related

MSG Box To Search And Return Adjacent Cell Value For Phone List Extensions

Apr 18, 2013

I have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.

I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.

Ideally I would like the msg box to show both the employee's full name and extension.

View 4 Replies View Related

Return Cell Value 30 Cells After Reference Cell

Apr 1, 2014

I want to find the cells 30 past the reference cell and the corresponding value:

i.e. =Sheet1!D312 to =Sheet1!D342 (=Sheet1!D(312+30))

or

=Sheet1!D312 to =Sheet1!E312

is there a way to automate this without having to manually edit each formula?

View 8 Replies View Related

Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today

Apr 4, 2008

how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.

I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.

CREATE TABLES LIKE BELOW?Column A Column B Column C

March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................

View 4 Replies View Related

Return Cell Reference Of Cell With Specified Value

Jun 30, 2007

the formula which will return the cell reference of the cell that contains a specified value

eg

if column A has values 1-10 in order, b1 has the value 5, then what formula can i input in c1 that will give me the cell reference in the range in column A which contains the value in column B? (the result of the formula in c1 would be A5 in this example)

View 4 Replies View Related

Cell Reference Return Blank Or Zero?

Jun 15, 2013

In sheet2 I've many numeric value.

In sheet1, A1: =Sheet2!B12 which returns 0 (zero) though B12 of sheet2 is the result of =sum(B1:B11) i.e 660. But if in sheet1 A1=Sheet2!D12 or any other cell in sheet2 instead of B12, A1 gives correct result. That means cell A1 itself is not a problem. I checked format of B12 which is number format.

View 5 Replies View Related

VBA: Vlookup And Return Cell Reference

Nov 11, 2008

i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).

I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:

View 3 Replies View Related

Match & Return Cell Reference

Jun 16, 2006

I have a column of data in column and a column of data in column F. The data in column F is exactly the same as column C but in a different order. Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H? i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27. All of the values are unique (I hope).

View 2 Replies View Related

Reference Cell & Only Return X Characters

Oct 20, 2006

how I would go about referencing a cell on another worksheet to return only the last so many characters from the data in that cell. EG:

Sheet 1 cell A3 contains the number (stored as text) 12345678
Sheet 1 cell A4 contains the number (stored as text) 98765432

On Sheet 2 I want to put into cell B2 5678 (the last four characters from the data in Sheet 1 A3.) Also on Sheet 2 I want to put into cell B3 5432.

View 2 Replies View Related

Return Cell Reference Of Large Formula

Jul 23, 2008

I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.

I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).

If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:

=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)

I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.

View 9 Replies View Related

Return Cell Reference From Multi-Column Lookup

Dec 13, 2006

i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)

it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!

i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).

any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!

should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?

View 8 Replies View Related

Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

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

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

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

Reference Sheet (return The Value Of Cell B2 On Whichever Sheet The Text Of A1 Says)

Nov 1, 2009

If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?

As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:

View 2 Replies View Related

Find Cell Value Copy Cell And Adjacent Cell

Aug 1, 2007

i am trying to find a cell in column C (if cell =140) and copy that cell and the adjacent cell in column "D" to worksheets called" upload" then repeat through multiple tabs- repeating process and dumping values in columns on the "upoload" worksheet

View 3 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Can Cell Keep Cumulative Of Values Typed In Adjacent Cell On Daily Basis?

May 31, 2014

See the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...

Cumulative Input.xlsx‎

View 5 Replies View Related







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