Forum Discussion
Spreadsheet Help with Formulas Across Several Columns
One possible solution:
=INDEX('1C'!A:D,LOOKUP(B7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B5,'1C'!1:1,0))
Wouldn't it be enough to change D5 from "6 Foot" to "3 Foot"?
Detlef_Lewin wrote:Wouldn't it be enough to change D5 from "6 Foot" to "3 Foot"?
Yes but only when B9 and D9 are equal to each other and B5 is already 3 Foot. The reason I'm asking for B5 also is because it could be 6 Foot or Even sometimes also.
I do apologize for the information overload so the following sentence is what I need your original formula modified to include, it's as concise as I can get it.
If B5 and D5 are not equal to each other and B9 and D9 are equal to each other then make B5 and D5 equal 3 Foot. (This is so F9 and G9 will equal zero because no fuel was transferred from Tank 3C due to B9 and D9 are equal to each other.)
I've added your original formulas to the referenced cells below. They work perfect as asked for when B9 and D9 are not equal to each other.
Cell C9 Formula
=INDEX('3C'!A:D,LOOKUP(B9,'3C'!A2:A342,ROW('3C'!C2:C342)),MATCH(B5,'3C'!1:1,0))
Cell E9 Formula
=INDEX('3C'!A:D,LOOKUP(D9,'3C'!A2:A342,ROW('3C'!C2:C342)),MATCH(D5,'3C'!1:1,0))
The following is a description of Trim for this class ship to help give you a picture of what's physically happening onboard that this spreadsheet represents.
"This class ship has three Trim conditions: Even, 3 Foot, and 6 Foot. Normally we're at 3 Foot Trim most of the time which means the bow is three feet higher in the water than Even Trim or six feet higher in the water at 6 Foot. When we issue a lot of fuel we can start out at 3 Foot Trim and end at 6 Foot trim because the fuel tanks are in the forward part of the ship and are now empty and the ballast tanks can't compensate enough to bring us back down to 3 Foot Trim. For Even Trim (same as before we may start out at 3 Foot or 6 Foot, depending on how full the tanks are) we have loaded a lot of fuel that has caused the Trim to fall below the 3 Foot or 6 Foot Trim because the ballast tanks can't compensate enough to bring us up to 3 Foot Trim."
I hope I have cleared up the confusion and do apologize again for the earlier information overload. I just wish I knew how to write the formula additions I have in bold to the formulas you've already provided; I've tried my best to simplify it to it's basic elements to what I need and added clarification for these unique scenarios.
I hope I haven't frustrated you with this, if so, then I do apologize, it wasn't my intent, only my lack of conveying my request to you in a clear and concise manner.
Please let me know if you need any further information, it's not a problem.
I do appreciate everything you've done for me,
Hello Detlef,
I’m in need of your help again; I’m pretty sure this is a much easier problem to fix since it’s only one problem and not the entire workbook.
I need help with the following formula:
=INDEX('1C'!A:D,LOOKUP(D7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B7,'1C'!1:1,0))
I modified the spreadsheet recently and now I need the LOOKUP portion of this formula to include D7 and E7. Originally D7 was feet and inches in a 2-decimal format but now feet and inches are in separate cells (D7 – feet and E7 – inches) in a regular number 0-decimal format.
This formula is used 20 times in this spreadsheet but if you can fix it for the one cell then I can edit the others myself.
I’m attaching both the current in-use workbook (8039 Decimal Format - Current) and the new in-progress workbook (8039 Feet & Inches - In Progress) for you to easily reference.
In the current in-use workbook all formulas and tabs are working as designed and you may notice a few changes in appearance since you last helped me last May.
In the new in-progress workbook all the cells and tabs have been updated to accommodate the new formula and is currently showing #REF! error codes due to on tab 1C, 2C, 3C, 3P, and 3S I changed the replaced the Ullage column with Ullage Ft and Ullage In columns.
If you have any questions, please don’t hesitate to ask.
Best Regards,
John
Hello Detlef,
I'm in need of your help again. This time I only have one problem and not the entire workbook. Attached are the updated workbooks you helped me with last May. The one named 8039 Feet & Inches is the one I need updating and the other one is for comparison.
I need help with the following formula:
=INDEX('1C'!A:D,LOOKUP(E7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B7,'1C'!1:1,0))
I need to be able to enter numbers in D7 and E7 and it will go to tab 1C and come up with the correct answer to put in F7 (based off the trim the MATCH portion of this formula provides. In this case the correct answer for 41 feet (in D7) and 2 inches (in E7) is 5,053 gallons in F7 and 3 Feet Beginning & Ending Trim.
If you can solve this one problem, I can edit the rest of the spreadsheet for the other tanks.
Thank you very much for your time with this.
Best Regards,
John
Detlef_Lewin wrote:My idea is not the change a formula but use a new formula in F5.
=IF(AND(OR(B9=D9,B10=D10),B5<>D5),"Change D5 to '3 Foot'!","")
So the user in front of the sheet gets the instruction to change the value in D5.
And everything is balanced.
Unfortunately this wouldn't work because it would change the values in the other tanks as well, but we're getting there.
Instead of using your formula, I decided to add three more Trim Groups which accomplishes exactly what I needed. I would've preferred to use just the two Trim cells but it looks like it's beyond Excel's design parameters.
I appreciate everything you've done for me, your help has been extremely invaluable, and the last formula you gave me provided the insight I needed to do to get this to work. If in the future you find what I'm looking for, please let me know, it would be greatly appreciated.
As before, I still think your employer doesn't pay you enough because what you've done for me, you're worth you weight in gold a thousand-fold and then some. If there was a way I could rate your help here, I would give you 10 out of 5 stars because you've gone well far and beyond what I could have ever expected with this ordeal.
Take care my friend,
John
Revised Gauge Tab
My idea is not the change a formula but use a new formula in F5.
=IF(AND(OR(B9=D9,B10=D10),B5<>D5),"Change D5 to '3 Foot'!","")
So the user in front of the sheet gets the instruction to change the value in D5.
And everything is balanced.