ExcelBanter - View Single Post - Desperate -Lookup function needed!

David

in article , Krefty at
wrote on 30/12/2003 9:23 PM:

Good Evening and thanks in ADVANCE for looking at my
problem. Please send me or reply with any code/equation
idea that makes sense

I have a workbook that contains 6 worksheets.

1=RegQList
2=289
3=Fisher
4=Grove
5=Sprauge
6=Mooney
(Exactly as typed)

I have in the RegQList hundreds and hundreds ,I think
2700 specific and unique Regulators that I need, no must
calculate, various equations for. What I have figured
out is that if I was smart enough to create a LOOKUP or
smart eqations to find
MAKE- Cell J2/MODEL-Cell K2/SIZE-Cell L2/ORIFACE-Cell M2

on each line in the RegQList then if you could help me
create the code to look at the 5 other worksheets find
the matching MAKE-Cell J2, then within that 1 of 5
sheets match-up MODEL/SIZE/ORIFACE (which on the 5 sheets
is always column A-MAKE/B-MODEL/C-SIZE/D-ORIFACE when
that match is located/solved I want the number known as
Cg which is already in COLUMN E of the same Cell rows as
the matching specific make regulator.

I would like that number returned, or so I wish to place
this lookup equation to place the Cg for each specific
Regulator in AA2 of RegQList. Then with that Cg factor I
can return to my basic Excel skills and create the needed
equation of (1.29) X ( SUM H2+14.7) X (SUM AA2) this
equals Q or Flow. The 1.29 and 14.7 are constant numbers
to calculate Flow.

I am going to play with this all night and morning. SO
if you think that you can help me figure this out I would
appreciate it so much I don't think you can even begin to
understand. This is something that I was given months
ago and I think was under the impression that the need
for the data was disregarded and found out today that is
needed tommorow.

For something quick and dirty and since all the information you need is
in each row, why not put the equation in the same row?
Assuming your first row is the header row put the following formula
"=1.29*($H$2+14.7)*E2" in cell 2 of the first available column.
(I would insert a column in front of Column F if it wouldn't screw
something else up. I am also assuming H2 is a constant and if that is true
and you do insert a column, your constant will move to I2. So in the formula
change the H to I If H2 is not a constant but a number unique to each
regulator, then remove the $ signs)
Then copy down the formula for the entire length of your database.
Now select the headings for your database and from the Data Menu select
Filter-Autofilter.
You will end up with buttons on the header row which will allow you to
select your regulators with what ever criteria you want to use.
Hope this helps
--

Remove nospam

Dave