Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup conundrum
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup conundrum
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 and I will forward you the file. HTH Mark. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup conundrum
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup conundrum
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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vertical lookup conundrum
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vertical lookup of pictures | Excel Worksheet Functions | |||
Conditional Vertical Lookup | Excel Worksheet Functions | |||
Vertical Lookup Question | Excel Worksheet Functions | |||
Vertical Lookup from a List | Excel Worksheet Functions | |||
Vertical lookup help needed | Excel Worksheet Functions |