Lock Named Range in Formula

[nemPyong]

This is my first post, and I'm a total noob in Excel
So, I want something that will lock a named range, which is for cell, it would be the $ sign, like: $A$1

Let's say I have dummy pivot table named Table_Flower.accdb4 with some column (left to right):
Name
Genus
Species
Color
Price
Then I made this formula in another sheet

Please Login or Register  to view this content.

The problem is that, when I drag the formula to the left or right side, the named range also moves. It will be like

Please Login or Register  to view this content.

and so on

So, is there a way to lock the named range?

[FDibbins]

Hi and welcome to the forum

A named range is 1 that you actually give a name to through the Name Range Manager, and it looks like thats not what you have done?

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across


what exactly are you trying to do, because there are other functions in 2013 that have to a large extent replaced the need to use sumproduct?

[Tony Valko]

Which range do you want to make absolute? Name? Price? Or both?

Let's assume you want to make both absolute:

=COUNTIFS(Table_Flower.accdb4[[Name]:[Name]],M$3,Table_Flower.accdb4[[Price]:[Price]],$A7)/M$5

[nemPyong]

Which range do you want to make absolute? Name? Price? Or both?

Let's assume you want to make both absolute:

=COUNTIFS(Table_Flower.accdb4[[Name]:[Name]],M$3,Table_Flower.accdb4[[Price]:[Price]],$A7)/M$5

GYAAAA It works! It works! Waaah thank you, thank you so very much!
Every range, and it worked well for all!
Oh Hero, again, thank you so very much! :'D

Hi and welcome to the forum

A named range is 1 that you actually give a name to through the Name Range Manager, and it looks like thats not what you have done?

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across


what exactly are you trying to do, because there are other functions in 2013 that have to a large extent replaced the need to use sumproduct?

Hi!

Hooo so that's Named Range. I thought that every range that has name is categorized as Named Range, so I thought a column of a table must be one of them >.< Thank you so much for your correction!

Yes I know the using of $, thank you so much for the explanation

hmmm... what I wanna do... I'm so confused to tell it in words, so that's why I used an example and formula.
So, I wanna get the number of row where some column's value of the row matches a particular cell.
If it's being said in SQL, more or less it will be:

Please Login or Register  to view this content.

googling and googling, I found that SUMPRODUCT can do that
If you know another way to do that in Excel 2013, that would be very good, but judging from some condition, for now I think (maybe) that's enough

---

Thank you so much for all the reply! The problem is solved!
Waaah I'm so happy with the fast response :')

[Tony Valko]

You're welcome. Thanks for the feedback!