# Conditional Part Cell Lookup Across Multiple Worksheets

Feb 22, 2008

We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.

Current working formula:

=IF(C7="","",INDEX(ProjectSchedule!\$C\$3:\$C\$202,MATCH(C7 & "*",ProjectSchedule!\$B\$3:\$B\$202,0)))

What I'm trying to accomplish, but it not working is something like this:

=IF(C7="","",INDEX(ProjectSchedule!\$C\$3:\$C\$202&CustomSchedule!\$C\$3:\$C\$202,MATCH(C7 & "*",ProjectSchedule!\$B\$3:\$B\$202&CustomSchedule!\$B\$3:\$B\$202,0)))

## Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

## Lookup On Part Of Cell Value

Jun 21, 2006

I have a question regarding searching in cells for a value, and returning corresponding data. This is what my workbook looks like:

Sheet1, cell A1 contains value "D600"
Sheet1, cell A2 contains value "V-1234"
Sheet1, cell A3 contains value "DB23"

Sheet1, cell B1 empty
Sheet1, cell B2 empty
Sheet1, cell B3 empty
..........................
1. search each cell value form Sheet1 column A, in Sheet2 column B
2. when a match is found, return the corresponding value of column A from Sheet2....

## Display Part Of Worksheets Name In A Cell?

Nov 10, 2011

If I have a worksheet named "c01", can I write a function in a cell on that worksheet that will display the "01" part of the worksheet name?

## Lookup Value Across Multiple Worksheets

Apr 24, 2008

Would like to lookup values across multiple worksheets. I am able to do this for a single worksheet using OFFSET(Karthik!\$E\$1,MATCH(A2,Karthik!E2:E21,0),-3).My Work Sheet Names are Karthik & Ramesh. How can i reference these worksheets in the above formula?

## Conditional Format On Part Of A Cell

Apr 2, 2009

I've used Conditional format before and up to now never had a problem with it. I would like that if a cell ends with a number it turns (eg) yellow. So Hallo1 remains colourless, but in the case of Hallo2 the cell turns yellow. I have tried (in the active cell A20) Right(a20,1)>1 (with the setting hasFormula) and =Right(a20,1)>1, both without the desired result.

## Lookup Across Multiple Worksheets (summary Sheet)

Feb 2, 2005

I want to create a summary sheet that will lookup a particular cells value on
multiple sheets (averaging 58 sheets) in a workbook (e.g. \$J\$19) based upon a
cell next to it (\$I\$19) that will match the criteria on the summary sheet
(e.g. w1, w2, w3).

I have tried VLOOKAllSheets but when there are other similar workbooks open,
it doesn't work right.

## Lookup Multiple Worksheets (approx 80) In One Workbook And Sum

Apr 16, 2007

I am trying to find a formulae that will sit in a summary sheet and lookup all of the worksheets in a workbook ( approx 80) for a particular individual's name and calculate the total amount paid.

The spreadsheet lists cash amounts by person for each week of the year. the individuals change, as they come and go during the year but the format of the spreadsheet remains the same.

I have attached the summary sheet and a few of the additional sheets. My formulae have fallen apart as I have been adding each person in manually, but I hope it will still be clear.

## Lookup Value Across Multiple Worksheets And Retrieve Worksheet Name

Jun 26, 2007

I would like a formula to lookup a value across worksheets and if found return the worksheet name that coresponds to that value. I do have this formula below but it will not retrive the correct worksheet name if there are duplcates values.

=INDEX(WSLST,MATCH(TRUE, COUNTIF(INDIRECT("'"&WSLST&"'!B2:B9"),B2)>0,0))

So I tried to use the define names formula method:

Col_B   =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))
N       =8
S       =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST   =Sheet1!\$A\$2:\$A\$3
XWSLST  =T(OFFSET(WSLST,INT(S/N),0,1,1))

and modified the formula into this manner: =INDEX(WSLST,MATCH(TRUE,COUNTIF(Col_B,B2=S)>0,0),COUNTIF(B\$2:B2,B2))+1

I left a sample workbook below that in column E are the values that I'm expecting.

## Lookup Function/Formula Across Multiple Worksheets

May 14, 2008

This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.

=IF('2'!G3:G271=A3, LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)

## Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet

Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

## Conditional Formatting Based On Part Content Of A Cell

Sep 10, 2009

I am using excel 2000. I want to apply conditional formatting to a range of cells if they contain (V) as part of the cell contents. The cells will generally look like this : 09.00-6 (V) or 9-5.30 (V). I want to shade any cell if part of the contents of that cell is (V).

## Conditional Formatting For Multiple Worksheets?

Oct 23, 2013

I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?

## Conditional Summary From Multiple Worksheets

Feb 12, 2008

In the attached WB I'm looking for some formula (for cells A6:C6 in the brown table) to summaries cells A2, in the different sheets, as per a criteria, located in cells A5:C5 in the brown table.

The criterias (A, B, and C) should check cells A1 of each sheet.

I would also like to know how to handle sheets named A, B, C, D, E instead of Numbers in such formulas.

So far for my question.

In addition - I tried some " Array Formulas" in order to understand the concept of solving such a problem - and came up with some "weird" results as you will see in the attached WB. Any additional word will be superfluous...

## Lookup Specific Cells In Multiple Worksheets To Bring Into One Workbook

Aug 13, 2013

I have a workbook that's setup like a form, with 30+ worksheets in it (lets say this file is called "source.xlsx"). I have information in specific cells I want pulled out in each worksheet and brought in to a new workbook called "Destination.xlsx" and listed in a row.

## Lookup On Part Of Lookup Array

Feb 21, 2009

Assuming 1st row is a header row

Sheet1, Column A

1230000_XL07 - WB OPS
1230001_XL08 - WB OPS
1230002_XL09 - WB OPS

Sheet 2, Column A

1230000

How do I lookup 1230000 and return 1230000_XL -07 WB OPS in B2

## Lookup Part Text

Oct 12, 2007

I am not sure if a vlookup can do the following.

I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:

British Pound
Danish Krone
Japanese Yen

I have created another spreadsheet 2 columns that would have something like:
column A/columnB
British/Yes
Danish/No
Japananese/Yes

This 2nd spreadsheet can grow and have more lines.
In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.

## VBA To Lookup Part Number When Scanning A Barcode

Feb 14, 2014

I have a USB scanner connected to a PC. Data is entered into Excel when the part number barcode on a box is scanned. I have two worksheets that I will be scanning part numbers into, one is Group A and the other is Group B. On Group A's sheet there is a list of Group B part numbers that will be used for the lookup procedure when the barcode is scanned. The same is true for Group B's sheet. The groups should never be mixed, so if part number "B005" is scanned into Group A's sheet it should cause an error message to pop up. I've attached a workbook as an example. Is it possible to do this with VBA?

Barcode Scan Lookup.xlsx

## Lookup Process For My Address Part Of My Invoice

Apr 6, 2007

I'm trying to create a lookup process for my address part of my invoice, I want it so when I enter the first address it automatically looks up and enters the rest of the information (Title, Name etc.) I've been trying to do this with vlookup and I can't seem to get it to work. I'm just getting #Ref! returned.

My address 'database' (list of addresses etc) are on a seperate sheet to the Invoice.

## Conditional Formatting Of A Part Of Column

Dec 21, 2013

I'm trying to build a simple spreadsheet that allows me to auto highlight the day in the week and part of the column.

I include a sample sheet... above saturday there is the formula I use to determine the day of the week.

## Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String

[code]....

Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

## Lookup Part Of Text String In Userform ComboBox And Search For Value In Sheet Range

Feb 12, 2014

I have a worksheet with following values:

A
B
C
1
Shorts
75

[Code]...

Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".

What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.

Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.

I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.

## Calculating Median With Multiple Part Numbers That Have Multiple Unit Prices

Dec 27, 2012

I have a list of part numbers which repeat when there are multple prices they were sold at. For each unique part number I need one median price. The list if of about 500 parts but with the various different prices the file is 3700 rows. How can i do this quickly? For each unique part number I need one median price.

## Multiple Applications Combined Into One Part Number With A List Of Applications In One Cell

Nov 28, 2013

If the admins or you would like to change or recommend a change, I am trying to figure out what the code would look like for cells B22:B28. I need it to count the number of occurrences of the part number in A4:A17 and then combine the applications from the corresponding cells in column F (F4:F170 into a single cell (B22:B28).

On sheet 2 I would manually copy/paste the list of part #s from sheet 1 and remove duplicates. The add the formula that I can't figure out into column 2.

## Sum Same Cell Across Multiple Worksheets

Jul 27, 2007

I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.

For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11

Is there a faster way to do this?

## Sum Same Cell Across Multiple Worksheets

Jul 27, 2007

I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet. For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11

## Lookup Multiple Criteria In A Cell

Jun 3, 2014

I have a cell that contains a long string of text.

I want to be able to lookup in it to see if any word from a list is in it, if it is to return text dependant on which word is in that cell.

Seems like it should be easy but looking up the multiple values is making it difficult.

## Lookup Value In A Cell With Multiple Values And Return Value From Different Cell

Nov 19, 2013

At work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.

Example Sheet:
A
B
C
D
E

123

012

234

789, 567

465

789

890

012

I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.

## Copy Cell Value From Multiple Worksheets?

Jan 6, 2014

I have nearly 200 worksheet in a same workbook. Sheet 1 is the index sheet. Now I need to copy cell values from A1:C1 of all 200 sheets to A1:C1 , A2:C2 , A3:C3...A200:C200 of index sheet. How can I do it automate?

## Same Cell Address On Multiple Worksheets

Nov 26, 2009

I have a formula that sum' the same cell but on 24 worksheets in the same work book. Each sheet is exactly the same format, just different data.

If I wanted to create a macro that would select say cell ("A1") on sheet1 threw sheet12 how would it be written?

What currently have is a long formula, selecting each cell on each page. Can this be shortened up? Besides holding the shift key.