I've got data on sheets 2-8 of a workbook. This data is pricing for vendors
in different counties I do business in. My data is in columns A through P
and all the data is the same on each sheet (i.e. vendor name in column A,
phone number in column B, 4 tier pricing structure in columns C through F, 3
tier shipping charges in columns G through I and so on. The only thing that
changes from sheet to sheet is the vendors and their pricing.
On sheet1 I input the county I need to use, the number of parts needed, the
delivery time needed and the amount I want to pay for the product. I then
get a listing of vendors I can order from along with their price. The
workbooks works perfectly if I want to sort the information by price, but if
I want to sort it by vendor, I get blank rows for each vendor whose price is
not within the threshold.
My threshold is compared to the sum of the column with the correct number of
parts to the column with the necessary shipping speed (i.e. overnight, 2nd
day, standard ground, etc.). In cells C3 and C5 on sheet1 are the letters
of the columns that will be added to compare to the threshold. They take
the column with the correct number of parts needed along with the column
with the correct shipping methods

Here is the IF statement that pulls my data from sheets 2-8:

=IF(INDIRECT("'"&$B$2&"'!"&$C$3&ROW(A2))+INDIRECT( "'"&$B$2&"'!"&$C$5&ROW(A2))<=$B$4,(INDIRECT($B$2&" !A"&ROW(A2))),"")

Where "'"&$B$2&"'!"&$C$3&ROW(A2) pulls the sheet to be used and the column
of correct number of parts. And +INDIRECT("'"&$B$2&"'!"&$C$5&ROW(A2) adds
the column with the correct shipping speed to compare to the selected
threshold (the price and delivery time to the end user). The selected
threshold is obviously in $B$4 of sheet1.

I sort the data by price, but then I would like to sort it by vendor to find
out if certain vendors can be used. I need to take my data that is sorted
by price add the two columns from the IF above and sort A1:P?? (where "??"
is the last row that the two columns called in C3 & C5 are less than the
threshold).

Any helpers???

I've proof read this explanation twice, but doubt it's as clear as a bell,
so, if you need any clarifying don't hesitate to ask.

Thanks in advance,
--
Gary "just when I thought I was done with this workbook..." Beard