check if formula exists and take 1st part of formula 2

[robcarr]

Hi,

I am updating a report to show differences of data over 2 days.

data in column a shows the data i found the first time, and column b shows the data i found the second time i ran the coding, column c shows the difference between b and c
this all works fine.
However, every now and then the inputter will change the the data in column a if he needs to make an adjsutment.

the adjustment would be like so

=949.566666666667-121

the column c formula (=b1-a1) to calculate the first part of formula above and not the value.
What i need to is to extract all numbers and decimals from the cell if it begins with a "=", the end part can of the cell above can be any length and can be either + or -, that doesnt really matter to me, however i do need the original figure which is the value after the =, and before the + or -.





Hope this is of use, Rob.

 

[SkipVought]

"however i do need the original figure"

Well then, you need to STORE the original figure[red]s[red/]. Use a hidden sheet, for instance. Then just do your difference on the originals.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i cant do that, it would store to much info.

the sheets are 4000 rows by 40 columns x 14 sheets, it would take too long,

is there no way to check for a formula and then break the formula down?



Hope this is of use, Rob.

 

[AnotherHiggins]

-> is there no way to check for a formula and then break the formula down?

No, I don't think there is.

Excel is storing the value produced by the formula. If A1 contains [COLOR=blue white]=949.566666666667-121[/color] and B1 contains [COLOR=blue white]=Left(A1, 1)[/color], B1 is going to return 8, not = .

Moreover, I would strongly advise against letting users just go around editing historical data in the cell. It is a fact that every person makes mistakes. So the more you have people messing with data, the more errors you introduce into that data.

Instead, how about adding a column where users can put in a value to be added to the original number. That way you can see which values have been edited and there is a reduced chance of users accidentally deleting/overwriting some of the numbers from the original value.

Example:
[tt]
1^st NEW ADJUSTED 2^nd
RUN COLUMN! VALUE RUN
Col A Col B Col C Col D
949.56 -121 828.56 ???
[/tt]
Where C1 contains [COLOR=blue white]=A1+B1[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lionelhill]

robcarr,

Yes, theoretically this can be done, though it's messy, and may not scale to the size of workbook you are using:

(1) you can use Edit-Find-Replace to replace all instances of "=" in a formula with a blank. This converts all formulae to text.

You can then use left, right, mid etc. to extract any information you need.

(2) If you only have a few instances of data-inputters adding corrections, you can merely use "Find" to locate them manually; if you do this, make sure you carry out the find by columns, as otherwise you'll stop on every cell in column C

(3) If your data-input system begins all entries with "=", even if they are merely one number, you may find it better in option (2) to do the search for "+" and "-" rather than "=".

 

[robcarr]

i cant edit the file, it is a global file, I am just creating a checked to compare values,

If i cant get a way to extract the formula I will have to look at a nother way or not do it.

thanks for input so far.

Hope this is of use, Rob.

 

[NWBeaver]

It is actually quite easy to detect an "=" sign and extract the formula, if using a few lines of VB code is option for you.

The example below looks for a formula in column A and copies the formula to column D if it finds one. So if a cell in column A happened to contain the formula =166-34.5 for example, column D would then contain '=166-34.5
The example only loops through the first 4 rows.


Sub Formula_Test()

For intR = 1 To 4
strX = Worksheets(1).Cells(intR, 1).Formula
If strX Like "=*" Then
Worksheets(1).Cells(intR, 4).Value = "'" & _
Worksheets(1).Cells(intR, 1).Formula
Else
Worksheets(1).Cells(intR, 4).Value = ""
End If
Next intR

End Sub

 

[xlhelp]

My apologies, but I don't have time to work this out through completely.

But here is a partial solution. If your adjustments, as you mention, are going to be in Column A, then go to Cell A1, go to Insert, Name, Define and in the "Refers to:" box type in
=GET.CELL(6,Sheet1!$A1)
and in the "Names in Workbook" box give it a name such as formis (what I used).

My example contained formula
=949.2345+5

when I type the formula
=IF(LEFT(formis,1)="=",FIND("+",RIGHT(formis,LEN(formis)-1)),"whatever")
in a corresponding cell in another column, I get the answer 9, indicating the + sign is the ninth character in the formula, not counting the "=". If there is no formula in the cell, I get the result "whatever".

If this is what you are looking for, then hopefully you can expand on it.

Vita Brevis

 

[xlhelp]

Here is completed formula:
=IF(LEFT(formis,1)="=",IF(ISERROR(FIND("+",RIGHT(formis,LEN(formis)-1))),MID(formis,2,FIND("-",RIGHT(formis,LEN(formis)-1))-1),MID(formis,2,FIND("+",RIGHT(formis,LEN(formis)-1))-1)),"")

This takes care of both the plus sign and the minus sign

Vita Brevis

 

[robcarr]

thanks this works exactly as required.



Hope this is of use, Rob.

 

[lionelhill]

xlhelp, may I ask a favour? Is there any online source of information about techniques such as the solution you described? (e.g. what other bits of information about the cell are available if I choose numbers other than 6). Excel's help seems blissfully unaware of the possibility. But there again, Excel's help is blissfully unaware of many of Excel's best features.

Oh, and have another star. Very helpful!

 

[GlennUK]

Hi lionelhill,

to see the full list of Excel 4.0 macro functions download the Help file from Microsoft:

http://support.microsoft.com/kb/128185

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

Thanks hugely!
Li