Formula Confusion

Gene1323 · May 13, 2011

Formula Problem
Trying to extract Data from a Formula.

I have a formula in Cell M30:
=IF(B38="Year 3 ",SUM($H$31:H38),0)

I am trying to use the MID & Find command to extract data from Cell M30:
=MID(M30,FIND("Y",M30)-0,7)

The result I get is:
#VALUE!

I expected to get the result:
Year 3

If I remove the leading (=) from Cell M30 the MID & Find Formula works.

How do I extract data from a Cell that is a Formula, or can I ???

I may need to add a Column containing just the desired Data.
This Spread Sheet is over 600 rows and the "Year" data changes every 12 rows.

tx
gene

ScottD · May 13, 2011

Your formula looks at the results in cell M30, not the formula. The results are numeric, so therefore there will be no "Y" to find. Perhaps you should be looking for "Year 3" in cell B38 if that's what you are looking for.

JackDanIce · May 13, 2011

Welcome to the board.

Your question doesn't make sense. You state in cell M30 you have the formula:

Rich (BB code):

=IF(B38="Year 3 ",SUM($H$31:H38),0)

This will return the SUM of the values H31:H38 or the value 0, which I'm guessing are numeric values.

You then say you are trying to extract data from cell M30 with text functions, but the output of the formula appears to be numerical, so how can you be extracting text?

What if you change the reference to cell B38, i.e.

Rich (BB code):

=MID(B38,FIND("Y",B38)-0,7)

To be honest, it's not very clear what you're trying to do. You may want to install Jeanie so you can upload your spreadsheet:
http://www.excel-jeanie-html.de/index.php?f=1

CharlesChuckieCharles · May 13, 2011

Gene1323 said:
Formula Problem
Trying to extract Data from a Formula.

I have a formula in Cell M30:
=IF(B38="Year 3 ",SUM($H$31:H38),0)

I am trying to use the MID & Find command to extract data from Cell M30:
=MID(M30,FIND("Y",M30)-0,7)

The result I get is:
#VALUE!

I expected to get the result:
Year 3

If I remove the leading (=) from Cell M30 the MID & Find Formula works.

How do I extract data from a Cell that is a Formula, or can I ???

I may need to add a Column containing just the desired Data.
This Spread Sheet is over 600 rows and the "Year" data changes every 12 rows.

tx
gene

two part answer

put the following VBA in a module ( not thisworkbook or sheet1-2 etc)

Code:

Public Function getformula(rng As Range) As String
 
getformula = rng.Formula
 
End Function
[Code]
 
 
Then put this in your spreadsheet
 
=MID(getformula(M30),FIND("Y",getformula(M30)),7)

Gene1323 · May 13, 2011

You are correct, My formula is trying to find "Y" then extract 7 characters or "Year 3 ".
But you hit on the problem, My formula is looking at the results of Cell M30 NOT the contents of Cell M30. . . Hu-m-m-m, I will re-think this.

Thanks
I could not see the Forest for the Trees.
Gene

Formula Confusion

Gene1323

New Member

Excel Facts

ScottD

Board Regular

JackDanIce

Well-known Member

CharlesChuckieCharles

Well-known Member

Gene1323

New Member

Similar threads

Forum statistics

Share this page

Formula Confusion

Gene1323

New Member

Excel Facts

ScottD

Board Regular

JackDanIce

Well-known Member

CharlesChuckieCharles

Well-known Member

Gene1323

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock