Excel Formula: Looking for help with basic cell referencing

[nwfunk]

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!

 

[elmer007]

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.

 

[nwfunk]

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!

 

[MarcelBeug]

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.

=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.

 

[nwfunk]

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.

 

[elmer007]

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>

 

[nwfunk]

Yes this works great, thanks very much!