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