CODEWITHSUNDEEP
excelvbaformula
theradman ya
theradman ya

Reputation: 15

Why won't my spreadsheet equations stay the same after i add a new row

I am making a spreadsheet which has multiple sheets and one of the sheets has an equation that looks to see if there is anything written in a certain cell on all the other sheets. Right now I just use this equation to find out whether or not it has anything written in that cell.

=IF(LEN('A'!N18)>1,CONCATENATE('A'!C18," "),)

This is just checking to see if anything is in the cell. The problem is that I want this to keep working if I add a new row on A, but right now it bumps row 18 to 19 and I am left with no row 18 so it won't even check that new row!

Upvotes: 1

Views: 98

Answers (2)

user4039065
user4039065

Reputation:

If you want to add a row between row 1 and row 18 on worksheet A but keep your formula references to A!N18 and A!C18 then you need to use INDIRECT or INDEX. Of these two, INDEX is the better choice as it is not volatile. A volatile function like INDIRECT will recalculate whenever anything in the workbook chanmges. A non-volatile function like INDEX will only recalculate when something that affects its outcome is changed.

'INDIRECT method; works but not optimal
=IF(LEN(INDIRECT("'A'!N18"))>1,CONCATENATE(INDIRECT("'A'!C18"," "),)
'INDEX method; works and is non-volatile
=IF(LEN(INDEX('A'!N:N, 18))>1,CONCATENATE(INDEX('A'!C:C, 18)," "),)

Upvotes: 1

PC_Goldman - SE is rotting
PC_Goldman - SE is rotting

Reputation: 105

The link that Slai posted in the comments to your question should be what you're looking for. If you don't want a reference to change when you add/delete rows/columns you need to use absolute references. As currently written "N18" and "C18" are relative references. If you change the columns/rows on sheet 'A', these references will automatically change with them. That's why it's moving to row 19 on you.

You can turn these into absolute references by adding "$" like this: "$C$18" and "$N$18". The first "$" sets the absolute reference for the column and the second sets the absolute reference for the row. You can mix and match these for various results.

Mixing relative and absolute values really comes in handy when you are reusing a formula with slight differences. For instance, if you want to multiply a number in column b (starting with row 4) by the number in A1 and show the result in column C (also starting with row 4). You'll always be using A1 so we can set this as an absolute value and in C4 enter the formula =$A$1*B4. Copy this down column C and it will automatically update the B value to the new column but will always use "$A$1" for the other part.

You can split the relative reference by only using the "$" on the column or row reference of the reference. Whichever one you use it on will be locked and the other can still adjust based on changes to the sheet or copying. Learning how to use absolute references can be a great time-saver.

I'm curious about part of your formula though. Why are you concatenating C18 with just a blank space? If you are using the info somewhere else, it may make sense to add the space in that concatenate but it's a personal choice.

Upvotes: 0

Related Questions