Return Single Cell Address From Range

May 18, 2007

I have a list of ID's on one sheet that are also located in a large matrix on another sheet.

This macro uses the Find_Range function to find each ID within the matrix and return the column header where the ID was first located.
There is also a line which return a list of ranges, indicating all the places where the ID was found.

What I want is to convert the list of ranges into a list of corresponding column headers (ie row 1 of all columns in the range)


' Number of id's in list
RowCnt = Application.WorksheetFunction. CountA(Columns("A"))

For I = 2 To RowCnt

LookFor = Sheets("ID List").Range("A" & I)
Set InRng = Sheets("Matrix").Cells
Set Found = Find_Range(LookFor, InRng, xlValues, xlWhole)
On Error Resume Next ' If value is not found
Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _
.Cells(1, Range(Found.Address).Column) ' Return column header
Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses
On Error Goto 0

Next I

View 9 Replies


ADVERTISEMENT

Find Value In Range Then Return The Cell Address?

Mar 16, 2014

construct a formula that finds a value in a range , then returns the cell address of that value.

Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.

I have tried the address & match function but gives me an N/A error.

View 3 Replies View Related

.range.find Need To Return Cell Address

Nov 14, 2007

when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?

so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng

set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious)
ok so say it picks up..... cell A69... how do i get it to save range A69

View 9 Replies View Related

Return Cell Address Of Value In A Range Contains Unique Values?

Dec 12, 2012

Here is a sample of database,

A
1
Jan-12

2
Feb-12

3
Mar-12

4
Apr-12

5
May-12

6
Jun-12

7
Jul-12

8
Aug-12

9
Sep-12

10
Oct-12

11
Nov-12

12
Dec-12

If i lookup a value in the range A1:A12, say Sep-12, i need to get the cell address instead of the value of the row. but i know how to get cell address using CELL function. but i need to get cell address when i lookup the value.because lookup value will be dynamic.

View 5 Replies View Related

Return Cell Address That Falls Within A Range Of A Custom Lookup Function

Jul 3, 2009

I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

View 3 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001
q9002
q9003
q9004
q9005
q9006
etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies View Related

Address Single Cells In Named Range

Dec 22, 2006

I'm having a 'mental block' day. I have a Named Ranges in a spreadsheet (Office XP). The range is defined as =OFFSET( ' Stock List'!$K$1,0,0, COUNTA('Stock List'!$F:$F),4)

i.e. starts at K1, is 4 cols wide and as deep as there are occupied cells in col F. (as I understand it). I want to treat this as an array in VBA and use 'x' and 'y' as indexes into the array. I want to either get the value from a single cell, or set the value in a single cell, which contain a string. I know its not correct but I have the formulae as follows, and for the life of me I cant figure out what the correct formula is -duhh!!

Dim x As Integer, y As Integer, z As String
x=3 'dummy test code
y=2
Range("MakerExtractArea").Offset(x, y).Value = "dummy"
z= Range("MakerExtractArea").Offset(1, 0).Value

View 7 Replies View Related

How To Return Address Of Column Or Cell Select Not Just Return Value

Aug 18, 2014

How to return address of the column or cell I selected from Application.Inputbox, not just return the value?

[Code] ....

View 8 Replies View Related

Split Address Which Is In Single Column Which Contains First / Last Name And Address Without Any Comma?

Apr 24, 2013

I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148

DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121

abdi abdi 5390 monterey rd #6 sanjose,CA95111

Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148

I need the result to be in a way like -

3430 Chemin de riviere
San Jose
CA
95148

3838 Glengrove way
San Jose
CA
95121

5390 monterey rd#6
San Jose
CA
95111

3212 Gateland CT
San Jose
CA
95148

I have around 12000 records with the same format.

View 9 Replies View Related

Return Value & Address Of Named Range

Feb 20, 2008

how to get the value of a cell by its name. For example, i give cell B2 Name "Hello" and value "2". how to use vba to get the value of 2 using its name 'Hello', but not the cell 'B2'. Somethings people delete and add rows or columns and using range may lost the right value.

View 2 Replies View Related

Return Range Address Across Different Columns Based On Conditions

Apr 23, 2008

I am trying to return a cell address range based on values in different columns. Attached is an example.

In Column A1 I have "Range" and below that a set of numbers from 1 to 31.

In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.

What I want to do is as follows:-

I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.

I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.

View 9 Replies View Related

Return Nth Value From Single Column Range

Apr 29, 2008

Assume A1:A10 filled with the characters A to J.

I'm looking for a way to return the letter "C" when 3 serves as the index_num BUT I do not want to present all 10 characters in the formula.

I would like to use the range A1:A10 =Choose(3,A1:A10) or, if applicable, to name the range as DATA and then use: =Choose(3,DATA)

I tried all I know, including INDIRECT - but found no solution [is there any ?]

As I'm familiar with all workarounds - I will appreciate replies that use CHOOSE.

View 9 Replies View Related

Lookup And Return Cell Address

May 1, 2009

I have a range of cells (C29:BB29) containing zeros and integers, I want to lookup the smallest non-zero value from that range in another range (C11:BB11) and return the cell address from C11:BB11 to use in a further SUM formula.
Is that possible?

View 9 Replies View Related

Return Cell Address Of Lookup Value

Jan 24, 2008

I'm trying to return the cell address of an lookup. The lookup result is correct, but when I try use the below Address function, it just returns the text value (i.e., the formula itself) instead of the cell address. Below is the an example of each: ...

View 4 Replies View Related

Return Address Of Moving Cell

Feb 29, 2008

I want to be able to display the location of a specific cell no matter if I move it around by inserting cells above it. I've used =ADDRESS(41,2,1,TRUE,B41). Cell B41 contains the word "Tommy". So the result of this formula returns this: Tommy!$B$41. But now if I insert a row above cell B41, "Tommy" moves to cell B42 but the formula still returns: Tommy!$B$41. How can I get the formula to display the new cell address of "Tommy"?

View 8 Replies View Related

Return Cell Address With Maximum Value

Mar 13, 2008

I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4)

It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.

View 2 Replies View Related

Return Cell Address Of Maximum Number?

Jan 20, 2009

i'm looking to return the cell address of the max number in a given range?

View 3 Replies View Related

Return User Changed Cell Address

May 4, 2008

The format for the sheet is set in stone and can't change, and therefore my macro's operate on set columns / rows amd cells.

Because the sheet is huge, im trying to figure out a way to only run the macro's that need to run and more specfically on the changed location.

The Question

I need to create a routine that on the user changing a cell, it returns the cell position (row,column), then it will call a routine i have already which decides based on the cell location, which macro to run and where.

View 9 Replies View Related

Vlookup Function To Return The Cell Address

May 18, 2002

Is there a way to get the vlookup function to return the cell address, rather than the value, of the lookup?

View 9 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related

Formula To Find Text And Return Its Cell Address

Sep 6, 2013

Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?

View 9 Replies View Related

Return Cell Address Of Largest & Smallest Value In Column

Jun 2, 2009

How would I return a cell reference (address) to a cell that contains the largest number in a list?

I tried using "Address(large....) where I get the correct column, but the returned row # is the actual value in the cell (the highest # in the list).

I eventually will want to delete the highest number to leave the cell blank.

View 3 Replies View Related

Return Range Of Numerical Values In Single Column Based On Frequency Percentage

Oct 25, 2005

I have a single column of numerical values that may repeat many times within
the column.

I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value
NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.

View 9 Replies View Related

Find Multiple Instances Of Single Criterion In Row & Return To A Single Col

May 8, 2006

Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Scenario:
Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

View 14 Replies View Related

Sum Of Multiple Cells And Return It To One Single Cell

Nov 4, 2009

I'm trying to take the sum of multiple cells and return it to one single cell using the Vlookup

For Example I want to match the ID numbers from one spreadsheet and use the list of codes to return the value of the sum of all matched numbers. So in this example I would want the number 65 in one cell...

View 9 Replies View Related

Getting Chart Range From Range Address Contained In A Cell

Dec 3, 2013

I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).

In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70

I'm looking for a way to be able to use this typed range as the range for some chart data.

View 2 Replies View Related

Match Named Range To Range/Cell Address

Aug 25, 2006

I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?

View 4 Replies View Related

How To Return Multiple Values From Array In Single Cell

Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies View Related

How To Return Multiple Values From Array In Single Cell

Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies View Related

Return Multiple Values From Lookup To Single Cell

Feb 20, 2007

i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2

View 8 Replies View Related







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