how to insert a collum on a sheet without it changing cell referen

J

Just Me

Its difficult to explain but I have this formula on a sheet:
=COUNTIF(Numbers!$A3:Numbers!AE3,1)
I want to be able to insert a collum on the Numbers sheet and put in new
data there without the cell reference $A3 changing to $B3
 
J

Just Me

I would like to insert the collumn at the first collumn, the data I insert
has a date then other numbers below that, I need to grab the date for 1
calculation then the numbers for others. I would have a simular problem with
the date if it is not in A1
 
M

Max

INDIRECT will allow you to always point to the fixed range impervious to
future col insertions/deletions:
=COUNTIF(INDIRECT("'Numbers'!A3:AE3"),1)
The range string: 'Numbers'!A3:AE3 within INDIRECT is just a text string, so
you don't need to use any $ signs. Any worth? hit the YES below.
 
J

Just Me

OK i will experiment with:
=COUNTIF(INDIRECT("'Numbers'!A3":AE3),1)
because the :AE3 part of the range needs to change as I populate the Numbers
sheet.
 
M

Max

If that's the case, maybe just grab the entire row 3 then?
=COUNTIF(INDIRECT("'Numbers'!3:3"),1)
 
M

Max

Sorry, I got carried away earlier. If grabbing the entire row 3, no need for
INDIRECT, just: =COUNTIF(Numbers!3:3,1)
 
J

Just Me

I would like to thank you both for helping me with my problem, I learned i
could insert the second column then manipulate the numbers and the page would
work without changing the cell addresses. But the real trick was to only call
for the row number range and not the collum at all so the collum would not
update:
=COUNTIF(Numbers!3:3,1)
and the place where I was calling the date just changed to:
=INDIRECT("'Numbers'!A1")
that way the cell reference does not change when I insert a collumn on the
numbers sheet and will show the proper date once the data has been pasted
onto the cells.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top