Vertical lookup conundrum

[Andy G]

I have

Numerous tank tables (tanks containing liquids) with columns, Sounding,
Volume, Centre of gravity, free surface etc

One summary table, to sum the contents from said tanks, with the same
columns as the individual tank tables.

Up to now I have been entering the sounding (column 1) in the summary table
and it has retrieved the data for that sounding from the relevant tank table
using vertical lookup. I would like to enter either the sounding (column 1)
OR the volume (column 2) in the summary table and for excel to extract the
relevant values from the tank table for that sounding/volume. Is it possible
if I have entered the sounding in column one that excel retrieves the data in
all columns including the volume in column two OR if I enter the volume in
column two it retrieves the data in all columns including the sounding in
column one?

Thanks

A B C D E F
1 Tank 25 Sounding Volume VCG LCG TCG
2 1.00 140.00 0.50 52.50 20.00
3 1.50 219.00 0.75 56.88 22.50
4 2.00 298.00 1.00 61.25 25.00
5 2.50 377.00 1.25 65.63 27.50
6 3.00 456.00 1.50 70.00 30.00
7
8 Tank 26 Sounding Volume VCG LCG TCG
9 1.00 140.00 0.50 52.50 20.00
10 1.50 219.00 0.75 56.88 22.50
11 2.00 298.00 1.00 61.25 25.00
12 2.50 377.00 1.25 65.63 27.50
13 3.00 456.00 1.50 70.00 30.00
14
15 Tank 27 Sounding Volume VCG LCG TCG
16 1.00 140.00 0.50 52.50 20.00
17 1.50 219.00 0.75 56.88 22.50
18 2.00 298.00 1.00 61.25 25.00
19 2.50 377.00 1.25 65.63 27.50
20 3.00 456.00 1.50 70.00 30.00
21
22 Summary Sounding Volume VCG LCG TCG
23 Tank 25 2.50 377.00 1.25 65.63 27.50
24 Tank 26 2.00 298.00 1.00 61.25 25.00
25 Tank 27 3.00 456.00 1.50 70.00 30.00
26 1131.00

 

[NoodNutt]

G'day Andy

I think a Lookup array will be what your looking for, as it appears in your
example that each tank has the same values throughout.

I have created an example worksheet for you to look at and play with.

It is normal practice within the NG to post responses, and never attach
files.

This in mind, you or anyone who has a similar requirement can send an email
to (e-mail address removed) and I will forward you the file.

HTH
Mark.

 

[NoodNutt]

Also Andy

If you need help on expanding the array & the ListBoxes just drop me a line.

More than happy to assist where I can.

Regards
Mark.

 

[Andy G]

Hi Mark

Thanks for your assistance but my example is unfortunately misleading as
there are over 100 tanks of which only a couple would be of similar
dimensions, they are also not symmetrical with all shapes and sizes.

Regards
Andy

 

[NoodNutt]

NP Andy

you can still use an arraywith a lookup to insert the values automatically
for you.

Setting up the array values will obviously take a little time, but once in
place will save you allot of time.

Drop me a line, I will send the example to you, have a look at it, if it is
what you are looking for then all good.

Or if you can provide more details as to what you ultimately want to
acheive, we can work it through.

Regards
Mark.