Calculate the volume based on the number of the tank

I need a formula to calculate the volume in liters based on the number of the tank number in another sheet

The volume calculated by looking for the value in column(cm) at the tank sheet and returning the value in another column (liters), and then also add/sum the millimeters value in another column (liters to the total of liters as total liters

Please find the attached example

Originally Posted by JohnTopley

Should the centimetres be 302 not 320 ?

And place the data in two columns (on each sheet) not spread over 12.

SORRY, Yes its 302 ,
I have to spread over 12 or more , i have more than 40 tanks in this layout
Thanks for your reply

Originally Posted by MrShorty

I'm not following your example very well, unless there is a typo in D12 and D12 should be 302. If D12 should be 302, then it appears that you want to lookup the volume that corresponds to tank number, cm number, and mm number, and add the two volumes together to get the final result.

My first thought in this -- are you required to store the data for each tank like you are currently doing it -- a separate tab for each tank and the cm-volume and mm-volume tables spread out over multiple columns? IMO, this lookup function will be a lot easier if you can combine the data for all three tanks into a single table, but I want to know if that would be allowed or not before proceeding.

SORRY , Yes its 302 ,

I have to spread over in this layout , function SUMPRODUCT works fine for this layout .
i need to know how to call the tank number from multiple tanks/sheets .
Thanks for your reply

Originally Posted by JohnTopley

Try

Formula:

Please Login or Register  to view this content.

IMHO you would be better adopting the layout in columns O:Q in "Tank 1" with all tanks on one sheet

JohnTopley YOU ARE THE MAN
thats what i mean , many thanks for your time and effort

Originally Posted by JohnTopley

Example of suggested layout

Formula:

Please Login or Register  to view this content.

OKAY but how about the mm(millimeters)!
PLZ find attached the original tank table layout, because now I have trouble with adding multiple tanks of the same layout .

your advice or suggestions will be much appreciated

Originally Posted by JohnTopley

With your last post (volumes much greater than sample post - i.e 1500 rows per tank) then l I would still organise as I suggested but if that is not to you liking then use the original SUMPRODUCT formula. But be aware that using INDIRECT can result in slow processing.

For millimetres you could create a seperate sheet with data per tank: if data is common to all tanks you could a table to the "cm" sheet.

Thank you for your reply .
is there any way or formula to change the layout into the suggested layout (two-column only ) instead of doing it manually .
Thanks for your help

Originally Posted by JohnTopley

Please Login or Register  to view this content.

Reformats the "Cm" data

"Tank !!B" ia copy of "Tank !!A": i was unable to change any data as the sheet is password-protected.

THATS GREAT ...many thanks for your effort and help
what I understand is that I should put all tanks in one single sheet ! Right !!!
and I will change the range of the cells in the vb code based on the tank format.

but one last question how about the mm ! should i put all tanks mm in one single sheet in the same way in cm(summary page)?

Originally Posted by meonly123

THATS GREAT ...many thanks for your effort and help
what I understand is that I should put all tanks in one single sheet ! Right !!!
and I will change the range of the cells in the vb code based on the tank format.

but one last question how about the mm ! should i put all tanks mm in one single sheet in the same way in cm(summary page)?

mm data is NOT common to all tanks

your assistance on the matter will be greatly appreciated

Originally Posted by JohnTopley

Please Login or Register  to view this content.

Macros are "REformat_CM" and "Reformat_MM" with output in Shhts "CM" and "MM"

that's wonderful
I appreciate your help so much.