Find a column and return values

**jvarian** · 11-03-2008, 08:31 AM

Hi there,
Im a newbie.
I have an issue with a function that i was hoping you guys could help with.

I have a row with for example everyday of the month starting with the 1st and going to the last.

First of November 08
The month is like the following 01/11/2008

What i need to do is get the last day of the month
EG A10=01/11/2008
A1=Month(A10)
A2=DATE(YEAR(A1),MONTH(A1)+1,0) This is the last day of the month.

Now is where your expertise comes in!!!
Now i need to search the row with all the dates until i find the 30/11/08
I need to find the column where the value in A10 to A40 is equal to A2
The month will vary so the column will change.
When i have found the column the value is in i need to copy all the values from that column to another location.

Hope you guys can help me with this.

**DonkeyOte** · 11-03-2008, 09:10 AM

jvarian, your example is not clear as you mention columns but then state that dates are in A10:A40 which are rows.

If your dates are in sequential order and only store dates for that month then it should be pretty easy to identify the row, no ?

=10+(DAY(A2)-1)

It may be best to post up a small sample file so we can physically see your layout.

**jvarian** · 11-03-2008, 09:30 AM

Sorry
Gettin confused, I have uploaded an example

F1=01/11/2008
AI=30/11/2008

I know that the first day of the month will always be in F1
So A1=F1
C2=DATE(YEAR(A1),MONTH(A1)+1,0)

C2 is now 30/11/2008
I need to search from F1 to AI to find the column that 30/11/2008 is in
I then need to get all the values from that column and place them in A23 to A42
So i need to get the values from AI2 to AI21 and copy them to A23 to A42

The column position of the last day of the month changes every .month