Forum Discussion

John Breeden's avatar
John Breeden
Copper Contributor
May 21, 2017
Solved

Spreadsheet Help with Formulas Across Several Columns

In cell B5 it currently says 3 Foot.  In different scenarios it can also say 6 Foot or Even.  I need cell C7 to reference the appropriate column on tab 1C in regards to Even, 3 Foot, or 6 Foot, then ...
Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    May 21, 2017

    One possible solution:

    =INDEX('1C'!A:D,LOOKUP(B7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B5,'1C'!1:1,0))

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 24, 2017

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.

 

John Breeden's avatar
John Breeden
Copper Contributor
Feb 19, 2018

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

Resources