Forum Discussion
Solved
How-To Needed: Combine Conversion and Worksheet Reference Formulae
A formula novice level question follows:
The source worksheet, 'FO-PO', includes foot/inch values.
On another worksheet, Calculations, in the same workbook, ...
How do I combine the two following formulae in a single cell?
Source worksheet reference in a separate cell now in the Calculations worksheet:
='FO-PO'!F27
Foot/inch to decimal foot formula cell now in the Calculations worksheet:
=B11=LEFT(A11,FIND("-",A11)-2)+SUBSTITUTE(REPLACE(A11,1,FIND("-",A11),""),CHAR(34),"")/12
NOTE: The separate worksheet reference portion of the formula will then be used to combine with other calculations such as multiplication and summing in a single cell.
Thanks,
Clint
It's not altogether clear what you're asking here. I'm going to do some guessing, trying to interpret, so you may need to correct my assumption.
First (and primary) assumption is that cell A11 contains that first formula, ='FO-PO'!F27
Second assumption, you want that A11 formula to be "combined"--the more usual term would be "embedded"--into the formula in cell B11, which is the longer formula you show.
=LEFT(A11,FIND("-",A11)-2)+SUBSTITUTE(REPLACE(A11,1,FIND("-",A11),""),CHAR(34),"")/12
If those assumptions are accurate, the most straight-forward way would be to simply replace each instance of A11 in the longer formula with 'FO-PO'!F27, which would result in
=LEFT('FO-PO'!F27,FIND("-",'FO-PO'!F27)-2)+SUBSTITUTE(REPLACE('FO-PO'!F27,1,FIND("-",'FO-PO'!F27),""),CHAR(34),"")/12
There are other things that could be done (e.g. using the LET function) or creating a self-defined formula using LAMBDA, but let's first see if any of my assumptions are on track.
It's not altogether clear what you're asking here. I'm going to do some guessing, trying to interpret, so you may need to correct my assumption.
First (and primary) assumption is that cell A11 contains that first formula, ='FO-PO'!F27
Second assumption, you want that A11 formula to be "combined"--the more usual term would be "embedded"--into the formula in cell B11, which is the longer formula you show.
=LEFT(A11,FIND("-",A11)-2)+SUBSTITUTE(REPLACE(A11,1,FIND("-",A11),""),CHAR(34),"")/12
If those assumptions are accurate, the most straight-forward way would be to simply replace each instance of A11 in the longer formula with 'FO-PO'!F27, which would result in
=LEFT('FO-PO'!F27,FIND("-",'FO-PO'!F27)-2)+SUBSTITUTE(REPLACE('FO-PO'!F27,1,FIND("-",'FO-PO'!F27),""),CHAR(34),"")/12
There are other things that could be done (e.g. using the LET function) or creating a self-defined formula using LAMBDA, but let's first see if any of my assumptions are on track.
- Your assumption is correct. I appreciate your time, solution, and the additional suggestions. It is recognized that application knowledge is needed: Formal coursework has just begun today.
