Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User cheshire_man
(knowledge is power) Wed 01-Feb-12 22:41:22
Print Post

Another Excel query


[link to this post]
 
I have a spreadhseet with a column of formulae of the form

=sheet1!A49
=sheet1!A80
=sheet1!A109
=sheet1!A140
etc.

Each of the increment row references (49 to 80 to 109 to 140 in the example) have to be hand entered as they depend on the number of days in a particular month.

I'd like to create a formula of the form

=sheet1!A[cell above + number of days in month in the date that is in cell A of the current row]

Is there any way of achieving such a relative effect?

Tony
Standard User philippercival
(committed) Wed 01-Feb-12 23:55:51
Print Post

Re: Another Excel query


[re: cheshire_man] [link to this post]
 
Assuming the first date is in A4, then if you put the following in A5

=IF(A4+1<=EOMONTH(A4,0),A4+1,"")

And copy this down column A, it will carry on producing dates till the end if the month.

Example here

If the formula got a little more cunning, it could leave a couple of blank lines and then start the next month, but I am not sure I quite understand your requirement.
Standard User XRaySpeX
(eat-sleep-adslguide) Thu 02-Feb-12 02:58:46
Print Post

Re: Another Excel query


[re: cheshire_man] [link to this post]
 
Following on from philippercival it's something more involved than:
=A4+DAY(EOMONTH(A4,0))
but I haven't time to go into it now, but it involves manipulation of cell references.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC


Register (or login) on our website and you will not see this ad.

Standard User philippercival
(committed) Thu 02-Feb-12 12:12:47
Print Post

Re: Another Excel query


[re: XRaySpeX] [link to this post]
 
Yes, I am afraid I do not quite understand the problem. However to obtain number of days in month in the date that is in cell A of the current row, you can use

=DAY(EOMONTH(A4,0)) (assumes Row 4)
Standard User cheshire_man
(knowledge is power) Thu 02-Feb-12 12:24:47
Print Post

Re: Another Excel query


[re: philippercival] [link to this post]
 
I've no problem in finding the number of days in a month. The problem (minor in the grand scheme of things, but there you go smile) is using that value to change the row reference in a formula. That seems to require some sort of dynamic formula reconstruction or some sort of dynamic referencing.

Tony
Standard User philippercival
(committed) Thu 02-Feb-12 12:33:08
Print Post

Re: Another Excel query


[re: cheshire_man] [link to this post]
 
I think it is a variation of the INDIRECT function that you want

see the Microsoft example
Standard User XRaySpeX
(eat-sleep-adslguide) Thu 02-Feb-12 16:12:36
Print Post

Re: Another Excel query


[re: cheshire_man] [link to this post]
 
Having now had time to experiment, I come up with this:

Using A1:C12 block for example:

A1: 01/01/12
A2: =A1+DAY(EOMONTH(A1,0)) and filled down
B1: A49 REM your starting point, Note no "="
B2: =ADDRESS(ROW(INDIRECT(B1))+A2-A1,COLUMN(INDIRECT(B1)),4) and filled down
C1: =INDIRECT(B1) and filled down

Then Col C is what you are after, I think smile

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
  Print Thread

Jump to