Can I create a variable? Or two?

Chris Shields

Hello,

I'm working through some quotes for a project. In this project there area series of wall panels, in a series of locations. The material per sqft costs $X and the installation labor costs $Y per sqft. I have two cells in a hidden column that hold these numbers (should I have to edit it them later).

Since the panels aren't all the same size, I have rows for panel 1, panel 2, panel 3....etc. In the column next to the panel name is the total sqft for that panel. So let's say Panel 1 is 50 sqft. The cost would be 50 x $X (=[Panel SQFT]@row * [sqft costs]2).

However when i drag the corner down on my cell it adds 1 to the sqft cost cell. So the subsequent equations look like this:

=[Panel SQFT]@row * [sqft costs]3

=[Panel SQFT]@row * [sqft costs]4

=[Panel SQFT]@row * [sqft costs]5

Well that's no good since only cell [sqft costs]2 has the sqft cost in it.

How can i create a variable, such as "N", that always holds the value of the cost so that when I cell drag down the column the equations look something like this:

=[Panel SQFT]@row * N

=[Panel SQFT]@row * N

=[Panel SQFT]@row * N

That make sense?

Genevieve P.

Hi @Chris Shields

It sounds like you just want to ensure that your formula locks on to one cell and does not adjust when the formula is dragged or moved around. This is called an absolute reference and it's definitely possible to do!

To create an absolute reference, put a $ sign in front of either the column name (if you want to lock in the column) or the row number (if you want to lock in the row). In your instance, you could lock both the column and the row to keep the formula stuck on that one cell, like so:

=[Panel SQFT]@row * $[sqft costs]$2

Then when you drag-fill this formula it will always stay locked on to row 2 in the column [sqft costs]. See our Help Center (here) for more information on Absolute References.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Haha no worries. Make sure you have the $ in front of the number 2 as well as in front of the column name:

$[sqft costs]$2

Genevieve P.

Hi @Chris Shields

It sounds like you just want to ensure that your formula locks on to one cell and does not adjust when the formula is dragged or moved around. This is called an absolute reference and it's definitely possible to do!

To create an absolute reference, put a $ sign in front of either the column name (if you want to lock in the column) or the row number (if you want to lock in the row). In your instance, you could lock both the column and the row to keep the formula stuck on that one cell, like so:

=[Panel SQFT]@row * $[sqft costs]$2

Then when you drag-fill this formula it will always stay locked on to row 2 in the column [sqft costs]. See our Help Center (here) for more information on Absolute References.

Let me know if this works for you!

Cheers,

Genevieve

Chris Shields

Hmmmm, I'll have to check it out a little bit later. I tried it quickly, but it still changed cell numbers on my. But to be fair, I'm pressed for a deadline, so I might have done something dumb.

Genevieve P.

Haha no worries. Make sure you have the $ in front of the number 2 as well as in front of the column name:

$[sqft costs]$2

Chris Shields

THAT'S what I was missing. The second $.

Thank you!

Genevieve P.

Hi Chris,

I'm so glad you got it working! 🙂

Thanks for letting me know,

Genevieve