define range based on cell value in another column

[samshut]

Hi....I'm trying to define a range based on the value of another column. For instance in the simplified example below I want to search column A until it finds a cell containing say "1" (or whatever I specify) and then adds the cell corresponding in column B to the range. The range for Area 1 for the example below would include Tools 19, 28, and 13.

A B
Area 1 Tool 19
Area 1 Tool 28
Area 2 Tool 25
Area 4 Tool 35
Area 1 Tool 13
Area 3 Tool 41

Thanks

Sam

[redstang423]

Unless I'm misunderstanding you, this can be done with a formula. Try this in a cell:
=SUMIF(A1:A10,"=1",B1:B10)

This will look at the values in Column A and if they are equal to 1, it will sum the corresponding value in column B.

Jeff

[T-J]

The Find/FindNext and Union methods should do what you need.
rngBig in the code below is the range defined based on values in column B.
Just change strToFind and the search range as required.

Please Login or Register  to view this content.