Dont know how?

[Krefty]

Good day and thank you for looking at my problem. I know
that with the assistance of this newsgroup I can figure
out out a solution.

I am using Excel but have used Access if you think that
this might easier in Acess.

Here is what I need to do. I have set up 4-worksheets in
an Excel workbook, I desire the first work sheet to creat
a "flowsum" in Column A and to place a "title" in Column
B. All the needed data is from the other worksheets.
named "reglist", "Mooney Cap" and "equation".

In the "reglist" here is the column titles with related
data underneath each item.District Code Alternate Reference
MAOP In MAOP out Reg 1 Make Reg 1 Model
Reg 1 Size Reg 1 Pilot Reg 1 Spring Range
Reg 2 make reg 2 model Reg 2 Pilot
Reg 2 Size Reg 2 Spring Range RV Make RV
Model RV Size RV Pilot RV Spring Range Equipment
Location Equipment Number Equipment Status

I need the Column A of "flowsum" to be the sum of a lookup
of the correct equation for a Mooney(Brand) Device because
some devices are differant as found in "reglist" worksheet.

For this situation I have the equation ("flowsum" = 1.29
Cg P1)

I have listed each unique equation into the "equation"
worksheet. In the "Mooney Cap" worksheet is where the
equation can find Cg for this equation and P1 is a lookup
from the "reglist" worksheet in Column named Inlet MAOP.

I need to create an lookup function or equation that in
the "flowsum" Cell A1 gos and looks at the (Brand) of Row
1 of "reglist" then based on the brand of device it looks
at the "equation" worksheet for the correct equation
related to brand. Then that equation looks for Cg from
the "Mooney Cap" worksheet by brand, and then
the "reglist" for a specific device and this gives me back
a sum in the A1 cell of what that equation creates.

I hope that this is clear and I will check this newsgroup
regularly to provide any additional information if
needed. I have hundreds of different regulators, that
then need to referance 13-18 differant equations based on
brand that then between each brand has hundreds of flow
factors that based on size determine which Cg value gets
used.

Thanks in advance for reading this massive request.

Krefty

 

[BrianB]

I do not really understand fully what you are tryng to do, but here ar
a couple of ideas.

1. Using Excel : If you can, it would be better to put everything int
a single table. We all tend to start the wrong way round by havin
numerous sheets and try to summarise them, when putting everything int
a single table (despite the need for additional columns, and th
seeming duplication of data) is much more efficient, enabling us t
use the more powerful functions of Excel & Pivot Tables. I call thi
the "Database approach".

2. Access : The way you describe your data as consisting of variou
"lookup tables" does lend itself to manipulation by MS Access. It seem
you have the makings of a Relational Database where Access is able t
automatically display columns of data in a form that appears to be
single table, but, in fact, consists of the contents of several. Thi
requires tables to be matchable with unique lookup values in bot
tables that Access can use to make the link.

You can set up a "Make Table Query" that does, in fact, put everythin
into a single table. This can then be linked to Excel for summarising
I do not recommend Access for this latter purpose. It is not so goo
when you need to depart from the tabular output