=$A$1:$A$12 Explain?

**brucemc** · 09-06-2009, 09:27 PM

When =$A$1:$A$12 is in cells B1 through B12 and refers to numbers in cells A1 through A12, it gives a numerical result of whatever is in the corresponding cell. What is going on I must of known at one time, but I am going crazy over trying to remember, and why the heck the author of what I am looking at didn't simply start with cell B1, write =A1 in it, and dragging that down (It is a bit more complicated than that as it is the first "value" in a vlookup, but...

Can anyone help jump-start my brain and let me know what and why one would use this kind of reference instead of my simpler afore cited reference?

**martindwilson** · 09-06-2009, 09:30 PM

whats the whole formula then?

**NBVC** · 09-06-2009, 09:38 PM

It is entered as an array formula.. i.e. there are { } brackets around it...correct?

This was entered by selecting B1:B12 and entering A1:A12 in the formula bar and then confirming it with CTRL+SHIFT+ENTER...

There is no real reason to do this, that I can tell, except perhaps using it as a sort of cell protection.. if you try to change anything in one of those cells containing the formula it will give you an error message and not allow change..

**brucemc** · 09-06-2009, 09:47 PM

The entire formula simply used that styled reference as the first argument in Vlookup.

When I entered exactly that form of argument as an equation, that is:
=$A$1:$A$12 into cells B1 through B12, B1 through B12 mirrored what I had in A1 through A12, so if the numbers 1 through 12 were in A1 through B12, they also then appeared in B1 through B12. I could have done that with putting in B1 :
=A1
and then dragging B1 down to B12, so I don't know why the heck the reference was made in this manner! The entire equation (which I see what I think are other flaws in) is:
=IF(ISNA(H4-(VLOOKUP($F$4:$F$38,$N$30:$Q$33,4,TRUE)*H4)),"",H4-(VLOOKUP($F$4:$F$38,$N$30:$Q$33,4,TRUE)*H4))

If you isolate the first relevant portion you have:
VLOOKUP($F$4:$F$38,$N$30:$Q$33,4,TRUE)

and so $F$4:$F$38 is the portion I recreated into $A$1:$A$12, referring to the range for the first value, $N$30:$Q$33 is the table for the lookup and we are shooting for the 4th column with closest match.

**brucemc** · 09-06-2009, 09:53 PM

Array was what I first suspected, but I verified that it is not! Try it yourself - just number A1 through A12 1 to 12 (or whatever you like) and enter =$A$1:$A$12 in B1, and drag it down to B12

Everything in A1 through A12 will be shown in B1 through B12 just as if in B1 you wrote =A1 and dragged that down to B12.

**NBVC** · 09-06-2009, 09:57 PM

Originally Posted by brucemc

Array was what I first suspected, but I verified that it is not! Try it yourself - just number A1 through A12 1 to 12 (or whatever you like) and enter =$A$1:$A$12 in B1, and drag it down to B12

Everything in A1 through A12 will be shown in B1 through B12 just as if in B1 you wrote =A1 and dragged that down to B12.

That was a guess, because I suspected that was the use..... but not sure why the user used that referencing style.. maybe just his/her preferred method.... Referencing the one cell and copying down is better as the original method won't let you go past the referencing of cell F38.. I.E. if you intend copying the formula down far enough to reference F39, etc.. you will get errors...

**brucemc** · 09-06-2009, 10:16 PM

Agreed, so you have to insert and then fill in, so why the heck would someone do that???

**NBVC** · 09-06-2009, 10:19 PM

Is that person an experienced Excel user? Perhaps, they were just trying to create a vlookup formula and decided to try it and it worked (or they think it worked)... best to ask that person... I have never come across that usage...