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


ADVERTISEMENT

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

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 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 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 Data Based On Two Conditions

Nov 7, 2006

I have very limited vb experience and I believe itís the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice:

Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc.
Range U5 Ė U16 will be pre-populated with the line price for each month if the line quantity is less than 10
Range V5 Ė V16 will be pre-populated with the line price for each month if the line quantity is 10 or more

The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just donít have enough experience to write this myself.

View 7 Replies View Related

Return Values From Tables Based On Conditions?

Aug 22, 2013

how to return values based on some conditions.

So for the sake of this example i have two cells lets call them J1 & J2 , one with the client name and another with State (matches the table name)

How can i get it so when i type in say Client 1 & Table 3 it will return the information in client 1s column into table 1s rate column. Obviously i want to be able to type in any clients name / table and have the same thing happen.

the red is what i want returned into table 1s rate column

Table 1
Table 1
Table 2
Table 2

[Code].....

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

Return Data From Table Based On Multiple Conditions

Jul 26, 2014

Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?

View 4 Replies View Related

Return Formula Value/Text Based On Many Cell Conditions

Oct 16, 2006

This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)

Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses

Is there a formula/function that i can use (like the IF Formula) to complete the following information;

EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"

Column H lists all departments

Column A lists deaprtments

A3 representing the 1st Department needing training

View 8 Replies View Related

Query Worksheet & Return Rows Based On Conditions/Criteria

Jun 1, 2008

I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

View 4 Replies View Related

Check To See If Cell Address Is Within Columns Of A Range

Nov 12, 2013

I have a named range BGl (G1:R1). I would like to see if a cell address ($U$6) is within the columns of that range. I need a function to give me a true or false based on changing cell addresses.

View 5 Replies View Related

Filtering Columns In Excel Based On Public IP Address

Apr 18, 2014

I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-

ABC
http://192.168.44.29:2967
DENIED

BCD
https://d31qbv1cthcecs. atrk.js
DENIED

[Code] ........

In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://95.138.166.181 is public IP and we need to copy to other sheet.

View 5 Replies View Related

SUMIF Based On 2 Conditions From 2 Columns

Jul 16, 2008

Have had a search around the web forums for an answer to this and am not getting anywhere.

In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)

i want to:

where column a=apr and column E=212130 sum the contents in column M

View 9 Replies View Related

Copy Range Based On Certain Conditions

Sep 5, 2006

I have this macro to copy a range to another worksheet:

Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y
' las cajas ATR
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))
Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))
rng1.Copy
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
rng2.Copy
Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True
End Sub

The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.

View 9 Replies View Related

Sum Based On Multiple Conditions & Variable Range

May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

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

Fill Range With Data Meeting Conditions Based On Cell Value

Apr 23, 2009

refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View 9 Replies View Related

Fill Multi Columned ListBox Based On Range Meeting 2 Conditions

Mar 14, 2008

I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View 4 Replies View Related

Return The Value Based On Two Columns

Feb 24, 2014

I need a vba code to return the value if two column's values are in sheet1 matching into sheet2,

Let us assume that, I have two sheets in a workbook(Sheet1 and Sheet2), In Sheet1, Col A and B are there some value and same like in Sheet2(Additionally Result in Col C)

I want to return the value in Col C of Sheet1(till A2 to last used rows) from Sheet2(Col C), if the first two column's values are matching with both sheets.

I have attached a sample workbook.

View 8 Replies View Related

How Do I Lookup Based On 2 Columns And Return A 3rd

Nov 30, 2009

I have 2 worksheets with thousands of rows. I need to lookup 2 columns (customer account # and number of occurrence) and then return a 3rd column (type of occurrence).

How do I do this? Normally I use sumproduct but in this case I need the result to be the text value for the type of call, not a count.

View 9 Replies View Related

VLOOKUP Return All Columns In The Range

Aug 31, 2007

I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.

I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.

What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?

View 9 Replies View Related

Return Unique Values From Columns/Range

Oct 24, 2006

I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.

View 4 Replies View Related

Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.

EG

Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

View 9 Replies View Related

Find Data In Column Range & Return Cell Same Row But X Columns To Right

Jun 18, 2008

i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different.

The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work.

What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.

View 6 Replies View Related

Splitting Address Data From 2 Different Columns Into City And State Columns?

Dec 5, 2012

I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).

View 4 Replies View Related

Return Value Based On Range Of Criteria?

Sep 25, 2013

I would like Excel to return me a value based on many criterias in the same worksheet.

E.g

A
B
C

[Code].....

QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.

View 1 Replies View Related

Return A Lookup Value Based On A Range Of Options?

Jul 13, 2013

I have used the below formula which works in a standard cell, however I would like to have this is a VBA code using the Target.Offset option, however I can not get this to work. There may also be a better way of doing this.

The code is looking to see if the 1st cell (A118) is empty or not, if it is empty do nothing otherwise it then looks up the value in the 2nd cell (B118) and assigs the appropriate name from the range values.

VB:
=If(A118="","",LOOKUP(B118,{0,0;0,"";1,"Main Bank";71,"PFS";80,"Main Bank";106,"Dry Clean / Photo";112,"SCO";141,"Cafe";168,""}))

View 5 Replies View Related

Formula To Return A Values Based On Whether A1 Is Within A Range?

Aug 5, 2014

I am trying to create a formula that will do the following:

If A1 is between 95 and 99 then populate with 2

If A1 is between 90 and 94 then populate with 3

If A1 is between 85 and 89 then populate with 4

If A1 is between 80 and 84 then populate with 5

If A1 is below 80 then populate with 6

View 11 Replies View Related







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