Relative reference won't autofill.

[dwilson31415]

I'm sure this is a common question but I have yet to be able to solve it even with searching the forums.

The goal is to pull a date from the sheet 'Tracker' see if the date has passed or if it occurs within the next month. If true it needs to print an 'X' in the appropriate cell on the sheet 'Hitlist'

I have the formula to do just that but the issue is I cannot use the autofill function even when I place in relative references. I'll need the same formula for thousands of cells but I cannot get the references to fill.

Below is my formula:

=IF(OR(Tracker!$D10<=TODAY(),Tracker!$D10=EDATE(NOW(),1)), "X ", " ")

From here I need it to autofill to Tracker!D11 in both references and so on


Thanks in advance.

 

[RoryA]

Are you filling down or right? (Right won't work, but down should).

 

[dwilson31415]

If I fill downwards it will only repeat the references I entered to help establish the pattern $d10,$d11,$d12,$d10,$d11.... no matter how many I use to establish a pattern.

 

[Jonmo1]

I'm not sure I understand what you mean by "to help establish the pattern"

Are you highlighting 3 cells, then filling down?

You should only highlight 1 single formula (the first one) and fill down.

 

[dwilson31415]

Thats what I thought too, but if I do that then it will only copy the same references. $d10,$d10,$d10.....

 

[Jonmo1]

Perhaps your formula is entered in the cell as Text.

When you enter the formula the first time, do you see the result of the formula, or just the formula ?

Make sure the cell is formatted as General (or number, anything other than text)
Then RE-Enter the formula
Then do the fill down.

 

[dwilson31415]

Well that must have worked they are proceeding as expected. Excellent idea Jonmo1, I inherited this project secondhand and would've never thought that would've happened.

You're a lifesaver.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[RoryA]

What happens if you copy and paste the first formula cell? Or select that cell and the ones below it, press f2 then Ctrl+Enter?