reference fill

[buckfran]

I would like to fill down references in a column this way: =A1 =A2 =A3 consecutively. Is there a way to do this?

[NBVC]

Type =A1 in first cell, then select it and click and hold the little black square at bottom right corner of cell.. then drag down.

or you can copy that cell, select the remainder of the range you want to copy to and go to Edit|Fill|Down (or CTRL+D)

[buckfran]

sorry, i meant =A1 =A3 =A5 consecutively

[NBVC]

Try:

=INDEX(A:A,2*(ROWS($A$1:A1)-1)+1)

copied down.

This references your cells of interest.

[buckfran]

I could not make that work unfortunately. The cells that I wish to reference are currently blank, but will have values eventually as data is entered over time. The cells I wish to reference have blank spaces between them and that is why I need the formulas to change by two cells each time.

Thanks.

Buck

[NBVC]

Note: this formula assumes your starting reference is A1, if not, you will need to adjust the formula:

=INDEX(A:A,2*(ROWS($A$1:A1)-1)+1)

starts referencing at =A2 and continues to =A100, adjust range to encapsulate full range.

If you are referencing A1 to begin with and getting 0's as results, then chances are it is working, but it returns 0 for blanks.

you can do either of these:

Change formula to:

=IF(INDEX(A:A,2*(ROWS($A$1:A1)-1)+1)="","",INDEX(A:A,2*(ROWS($A$1:A1)-1)+1))

which will return blank if the referenced cell is blank.

You can go to Tools|Options and in the View tab, uncheck Zero Values (this hides all 0's in the sheet)

You can select the range and format the cells as: 0;-0;;@ which will hide zeroes only in the selected range.

[buckfran]

I was able to make your earlier suggestion work.

Thanks much.

Buck

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save