Hi All,

I'm new to Excel ( and to this forum :) ) and so I hope somebody may be
able to help me.

I've got 2 questions....

QUESTION 1:
I've got a spreadsheet which takes data from one worksheet and uses it
to calculate data in a second worksheet using the following code /
formula:

=IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November
2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19'4th
November 2005'!B19,"DOWN"))))

The problem is, when I create a new worksheet I have to go through and
update all of the references to the previous worksheet. I know that I
can do a "find and replace" (which isn't too much of a pain), but what
I would like to do is use some kind of a relative (rather than
absolute) formula.

So instead of saying "go to the worksheet called '4th November 2006' "
I could instead say "go to the sheet which preceeds this one"

- hopefully that makes sense! :)

QUESTION 2:
In addition to the above formula (which just tells me if a value is
higher, lower, or the same as the previous value) is it possible to
calculate what the difference is and display that in brackets?

_For_example:_
If a result in the spreadsheet for "4th November 2005" = 6 and the
result in the spreadsheet for "4th February 2006" = 1, it would show:
"UP (+5)"

Even better still, could I -also -reference an image (up arrow / down
arrow) so that I could generate a result which looks a bity like those
you get in the music charts.

Cheers!
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

I'm not sure about your first question, but in answer to your second
question, you can amend your existing formula as follows:

=IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November
2005'!B19,"UP (+"&TEXT('4th November
2005'!B19-B19,"00")&")",IF(B19='4th November
2005'!B19,"Same",IF(B19'4th November 2005'!B19,"DOWN (-"&TEXT(B19-'4th
November 2005'!B19,"00")&")" ))))

Hope this helps.

Pete

This requires a User Defined Function (UDF). See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html


In article ,
kessa wrote:

QUESTION 1:
I've got a spreadsheet which takes data from one worksheet and uses it
to calculate data in a second worksheet using the following code /
formula:

=IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November
2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19'4th
November 2005'!B19,"DOWN"))))

The problem is, when I create a new worksheet I have to go through and
update all of the references to the previous worksheet. I know that I
can do a "find and replace" (which isn't too much of a pain), but what
I would like to do is use some kind of a relative (rather than
absolute) formula.

So instead of saying "go to the worksheet called '4th November 2006' "
I could instead say "go to the sheet which preceeds this one"

One way:

=IF('4th November 2005'!B19="", "nothing here dude", CHOOSE(SIGN(B19
- '4th November 2005'!B19)+2, "UP", "SAME","DOWN") & TEXT(B19 - '4th
November 2005'!B19, " (-0); (+0);;"))

In article ,
kessa wrote:

QUESTION 2:
In addition to the above formula (which just tells me if a value is
higher, lower, or the same as the previous value) is it possible to
calculate what the difference is and display that in brackets?

_For_example:_
If a result in the spreadsheet for "4th November 2005" = 6 and the
result in the spreadsheet for "4th February 2006" = 1, it would show:
"UP (+5)"

Even better still, could I -also -reference an image (up arrow / down
arrow) so that I could generate a result which looks a bity like those
you get in the music charts.

Hi JE McGimpsey & Pete_UK,

Thank both for your help on this!

At the moment I seem to be getting a #value! error when I try either
solution. Any ideas? Do I need to set something else up / change
something?

To help me figure out what's going on, could you please let me know
what the following are/do:

Choose
Sign
Text

Are they functions which Excel will recognise, or are they things that
I need to set a UDF for?

Also, JE McGimpsey - thanks for the info about the UDF. I had no idea
that you could declare your own functions in Excel.... how cool is
that! :)

Cheers
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Hard to troubleshoot without knowing what the values are in the relevant
cells.

You can get a #VALUE! error if one of the cells contains text (or a
number entered as text). That includes space characters if you "delete"
cells by typing the space bar.

In article ,
kessa wrote:

At the moment I seem to be getting a #value! error when I try either
solution. Any ideas? Do I need to set something else up / change
something?

Hi JE McGimpsey,

The "position" column contains the following data:

"Not in top 100"
A number (from 1 - 100)
Empy (whitespace for no value)

In addition, the column for used of calculating the difference (where
the formula is located) contains / writes:

Up
Down
Same
Nothing here dude (which I use to spot errors, etc)

In all cases, the formatting has been set to "General" as I never know
whether a particular field will contain letters, numbers or both.

Does that help at all?

Cheers
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Hi All,

Can anyone offer a follow up on this.... JE McGimpsey? :)

Thanks
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Both formulae are expecting numbers in B19 and in '4th November
2005'!B19, as one is subtracted from the other - are these your
"position" columns?

If you have text values in them, such as "Not in top 100" or just
spaces, then you will get the #VALUE! error because you cannot perform
arithmetic on text.

The solution is to ensure that you have only numbers or blanks
(completely empty) in these cells.

Pete

Hi Pete_UK,

A quck update - I just managed to get your example to work (thanks!)
and so I'm guessing I had popped it in (or referenced) a field where
one of the cells contained "Not in top 100" - doh!

In the cases of the "Not in top 100" entries, I really need to leave
them in and so I wondered if excel offered some kind of replace
function? For example, would it be possible to "replace" the occurances
of "Not in top 100" (in the formula only) with the number "101"?

Therfore the logic would go something like this:

-If -the appropiate cell position column is -equal to- "Not in top 100"
-then replace- with "101" -then- perform calculation to determine the
difference.
(This would obviously need to be incorporated into your existing
formula)

I hope that makes sense?

Also, it it possible to reference the "up", "down" and "same" image
arrows in excel?

Thanks again for all of your help!
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

If the text "Not in top 100" could appear in either B19 or in '4th
November 2005'!B19, then you could amend my earlier formula as follows:

=IF(OR(B19="Not in top 100", '4th November 2005'!B19="Not in top
100"),"Outside top 100", IF('4th November 2005'!B19="","nothing here
dude",IF(B19<'4th November 2005'!B19,"UP (+"&TEXT('4th November
2005'!B19-B19,"00")&")",IF(B19='4th November
2005'!B19,"Same",IF(B19'4th November 2005'!B19,"DOWN (-"&TEXT(B19-'4th
November 2005'!B19,"00")&")" )))))

Watch out for line breaks in the posting - this is all one formula. You
can change the comment "Outside top 100" how you wish.

Here's one way of achieving the images - assuming the formula above is
in C19, enter this formula in D19:

=IF(LEFT(C19,1)="U",CHAR(74),IF(LEFT(C19,1)="D",CH AR(76),IF(LEFT(C19,1)="S",CHAR(75),"")))

and format the cell using the Wingdings font - these are smilies in
Wingdings.

Hope this helps.

Pete

Cheers Pete,

Actually, just after posting my last message I had another go and tried
using IF. I came up with the following all on my little lonesome (I just
wanted to let you know that all your teaching is not in vain! :)):

=IF('4th November 2005'!B16="","nothing here dude",
IF(B16<'4th November 2005'!B16,"UP (+"&TEXT(IF('4th November
2005'!B16="Not in top 100","101",'4th November 2005'!B16)-IF(B16="Not
in top 100","101",B16),"00")&")",
IF(B16='4th November 2005'!B16,"Same",
IF(B16'4th November 2005'!B16,"DOWN (-"&TEXT(IF(B16="Not in top
100","101",B16)-IF('4th November 2005'!B16="Not in top 100","101",'4th
November 2005'!B16),"00")&")" ))))

Let me know if you can spot any errors or if your solution provides a
neater alterative - I just was quite pleased to have managed to figure
something out :)

Regarding the images, is the only option to use wingdings, as I really
wanted to be able to use a green arrow for up and a red arrow for down
(unless you can colour wingdings?)

Cheers
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Kessa,

well done - the best way to learn is to try things and then to learn
from any mistakes. You've chosen to replace "Not in top 100" with "101"
which is still text and a bit arbitrary, whereas my formula returns a
message. I think you will need to change "101" to 101 for it to work.
Then test it out by putting different values in the cells referred to.

Regarding the images, you can use any font and a character in that font
which is displayed as an arrow - you may well have another symbol font
on your PC which you could use.

You can use conditional formatting to give you different coloured
effects depending on a cell's contents - select the cell(s) and click
Format | Conditional Formatting. In the panel presented to you select
Cell Contents then "Equal to" and then "J" (same as character 74 that I
recommended above). Then click on the Format button, and then choose
colour green, maybe with a background colour (Patterns tab) of black.
Then click ok and you want to "Add" another condition and choose a
different colour and background in the same way - maybe red foreground
with yellow background. You can have a maximum of 3 conditions.

Hope this helps.

Pete

Kessa,

I've experimented a bit and found the following arrow symbols in
Wingdings (I think it depends on which version of Windows you have, as
a font sheet I did some years ago is different):

UP - CHAR(199), Down - CHAR(200), Same - CHAR(198)

My other comments about conditional formatting still hold, but you will
have to enter the CHAR(199) rather than just "J".

Hope this helps.

Pete

Hi Pete_UK,

Thanks - the arrows are now working a treat. The only thing I still
can't get to work is the conditional formatting.

I've tried using: "cell is" --- "equal to" ---"CHAR(199)"
(OR, CHAR(200), CHAR(198) as appropriate)

....but the colour remains the same.

I also tried using "greater than", "less than" (as an attempt at error
checking) and that did colour the arrows, which I guess indicates it
doesn't recognise the value....?

Any ideas?
Cheers
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Hi Kessa,

I think if you highlight the cells again and go to Format | Conditional
Format you will see that Excel will have put quotes around the
char(199) etc, i.e. it will look like:

="char(199)"

in the panel. All you need to do is edit this to remove the quotes in
both panels (red and green), so that it looks like:

=char(199)

and then click OK. You should then see your red and green arrows, as
appropriate.

Hope this helps.

Pete

Kessa,

As an aside, but related to your task, enter this formula in A1 of a
blank sheet:

=CHAR(ROW())

then copy down to row 255. Highlight this column and choose Wingdings
as the font. You can now scroll down and see what symbols are available
with this font. You might like to choose characters 232 to 234 for your
arrows, or characters 241 to 243, or even 216 to 218.

Pete

additional on the aside comment:
You could extend the example with other special fonts as well
http://www.mvps.org/dmcritchie/rexx/htm/fonts.htm
and can be used as it appears there if you are using Internet Explorer
as your browser.

"Pete_UK" wrote ...
Kessa,

As an aside, but related to your task, enter this formula in A1 of a
blank sheet: =CHAR(ROW())

then copy down to row 255. Highlight this column and choose Wingdings
as the font. You can now scroll down and see what symbols are available
with this font. You might like to choose characters 232 to 234 for your
arrows, or characters 241 to 243, or even 216 to 218.

Hi Pete_UK,

Yippee!!! That's perfect - thank you SO much!
(...and thanks for the extra info about viewing the charset for the
wingdings font)

But finally, thanks so much for your continued patience and support -
I've already learnt so much more about excel than I could have
originally expected.

Cheers!

*David McRitchie* - Hi David,

Thanks for the extra info. It's a really handy to be able to see the
various symbols side by side.

Cheers both!
Kessa


--
kessa
------------------------------------------------------------------------
kessa's Profile: http://www.excelforum.com/member.php...o&userid=32080
View this thread: http://www.excelforum.com/showthread...hreadid=518338

Hi Kessa,

thanks for feeding back. If you have any other queries in the future,
you know where to come to ...

Pete