# Excel Formula: Looking for help with basic cell referencing

#### nwfunk

##### New Member
Hi,

I'm looking for help with a cell reference formula in excel. These are the facts:

Cells A1:A5 contains the following numbers, starting at A1 and moving downwards; 1, 2, 3, 4, 5

I want to copy these cells to B1:B5.

This is where it gets tricky..

I then want to fill cells C2:C6 with the same numbers (1 through 5) for n number of columns such that each time I fill to the next column on the right, the first number "1" starts 1 row down.

I'm looking for a formula that would do this in Excel, not VBA code.

Thanks very much!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I think, although I'm not sure of the purpose, that this formula may do what you're looking for: =IF(B2=1,"",IF(AND(B2="",B1=5),5,IF(B2="","",B2-1)))
This assumes that A1:A5 = 1,2,3,4,5 and that B1:B5 = 1,2,3,4,5

Paste the formula into C2, then copy to wherever you want. Let me know if this doesn't work or if you have questions.

I think, although I'm not sure of the purpose, that this formula may do what you're looking for: =IF(B2=1,"",IF(AND(B2="",B1=5),5,IF(B2="","",B2-1)))
This assumes that A1:A5 = 1,2,3,4,5 and that B1:B5 = 1,2,3,4,5

Paste the formula into C2, then copy to wherever you want. Let me know if this doesn't work or if you have questions.

Hey Thanks very much, this formula does the trick!

Alternatively, if you add column headers in row 1 so B2:B6 = 1,2,3,4,5 you can use the following formula in C2, copied down and to the right, whereever required.
Code:
``=IF(SUM(B1)=0,"",SUM(B1))``
Remark: I use SUM because SUM of a text field will return 0. So column headers can be anything except numbers <> 0.

Last edited:
Ok, now can we make this a little more complicated?

Imagine now that we have copied the formula down and over to column O. So what you would see is that O14:O18 = 1,2,3,4,5.

Now insert a row at row 1, such that everything is shifted down 1 row. And place dates across row one such that A1:O1 = 30-Apr-15... and so on to 30-Jun-16

Also, insert dates in cells S1:S2 = 31-Oct-15, 31-Mar-16.

Here is the concept. The formula that you created now has to only show numbers (referencing cells in column A) within the timeframe outlined in cells S1:S2. For example, if we go with the timeframe of 31-Oct-15 to 31-Mar-2015, then we will see G7:G11 = 1,2,3,4,5... and so on until L12:L16 = 1,2,3,4,5.

Does that makes sense?

Thanks,
Nathan.

Assuming that S1 is always the earlier date and T1 is always the later date, then put this formula in cell C3 (which used to be C2 before inserting rows): =IF(C\$1<\$S\$1,"",IF(C\$1>\$T\$1,"",IF(OR((ROW()-(COLUMN()-1))<1,(ROW()-(COLUMN()-1))>5),"",ROW()-(COLUMN()-1))))
Copy to the desired range.

Let me know if this doesn't work or get you what you need.

Edit: The above formula can also be copied to column B. However, column A doesn't work because A2 returns "2" (there's no room for "1"). If you want column A to show/hide with the date selection, put: =IF(A\$1<\$S\$1,"",IF(A\$1>\$T\$1,"",IF(OR((ROW()-(COLUMN()-0))<1,(ROW()-(COLUMN()-1))>6),"",ROW()-(COLUMN()-0)))) in column A only.

If I understand it correctly, if your date range went from 4/30/15 to 6/30/15, cells A2:A6 and B2:B6 are both 1,2,3,4,5, and the stairstep effect begins with column C.

 4/30/2015 5/31/2015 6/30/2015 7/31/2015 8/31/2015 9/30/2015 10/31/2015 11/30/2015 12/31/2015 1/31/2016 2/29/2016 3/31/2016 4/30/2016 5/31/2016 6/30/2016 1 1 2 2 1 3 3 2 1 4 4 3 2 1 5 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 1 5 4 3 2 5 4 3 5 4 5

<tbody>
</tbody>

Last edited:
Yes this works great, thanks very much!

Replies
6
Views
707
Replies
3
Views
273
Replies
3
Views
497
Replies
24
Views
689
Replies
10
Views
853

### Forum statistics

1,196,052
Messages
6,013,115
Members
441,748
Latest member
MrBigglesworth ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back