How to reference cell above named cell? 1

[may1hem]

I'm writing a formula in Excel 2003, and it's quite complex and hard to understand so I want to use named ranges instead.

I have named a range of cells "Totals", which is a column of 6 values from K7 to K12.

I'm writing a formula in cell F7 which checks if a total exists in the previous row, i.e. it checks if there is a value in cell K6.

At the moment I use: =IF(K6="", "Value found", "Nothing found") , but as I said I want to use named ranges to make it more readable.

I know that if I changed the formal in F7 to: =IF(Totals="", "Value found", "Nothing found") , then this will check cell K7 but I really need to check the cell above (K6).

So how can I get it to check one row up (cell K6) using a reference to the named range (Totals)? Is there some way I can get it to check one row up relatively?

Thanks,
May

 

[GlennUK]

Don't use a name for that particular reference, just use the cell reference.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

You can define a Named Range using Relative Referencing. That is, you can make a name always refer to the cell five columns right, one row up, relative to the cell containing the formula that uses the name. That would get you from F7 to K6.
[ul]
[li]Single Click cell F7[/li]
[li]Go to Insert > Name > Define[/li]
[li]Type in a Range Name - I'll use rngPrevTotal[/li]
[li]In the Refers To box, Left click and drag to select all the writing in the box (to ensure that we completely overwrite everything there, rather than just adding to it)[/li]
[li]Single Click on K6[/li]
[li]Press [F4] three times (you'll see that this changes the position and presence of the dollar signs - we are rotating through the different reference options)[/li]
[li]Press Add[/li]
[li]Press OK[/li]
[/ul]
Now you can use that reference in F7 to refer to K6. Or in F8 to refer to K7. Or, for that matter, in R30 to refer to W29.

[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.

 

[GlennUK]

Hi John,

yes, that can be done, but many users can't get to grips with relative reference defined names. Every time I've done it I had to explain again and again to the users how it works ( even though the explanation is in the user guide ).

A very good explanation though. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[may1hem]

Glenn I've clearly said that I'm already using the cell reference but I want to change to to using named references. I don't think it's right to suggest that John's explanation should be withheld on the basis that some users might or might not understand it. How else are people supposed to learn??

Thanks John, I'm sure that will do the trick. Thanks for taking the time to explain that, it makes perfect sense. =)

May

 

[GlennUK]

Hi May,

I did not suggest that John's explanation should be withheld. I just said that the method might be hard to understand for some people ... are you saying that this is not the case? It was a caveat for any developers reading this, so that they are aware of how users may find this method confusing. I'm sorry if that was not clear to you, but I was not intending to prevent people from learning.

Cheers, Glenn.

Beauty is in the eye of the beerholder.