Look Up Value And Return All Related Values From Other Cells

Jan 8, 2009

I have two worksheets. One contains my master data and the other my look up table.

Master Data: ....

View 9 Replies


If Value Is Included In A Range Between 2 Cells Return Value Related From Another Range

Jan 8, 2014

I would like to use a excel formula to resolve this problem.

Column C is the anwser I pretend.

In the same cell it will return the name of the correct range.

Ex: Between 200 and 300 there are numbers involved that are included in Range 1 and Range 2 (see second table)


Result: (How to return this value?)

Range1, Range2



Table to Check Value to Return:

Range 1

Range 2

Range 3

View 3 Replies View Related

Return Related Information From Another Workbook

Nov 4, 2009

we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.

My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................

View 3 Replies View Related

Filtered Listbox Selection To Return Value To Related Cell?

Apr 30, 2014

See the attached : ToolMatrix4.xlsm

Combobox2 filters listbox2 which is pulled from sheet2. How do I get the X to return the value to the relevant cell on sheet 2, ie create a relevant listindex for the listbox

Also, one other minor thing, why selecting the last record in the listbox doesnt write to the worksheet?

View 4 Replies View Related

Return Multiple Instances Of Data Related To Number

Nov 26, 2008

The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.

The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied

Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search

Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String

Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............

View 4 Replies View Related

How To Group Values Related To Each Minute

Apr 16, 2014

I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,

i want to show 10 which is sum of first four values.

Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1

View 4 Replies View Related

Combobox To Show Only Values Related To Row

Feb 24, 2007

I have an inventory sheet that uses multiple userform comboboxes to input data to a sheet. Everything works great except on one userform "pullmat" where the user removes inventory from a unique list of all material available in "Master Log" sheet. The combobox that im trying to fix is "combobox3" in the "pullmat" userform. I'm trying to get that combobox to only show P.O. numbers that are related to the material selected in the "Material Code / Name- combobox2. In other words, the user should only be able to select a P.O.# that matches that specific material on the Master Log sheet.

The file size is too large to attach but I can email a copy if needed.

View 3 Replies View Related

Find Value And Copy Related Values

May 21, 2008

I am building a Macro which can be found underneath. The red code is not working right now and I am looking for alternatives to solve this error but until now I haven't found none.

Basically, I am looking for a correct code to copy files from a sheet to another sheet with a find macro.

Sub vinden()
Dim FindString As String
Dim Rng As Range
FindString = Range("A21")
If Trim(FindString) <> "" Then
With Sheets("Voorraadverloop").Range("A1:IV65536")

View 9 Replies View Related

Put Message Box Related To A Cell If Threshold Values Reached

Mar 25, 2012

I am trying to put a message box related to a cell if a threshold values is reached.

E.g.: Cell A: 85

If cell A values moves to 86 I am trying to put in a message that says something like " This value is not allowed". Is there a way to do this ?

View 8 Replies View Related

Find Related Data From Another Sheet & Copy Values From Left

Mar 30, 2008

Im going to try to make this as clear as possible. I cant use my actual data because it wouldnt make any sense to anyone so Ive made up an example problem. Here goes...

Lets say in Sheet 1 I have two descending columns of data. Column A is MODEL of Vehicle (Civic for example). Column B is vehicle identification number (xxx for example). Sheet two has 4 columns of data, but only one is really required for this example. Cell A1 is the MAKE of vehicle (Honda for example). Directly below that in Cell A2 is the MODEL of the vehicle (Civic). There are then a few rows of empty space until it gets to the next vehicle MAKE and MODEL.

So in Sheet1 there is a long list of MAKE's in ColumnA and VIN's in ColumnB. Sheet2 Has a long list of MAKE's and MODEL's in ColumnA and random data in other columns.

What I want to do is assemble a Macro to start in Sheet1-A2, read the MODEL then copy the corresponding Vehicle Identification Number in B2. I then want it to go to Sheet2-A2 and start searching downward until it comes across a matching MODEL. Once it finds the match I want it to step downward 2 cells and paste the Vehicle Identification Number. Then return to Sheet1-A3, and repeat the process until EOF.

View 9 Replies View Related

If Formula - Look At All Eight Cells Related To These Courses?

Jun 19, 2014

got a formula that checked C4, then D4 for dates (either in or our of date) and returns a count of 0 or 1, with some cells having tet in them also.

The formula I am currently using is below

=IF( OR(F4 ="enrolled",F4 = "O/S"),0, IF(AND(G4="",TODAY()-F4>730),0,IF(AND(TODAY()-F4>730,TODAY()<G4),0,1)) )

I have a range of four courses (all child protection) that should be attended, some are eleanring, some local authority courses and staff should attend at least one but up to all four of them. Although they all fall under the question are they compliant or not (i.e.0 or 1)i do not want to count them all seperately. Is there any way I can look at all eight cells related to these courses and assess the latest date to assess if they are complinat (in date) or non compliant (out of date)?

I have attached the spreadsheet which I hope will clarify things


View 8 Replies View Related

Highlighting Cells Related To A Cell

Jul 14, 2009

in this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.

how can i make the cells highlight that relate to the cell sum in group 3.

i.e when i click cell b38 cell b21 and b4 should highlight.

View 9 Replies View Related

Event Trigger And Format Related Cells

Sep 20, 2012

Is it possible to write an event trigger macro to format cells? Let's say I want to add a new information to the row which is after FinalRow. Can I force excel to detect the input and then format that new row as previous rows or something custom?

View 4 Replies View Related

Excel 2010 :: Disable Something Like Text Box Related To Cells?

Aug 4, 2014

I am using excel 2010. When I click on cells on one column, something like text box will pop up with information related to that column. How can I get rid of this text box. How to disable this text box which I think it has a link between the text box and cells on that column.

View 1 Replies View Related

Compare Cells Values And Return Value?

Nov 27, 2012

I'll quote the example straight away.

Col A Col B
A,R,S A,R,S,T and so on.

There is no fixed pattern for the values, but they are separated by commas only. All i need is to compare col A and col B and return me the value in Col C like this,

Col C (col C is to know the removed value from comparison)
F (for line 1)

Col D (to find the letters that are added from comparison)
T (for line 2)

View 7 Replies View Related

Check Values Of Cells & Return Value Accordingly

Sep 12, 2007

I am tryng to perform a logical statement using 3 fields, ex

A1>0,B1<0,C1<0 returns
A1>0,B1>0,C1<0 returns
A1>0,B1>0,C1>0 returns

end statement

View 9 Replies View Related

Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

Jan 15, 2014

Here's a simplified example:




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

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

View 7 Replies View Related

Return Value If It Falls Within Values In Range Of Cells?

Jun 12, 2014

I am trying to learn more about functions like IF(), AND() MATCH() and LOOKUP()... but so far I cant seem to get it right. I am actually trying to get a simple working formula that can take a value from the first tab and cross reference it with a table in the second tab to return another value in the first tab.

For example....

On the first tab/sheet named "Details", i have a list of people, an area of work they fall in and then a column named region that categorizes their areas of work into groups


Area of Work

Mr A

Mr B

Mr D

Mr E

Then on the next tab/sheet named "Category", I have a table which lists the areas that belong to a region;



A-J. 1-6

K-P, 7-15


I have tried formulas like IF(AND(B2=Category.B2),"1","ERROR") but it keeps returning me "ERROR" when it should return "1"

View 7 Replies View Related

VLOOKUP Values In 2 Different Cells And Return Other Columns?

Feb 26, 2013

Vlookup or Match for a product list project.

I need "my sheet" to populate (columns A,B,&C) with some source data; however, I need the lookup to reference both retailer# and color (columns D&E) and return other information such as description, color description, etc...

Here is the example of both sheets:

My Sheet:


View 1 Replies View Related

Formula/VBA To Return Multiple Values In Different Cells

Nov 7, 2008

I am trying to find a way to use information in one cell in order to look up corresponding multiple values on another sheet. The problem that I am running into is that VLOOKUP only returns the first value. I need the values to be calculated in different cells going vertically, not all combined in the same cell as was in an earlier post. I am not looking to sum anything so a pivot table also doesnt work.

Sample data:
A / B / C / D
Identifier / Pub ID / Invoice # / Job #
ABCD1234 / ABCD / 1234 / A41254
ABCD1234 / ABCD / 1234 / B41254
ABCD1234 / ABCD / 1234 / C41254
DCBA4321 / DCBA / 4321 / A56789


I am looking for it to do this:
A / B
Identifier / Job #
ABCD1234 / A41254
/ B41254

I want to enter ABCD1234 into another worksheet and have it return all of the job #'s, but I have a lot of these so I don't want to have to look up how many job numbers there are associated with it and have to enter different formulas into multiple cells. Filtering also isn't an option as there are simply too many cells to calculate.

View 11 Replies View Related

Return Number Of Cells With The Most Consecutive Specific Values?

Aug 13, 2014

I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.

View 8 Replies View Related

How To VLOOKUP A Value In A Another Sheet And Return Concatenated Values Of Other Cells

Nov 27, 2013

I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).

The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).

I'm not sure how I can do this using formula.

I've attached a example.

View 6 Replies View Related

ID First And Last Non Blank Values In Row And Return That Cells Column Header?

Mar 13, 2014

I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.

View 7 Replies View Related

How To Use VBA To Search Strings And Return Values Or Cells Nearby

May 20, 2009

Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem

View 11 Replies View Related

IF Statement: Look At Different Cells And Return Different Values If Ture Or False

Nov 6, 2009

I have an IF statement that I need to look at different cells and return different values if ture or false, depending on what is selected in cell J2

Cell D2
if J2 = "FMB/FPI" then retrun the value of cell Y7
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then return the value of cell Y7

Cell E2
if J2 = "FMB/FPI" then retrun the value of cell AC7
if J2 = "FPI/FPI" then then retrun the value of cell AC7
if J2 = "FMB/OTHER" then N/A

Cell F2
if J2 = "FMB/FPI" then N/A
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then then retrun the value of cell AC7

View 4 Replies View Related

Lookup Column Values On Another Sheet & Return Adjacent Cells

Jun 11, 2008

Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b.

We have two worksheets, sheet a and sheet b

compare sheet b, column c (email addresses) to sheet a, column x, (email addresses).
If they match, move sheet a, columns d, e, f, g, and h to sheet b.

View 3 Replies View Related

Lookup/Match Column Values On Another Worksheet & Return Adjacent Cells

Jul 1, 2008

I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.

View 5 Replies View Related

Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing

Jun 25, 2009

Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....

View 9 Replies View Related

How To Return Multiple Values In Multiple Cells If Argument Is TRUE In IF Functions

Jan 8, 2013

I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?

IF(logical_test, [value_if_true], [value_if_false])

value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)

How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...

View 3 Replies View Related

Lookup All Values In Ascending Order And Return All Corresponding Values.

Oct 23, 2008

I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.

View 3 Replies View Related

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