# Find Range Of Part Number And Bring Back Value 0 If Match Or Value Of Another Cell

Sep 4, 2013

I need a formula to check a range of parts and bring back a value of 0 if matched or the value in another cell if it doesn't match.

Part number H-200-3
Part number H-200-3-A
Part number H-200-3-B

If I enter any of the above part numbers then cell B1 returns 0 if it is a different part number then B1 would need to match cell A1.

## Find Median In Range And Bring Back Adjacent Cells

Jun 25, 2014

Median Indirect: I would like to find the median in a range of cells and then bring back the 2 adjacent cells

1.......................L1......................L2
2.......................P3......................P4
3.......................O9......................010

So the median of the range is 2, thus we would want the formulas to bring back P3 and p4

Column 1..............Column 2
P3...........................P4

## Index / Match And Bring Back Information One Cell Above

Jan 19, 2013

I am running an Index, Match off of totals, what I would like to do is when the formula finds the column_index_num to move up one row and bring that value back.

For example: if I am going to sheet2 and my formula would normally pull back the information on row 7 with a column_index_num of 5 (or column E), I want to bring back the information on row 6 column E.

How do I add the variable to bring back the information one cell above?

## VLookup To Find The Customer And The Dates And Then Bring Me Back An Asterisk In A Separate Column

Jul 27, 2007

I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.

I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.

Here is my formula now:

=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)

J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.

## Vookup: Bring Back The Info In The Last Populated Cell On The Row

Mar 3, 2009

I want to lookup some data and bring back the info in the last populated cell on the row, rather than column 4. I have written the below but it is bring back info from Column Index number 4.

=VLOOKUP(D4,'A2'!A:Z,4, FALSE)

## Array Formula Lookup - Bring Back Lowest Date And Cell Location?

Jun 4, 2014

=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx

Following on from a previous post need to add to my (forums) array.

I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.

## Bring Back Complete Printer Name

Nov 12, 2009

create an array of active printers. I edited the code to write the array to a range, then populate a data validation list with the printer names. All works great. The goal was to select a printer name from the list and run a macro to print some sheets. However, the function doesn't bring back enough of the name to do this. how to edit the function to bring back the whole name.

For example: after running the function the 1st printer returned ="\rizzoHQ-ENG-04"...when i record a macro to print to this printer it needs ""\rizzoHQ-ENG-04 on Ne05:". Here is the function unedited, I bolded the part i think is controlling the name:

## Using If And Formula To Bring Back Company Name

Jun 5, 2007

Name Qtr Year
Org 112007
Org 222006
Org 332006
Org 442006
Org 542007
Org 612006

I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.

The code should loop through a range of constant data (Region Name), until the Region Name is blank.

Dim OrgCell As Range
Dim OrgTargetCell As Range
Dim RegionCell As Range
Set OrgCell = Sheets("DATA Removed").Range("A2")
Set OrgTargetCell = Sheets("Overdue").Range("A3")
Set RegionCell = Sheets("DATA Removed").Range("C2")
i = 0

OrgCell.FormulaR1C1 = _
"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"
Do
OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value
i = i + 1
Loop Until RegionCell.Offset(i, 0).Value = ""
End Sub

## Multiple Search Criteria To Bring Back Data

Jul 13, 2007

I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.

Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?

Sub PopulateReport()

Application.ScreenUpdating = False

Dim MyFilter1 As String
Dim MyFilter2 As String
Dim MyFilter3 As String

MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string
MyFilter2 = CStr(Sheets("Report").Range("C4").Value)
MyFilter3 = CStr(Sheets("Report").Range("C6").Value)

Sheets("Waste").Select

Dim Rw As Long
Dim Rng As Range

Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A1:W" & Rw)

With Rng
.AutoFilter
.AutoFilter Field:=20, Criteria1:=MyFilter1
.AutoFilter Field:=2, Criteria1:=MyFilter2
.AutoFilter Field:=13, Criteria1:=MyFilter3
End With

how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!

## Inventory - Match Part Number To Multiple Vehicles Sharing That Same Number

Jun 3, 2013

I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.

Vehicle
Oil Filter #
Also used with
Dodge
51515
Honda
54565
Toyota
51515

I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.

## Search For A File And Bring Back Data To Master Spreadsheet

May 22, 2008

To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.

I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.

## Search Criteria Bring Values Back To A Results Column

Apr 25, 2008

i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results

LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True

## Search Page To Bring Back All Transactions Based On A Primary Key

Jun 28, 2009

I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:

Account NumberDateComments2343566/2/2009 \$ 111.43 3453465/1/2009 \$ 89.34 5676552/5/2008 \$ 643.23 8078989/3/2008 \$1,245.34 12543612/5/2008 \$ 56.65 2343562/2/2009 \$ 343.54 3482459/9/2008 \$ 78.76 9345641/2/2009 \$ 356.22 2343565/3/2008 \$ 529.66

The idea is to enter an account number like 234356 click a button and bring back:

Account NumberDateComments2343566/2/2009 \$ 111.43 2343562/2/2009 \$ 343.54 2343565/3/2008 \$ 529.66

I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.

## Find Nearest Match To The Number In Cell

Dec 11, 2013

In cell A1 I have a number. I want a formula that looks at that number and then looks at row C on sheet2 where there are rows and rows of numbers. I want the formula to return the nearest match to the number in cell A1.

## Look Through A Sheet, Find A Text Reference, Then Bring Up Something In A Cell

Jan 13, 2008

I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.

I just have a couple of queries after looking at my initial designs.

1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet).
E.G. House, Games Played, Wins, Losses, Draws, House Points

2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.

## Match Part Number Then Extract Quantity Onto A Different Sheet

Apr 3, 2014

I have 2 sheets in a file. One is given to me from our parts department containing all parts on a given truck model. The 2nd sheet is exported as a database file from inventor and imported as an update able sheet in excel.

I am needed to compare part numbers between the 2 sheets and the qty of each. So far I have it where it matches the part numbers and turns the ones I don't have in the model red. Now I'm trying to match the part numbers and pull the qty of said part from sheet 2, the imported one, and compare it to the to the qty in the 1st. All I really need to accomplish is matching the part numbers then copying the qty from sheet 2 to sheet 1 onto a new cell of the matched part number.

## Bring Data From Certain Row Based On Cell Number

Dec 31, 2009

I'd like to bring data from Sheet2 to Sheet1. If value at Sheet1 f2=5 it should copy from sheet2 b5:k5 tosheet1 a2:j2 If value at Sheet1 f2=10 it should copy from sheet2 b10:k10 tosheet1 a2:j2 etc.

## How To Add Back Number To Itself Throughout Range

Aug 2, 2014

I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.

## Find Text String And Report Back Data In Unknown Number Of Rows

Apr 26, 2007

For example

Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698

Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?

## Obtaining Largest Number In A Range But Then Pulling Back Name Of Team

Oct 16, 2013

I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...

Jan
Feb
Mar
Apr
May
Jun

Team 1
25087
23029
32991
21319
25271
25136.8

[Code] .........

## Find Part Of Work In Range?

May 5, 2014

I want to go ("E:E") and if part of the cell contains "WAL-MART" than put the word "Food" in cell G of the same row and contunie until end

## How To Match And Bring Third Value With Index

Jan 23, 2012

I am trying to match and bring the third value with index formula in book 1

Book 1
OFFICE KEYCHART#CPT
113491209503959551134912095703662011349120957200160
Book2
OFFICECHARTCPT
113491209503959551134912095703662011349120957200160

## Find Part ComboBox Choice In Range

Oct 28, 2006

I have ComboBox on a UserForm that is looking to a long list on a worksheet. A lot of the entries in this list start with a brand name instead of a more discriptive name. I really need to be able to find an entry with any key word (not just the first word) in this list.

## Use A Forumla To Find A Cell One Back

Feb 19, 2008

I have a worksheet with pricing & part numbers on sheet1. I put a formula on sheet2 that puts the lowest price in colum C of sheet2. Now I need to also get the part number from the cell before. So if the lowest price is in cell E3 I need to also get the part number in D3, and put it in B3.

## SUMProduct (182 Matches Part 1 Of Which 32 Match In Part 2)

Nov 18, 2008

Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).

=SUMPRODUCT(--(\$A\$1='7. PM BDE'! \$J\$3:\$J\$366))*(--('7. PM BDE'!\$L\$3:\$L\$366=1))

## If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"

Mar 17, 2009

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009
\$23.95 \$24.15

Using excel 2007

## Find Whole Cell Value, Not Part

Apr 29, 2008

I use this code to "search" a value in columns A or B and if found to select the row the value is in.

Trouble is if I look for say value 11 (the code finds 1) I understand what happens but need your help to fix it hoping to keep it as simple as it is. (I cannot use filters)

Dim wht As Variant, Found As Range
wht = InputBox("Searchfor")
Set Found = ActiveSheet.Columns("A:B").Find(what:=wht)
If Found Is Nothing Then
Exit Sub
Else

## Address From Cell Back To Range VBA

Apr 29, 2013

I have to macros I'm working with the first take a user set range and prints the . Address to a cell.

The second Macro I'm trying to get to look at that cell and pull out the range. So is there an opposite fuction to .

Address that will convert excel format to a VBA Range format. Ex A cell with \$A\$1:\$A\$2 to Range("A1",A2")

Macro 1

Code:
For j = 1 To x
c = 1
For i = 1 To ws.Range("AD" & rc).Value
ws.Range("AE" & rc).Value = Application.InputBox
(Prompt:="What is the Heading of Data set #" & c & " Table " & tc & " This entry may repeat", Type:=8)

[Code] .........

Macro 2

Code:
'A lot of code here but I want to be able to set multiple ranges to the ranges list in the cell values.
rng(2) = sh(0).Range("AG2").Value

## Find If Number Is A Match

Mar 19, 2013

Trying to find if the number entered in the input box "num_cmp" is => Cells(i, 2) AND

## Find And Replace Part Of Cell

May 20, 2009

As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.