Links and replace data (Excel 97)

Topic

New Reply

Good morning everybody!
I have the following problem. I have two files, one with the ‘raw’ data as I call it, and the second one is linked to the first one, with the report in a nice and presentable format.
As I am working with % change data in some columns of my report, sometimes, depending on my values I have -100. Somebody asked me if I can replace the -100 with NA (not available), it would just look better, she said. Now, I can only make modifications to the linked file, I cannot touch the original file with the ‘raw’ data.
I was thinking in the lines of an IF formula in each cell, which would work, but this solution is very very tedious and long, as I am working with practically hundreds of cells like these, scattered across 10-12 sheets. It would take me ages to input this formula in every cell, even if I could drag down the formula to other cells below. Also my link looks like this for example:
=’D:seriesClientCountryCategory[rawdata.xls]Total’!F7 . If I will use an IF formula, it will become also very, very long:
=if(=’D:seriesClientCountryCategory[rawdata.xls]Total’!F7 =-100, ‘NA’, ‘D:seriesClientCountryCategory[rawdata.xls]Total’!F7).
Can anyone think of an easier way to accomplish this?
I also thought of a macro, which searches for -100 and replaces them with NA’s, but for that I need to have real values, not links in my file.
Any help, hints very welcome and appreciated.
K.

Reply | Quote

Replies

You could use format, conditional format and set the cells that are equal to -100 (-1 ??) to have a white font.

Reply | Quote

I thought about this too, but unfortunately it wouldn’t work as the person wants explicitely NA instead of -100, and not an ’empty’ cell.
But thanks anyway for your kind post.
Any oder ideas maybe?
Regards,
K.

Reply | Quote

What about this macro:

Option Explicit

Sub MakeIfs()
Dim rCell As Range
Dim sOldFormula As String
Dim sNewFormula As String
Dim sElse As String
Dim sCondition As String
sCondition = “=-100”
sElse = “””NA”””
For Each rCell In ActiveSheet.UsedRange.Cells
sOldFormula = rCell.Formula
If Left(sOldFormula, 1) = “=” Then
sOldFormula = Right(sOldFormula, Len(sOldFormula) – 1)
sNewFormula = “=IF(” & sOldFormula _
& sCondition & “,” & sElse & “,” & sOldFormula & “)”
rCell.Formula = sNewFormula
End If
Next
End Sub

Reply | Quote

Use a custom number format to display -100 as NA. Choose menu command Format/Cells and click the Number tab, and select Category “Custom” from the list on the left hand side, and then in the Type input box type this :
[=-100]”NA”;#0
and click the OK button, and that should do what you want.

Glenn Bumford

Reply | Quote

Nifty! [ I have to remember that.

Cheers

Reply | Quote

Thanks for all your responses, they are very useful. I think, however, the the best one that suits my needs is the custom cell formatting, because it seems the easiest solution from all. I will keep the macro in my list of useful macros, but for now, I simply took the formatting given and modified it to also show the negative numbers with red font and in brackets. I have a very minor problem with it though, that even the 0 is shown as negative now. I can’t figure out why… Can you please help me again?
Here is what I did so far:
#,##0;[=-100]”NA”;(#,##0)

Thanks again for all your help.
K.

Reply | Quote

Try this
#,##0;[=-100]”NA”;General

For those following this thread –
you have 4 settings you can control in a custom format, each setting is separated from the others by a semicolon.
first position is for positive number format;second position is for negative number format;third position is for zero;fourth position is for text.

Cheers

Reply | Quote

#,##0_);[=-100]”N/A”;0_; also works. He needed to remove the red formatting from the last argument, and he loses ability to further format negative numbers. Personally I’d go for using NA() somewhere in the formula.

I didn’t know you can use text names like that! It’s not in Help. But “General” seems to be the only one that works in XL97; are there others?

Reply | Quote

I don’t think so …. but hey anything is possible!

Cheers

Reply | Quote

You can have more than one section with a clause in it. Try this
[=-100]”NA”;[ red ][<0](#,##0);#,##0_)
which will make your negatives red with brackets round. ( Leave the spaces out of [ red ], as I've done this to stop it being mistaken for a posting font control string. )

Glenn Bumford

Reply | Quote

I see what the problem is; when you put [ red ] in -without- the spaces, the Lounge is interpreting it as turning the post message font red. So for custom formats in Excel your posted word [ red ] is not showing up. Put in spaces like I have here, then tell everyone not to use the spaces in Excel.

Reply | Quote

Thanks John, have edited my posting now, so should make more sense.

Anyway, the rules for conditions of your own in Custom Formats is very similar to the standard, except that when you create conditions the rules go something like
[condition 1]number format 1 ; [condition 2]number format 2;number format 3;text
where number format 3 is applied to cells that fail conditions 1 and 2.

Glenn Bumford

Reply | Quote

This is a cool tip Glenn. In playing with it in XL97, it appears that 1. there can only be one “conditional” format per custom format argument and 2. we are restricted to the standard four arguments (pos, neg, zero, text); therefore we can’t use multiple conditional formats for any particular one (of the four) format argument sets. Or am I missing something?

Reply | Quote

No, you’re not missing anything. The four arguments limit, and the fact that only one condition is allowed per argument means that it’s usefulness is quite limited, but when your needs can be met this way it’s a quick way to achieve something nifty very quickly.

Glenn Bumford

Reply | Quote

Does [>=0]#,##0;[=-100]”NA”;[ red] (#,##0) help ?

Whilst the formatting sections cover Positive, Negative,Zero and text under normal circumstances, in effect the thgird section applies to any numbers not covered by either the first or second sections, so that if conditions apply to those it could upset what the third section applies to.

Andrew C

Reply | Quote

Though others have solved your problem, the following is sometimes useful. An alternative to filling down &/or across is to select all the cells you want the formula entered in, type the formula in the formula bar, then hold down Ctrl and press Enter, this enters the formula in all the selected cells. It works on multiple selections.

Reply | Quote

Cheers to everybody!
I want to say a great thanks to all of you. Some gave me a direct help, others useful tips which are worthwhile keeping.
I will print out the whole thread and chew through it carefully.
You are the greatest!

K.

Reply | Quote

I find I use this all the time. 9 times out of 10 I want to copy the formula but not the formatting. Usually the formula is already entered, so I use a minor variation. Select all the cells, including the source cell with the formula to be copied (the source cell should be the active cell.) Press F2, then Control+Enter. Effectively it’s equivalent to Control D or Control R (copy down or copy right), except the formatting isn’t copied.

Reply | Quote