Find value in one column and return value from same row but different column in another tab

[Vickie Hartley]

I work for a non-profit community housing agency and created a workbook that has multiple tabs. One of the tabs is a spreadsheet that contains 22 columns (scope assumptions). On another tab I want to pull data from this large spreadsheet. There are usually at least a 100 rows, each room in a house has multiple lines and tasks with sub-totals by room. The spreadsheets primary purpose is to communicate to the construction crew what needs to be done in each room and what the budget is. Here is an example of the primary spreadsheet.

Location	Task	Description	Labor Hours	Labor Costs	Materials	Subs	OH,G&A, Profit	Contract Amount
Front Porch	Demolition	Demo garbage, existing windows, steps	3	90	50		60	200
Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light				165	71	236
	Sub-Total			90	50	165	131	436
Living Room	Electrical	Furnish and install new light switch and cover plate				50	21	71
Living Room	Doors & Windows	Furnish and install new trim and windows (3)	8	300	300		257	857
	Sub-Total		8	300	300	50	278	928
*******	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge				500	214	714
*******	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install				1050	450	1500
	Sub-Total		0	0	0	1550	664	2214

<tbody>
</tbody>

A second, third and fourth spreadsheet is needed to communicate to the sub-contractors what needs to be done. One spreadsheet each for Electrical, Plumbing, and HVAC. Ideally I want to create formulas that will look for every occurrence of a given task (i.e. "Electrical") in column B from the primary spreadsheet and return the corresponding location, description, and cost for each occurrence. For example:

Electrical Scope

Location	Description	Budget
Front Porch	Properly install wire mold at outlet close to ceiling and exterior light	236
Living Room	Furnish and install new light switch and cover plate	71
*******	Furnish and install new 240V outlet for range and 120V outlet for fridge	714

<tbody>
</tbody>

Can you please give me some guidance and/or answers?

I often use Mr. Excel to solve my Excel problems by looking for similar problems or using the books I have purchased but this time, I could not find a solution so I posted my first request for help. I have been using this site for about 3 years.

I have tried a multitude of nested formulas (search, if, lookup, index, etc) to get this to work.

 

[tonyyy]

Let's try this...

In the line:

ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

Replace "LastCol" with the number of columns in the "Original scope assumptions" worksheet. So if the last column is letter I (as in Idea), then the number would be 9.

 

[Vickie Hartley]

Same error code - on same line. The line now reads:
ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, 9)).AutoFilter

Here is the rest of the code:
For i = 1 To 3
If i = 1 Then Subcontractor = "Electrical"
If i = 2 Then Subcontractor = "Plumbing"
If i = 3 Then Subcontractor = "HVAC"

Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=2, Criteria1:=Subcontractor
Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Subcontractor
ActiveSheet.Paste
For j = LastCol To 1 Step -1
If Cells(1, j).Value <> "Location" And Cells(1, j).Value <> "Description" And Cells(1, j).Value <> "Contract Amount" And Cells(1, j).Value <> "Budget" Then Cells(1, j).EntireColumn.Delete
If Cells(1, j).Value = "Contract Amount" Then Cells(1, j).Value = "Budget"
Next j
Cells.Columns.AutoFit

Sheets("Original scope assumptions").Activate
Application.CutCopyMode = False
Next i


ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub


You have been so patient and helpful.

 

[tonyyy]

Patient? Maybe... but so far not so helpful.

When you state "Here is the rest of the code:" - it was just an oversight when you didn't include the start of the code right? I mean, just above the offending line should be:

Sheets("Original scope assumptions").Activate
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
[COLOR=#808080]ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter[/COLOR]

And in particular, there should be the line: Sheets("Original scope assumptions").Activate. Not having this line will produce a Run-time error '1004':

I think we've eliminated the LastCol as the error source; you can switch back to the original line:
ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

 

[Vickie Hartley]

Right, I did not include the whole code - just the portion after the offending line. Here is the whole code:

Sub CreateSubcontractorSheets()


Dim LastCol As Long
Dim LastRow As Long
Dim Subcontractor As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet


Application.DisplayAlerts = False
Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Electrical" Or ws.Name = "Plumbing" Or ws.Name = "HVAC" Then ws.Delete
Next


Sheets("Original scope assumptions").Activate
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter


For i = 1 To 3
If i = 1 Then Subcontractor = "Electrical"
If i = 2 Then Subcontractor = "Plumbing"
If i = 3 Then Subcontractor = "HVAC"

Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=2, Criteria1:=Subcontractor
Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Subcontractor
ActiveSheet.Paste
For j = LastCol To 1 Step -1
If Cells(1, j).Value <> "Location" And Cells(1, j).Value <> "Description" And Cells(1, j).Value <> "Contract Amount" And Cells(1, j).Value <> "Budget" Then Cells(1, j).EntireColumn.Delete
If Cells(1, j).Value = "Contract Amount" Then Cells(1, j).Value = "Budget"
Next j
Cells.Columns.AutoFit

Sheets("Original scope assumptions").Activate
Application.CutCopyMode = False
Next i


ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[tonyyy]

Okay, here's the next thing we'll try...

Replace this line: Sheets("Original scope assumptions").Activate

with

ThisWorkbook.Sheets("Original scope assumptions").Activate

 

[strooman]

Maybe this can help.

I'm not exactly sure about your layout. So you have one workbook with tabs. Something like this?



Let's suppose below is your Table on the sheet named Primary. (I put some additional data in it for better recognition).
Make sure that the names you use are consistent. Otherwise you get problems.

Select the whole table and hit Ctrl+Shift+F3. In the "Create names from selection" dialog only check "Top Row". Click OK. You have now created some names and they correspond with the names in your headers. (in the screenshot marked Blue.

Row\Col	A​	B ​	C​	D​	E​	F​	G ​	H​	I​
1​	Location	Task	Description	Labour_Hours	Labor_Costs	Materials	Subs	OH_G_A__Profit	Contract_Amount
2​	Front Porch	Demolition	Demo garbage, existing windows, steps	3​	€ 8,53​	€ 1,51​	€ 116,13​	€ 60,00​	€ 186,17​
3 ​	Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light	5​	€ 0,93​	€ 351,53​	€ 51,87​	€ 71,00​	€ 475,33​
4 ​		Sub-Total		7​	€ 15,67​	€ 96,43​	€ 4,99​	€ 131,00​	€ 248,09​
5 ​	Living Room	Electrical	Furnish and install new light switch and cover plate	8​	€ 7,98​	€ 31,43​	€ 38,06​	€ 21,00​	€ 98,47​
6 ​	Living Room	Doors & Windows	Furnish and install new trim and windows (1)	3​	€ 30,09​	€ 23,72​	€ 76,10​	€ 257,00​	€ 386,91​
7 ​		Sub-Total		8​	€ 63,20​	€ 504,62​	€ 287,15​	€ 540,00​	€ 1.394,97​
8 ​	BedRoom	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge	7​	€ 29,59​	€ 170,88​	€ 54,42​	€ 214,00​	€ 468,89​
9 ​	BedRoom	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install	4​	€ 52,52​	€ 59,25​	€ 69,53​	€ 450,00​	€ 631,30​
10 ​		Sub-Total		0​	€ 82,11​	€ 230,13​	€ 123,95​	€ 664,00​	€ 1.100,19​
11 ​	Front Porch	Demolition	Demo garbage, existing windows, steps	3​	€ 242,95​	€ 4,98​	€ 63,54​	€ 60,00​	€ 371,47​
12 ​	Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light	5​	€ 46,00​	€ 210,80​	€ 79,25​	€ 71,00​	€ 407,05​
13 ​		Sub-Total		3​	€ 4,27​	€ 15,80​	€ 36,68​	€ 116,13​	€ 172,88​
14 ​	Living Room	Electrical	Furnish and install new light switch and cover plate	7​	€ 38,51​	€ 13,42​	€ 47,38​	€ 51,87​	€ 151,18​
15 ​	Living Room	Doors & Windows	Furnish and install new trim and windows (2)	8​	€ 211,22​	€ 26,61​	€ 137,44​	€ 4,99​	€ 380,26​
16 ​		Sub-Total		8​	€ 193,37​	€ 4,03​	€ 23,79​	€ 38,06​	€ 259,25​
17 ​	BedRoom	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge	8​	€ 37,97​	€ 30,34​	€ 100,60​	€ 76,10​	€ 245,01​
18 ​	BedRoom	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install	9​	€ 8,53​	€ 47,22​	€ 42,70​	€ 49,19​	€ 147,64​
19 ​		Sub-Total		0​	€ 782,82​	€ 353,20​	€ 531,38​	€ 467,34​	€ 2.134,74​
20 ​	Front Porch	Demolition	Demo garbage, existing windows, steps	3​	€ 15,67​	€ 1,27​	€ 351,53​	€ 69,53​	€ 438,00​
21 ​	Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light	9​	€ 7,98​	€ 4,41​	€ 96,43​	€ 29,78​	€ 138,60​
22​		Sub-Total		8​	€ 30,09​	€ 93,25​	€ 31,43​	€ 63,54​	€ 218,31​
23​	Living Room	Electrical	Furnish and install new light switch and cover plate	7​	€ 65,00​	€ 27,20​	€ 23,72​	€ 79,25​	€ 195,17​
24​	Living Room	Doors & Windows	Furnish and install new trim and windows (3)	6​	€ 29,59​	€ 1,36​	€ 110,11​	€ 36,68​	€ 177,74​
25​		Sub-Total		5​	€ 148,33​	€ 127,49​	€ 613,22​	€ 278,78​	€ 1.167,82​
26​	BedRoom	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge	4​	€ 123,83​	€ 63,77​	€ 59,25​	€ 137,44​	€ 384,29​
27 ​	BedRoom	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install	3​	€ 242,95​	€ 133,00​	€ 367,00​	€ 23,79​	€ 766,74​
28 ​		Sub-Total		0​	€ 609,70​	€ 352,82​	€ 1.173,30​	€ 555,94​	€ 2.691,76​
29 ​	Front Porch	Demolition	Demo garbage, existing windows, steps	3​	€ 4,27​	€ 44,15​	€ 210,80​	€ 42,70​	€ 301,92​
30 ​	Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light	11​	€ 38,51​	€ 1,66​	€ 15,80​	€ 1,51​	€ 57,48​
31 ​		Sub-Total		12​	€ 1.019,26​	€ 595,40​	€ 1.826,15​	€ 761,38​	€ 4.202,19​
32 ​	Living Room	Electrical	Furnish and install new light switch and cover plate	13​	€ 193,37​	€ 51,87​	€ 26,61​	€ 96,43​	€ 368,28​
33 ​	Living Room	Doors & Windows	Furnish and install new trim and windows (4)	8​	€ 37,97​	€ 4,99​	€ 4,03​	€ 31,43​	€ 78,42​
34 ​		Sub-Total		8​	€ 1.293,38​	€ 698,07​	€ 2.083,39​	€ 933,45​	€ 5.008,29​
35 ​	BedRoom	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge	23​	€ 0,21​	€ 76,10​	€ 47,22​	€ 110,11​	€ 233,64​
36​	BedRoom	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install	45​	€ 74,60​	€ 49,19​	€ 26,31​	€ 170,88​	€ 320,98​
37​		Sub-Total		0​	€ 1.599,53​	€ 880,22​	€ 2.187,56​	€ 1.342,30​	€ 6.009,61 ​

<tbody>
</tbody>

Now have a look at the screenshot.



In Column K put the names of the tasks. They must be exactly the same as they appear in the task Column B. In M2 make a validated list. Go to:
Data | Data validation | Data validation | Allow > List | Source =$K$2:$K$6

Formula in N2:
=COUNTIF(Task;M2)

On every tab, Electric, Plumbing, HVAC, Doors & Windows etc put something like this:


*** Then in A2 the formula:
=IF(Primary!$M$2="Demolition";IF(ROWS(A$2:A2)<=Primary!$N$2;INDEX(INDIRECT(A$1);SMALL(IF(Task=Primary!$M$2;ROW(Task)-ROW(Primary!$B$2)+1);ROWS(A$2:A2)));"");"")

Which must be entered with Ctrl+Shift+Enter (Excel puts Curly Brackets { } around the formula)
Drag the formula three cells to the right and then down, let's say 50 rows.

Repeat the above (from ***) for every sheet Electric, Plumbing, HVAC, Doors & Windows etc.

The last thing you must do is change the word "Demolition" on every sheet to a name which is on the validated list you made earlier. Remember that these names must be exactly the same as in the validated list AND in column B of your sheet named Primary.

When you choose a task from the validated list, go to the appropiate sheet and there youw ill find the data you want.

P.S. in the formula replace semicolon with comma.

 

[Vickie Hartley]

Tried - same error code.

 

[Vickie Hartley]

Your set-up is similar. FYI --- The task column in my table is a list. I copied and pasted the formula, made changes to cell references and changed semi-colons to commas. I got a 'value' error. 'Encountered existing arrows or a circular reference'. Most of the formula has locked cells and I can't tell where the curly brackets so I have not had any luck determining what is driving the error.

Here is my formula.
=IF('Original scope assumptions'!$AE$37="Electrical",IF(ROWS(A$2:A2)<='Original scope assumptions'!$AF$37,INDEX(INDIRECT(A$1),SMALL(IF(Task='Original scope assumptions'!$AE$37,ROW(Task)-ROW('Original scope assumptions'!$B$37)+1),ROWS(A$2:A2))),""),"")

 

[Vickie Hartley]

tried this with no luck -- the same error code when running the macro

 

[tonyyy]

Hi Vickie...

Sorry for all the trouble.

Question: When you originally copied/pasted the code into the Visual Basic Editor, did you paste it into a sheet module or a standard module? The code should go into a standard module. (From the Insert menu, choose Module.)

If that isn't it, my next step is to request (if possible) that you post the spreadsheet to a virtual drive (eg, dropbox, onedrive) so I can download and examine it; or if you prefer I can private message you with my email address.