~Locating Cells to Begin Data Flow

[Guest]

To further continue with the above:

If I had a Timeline, i would like to locate a cell adjacent to a particular
date in order to start a flow of data...is there a formula for that?

eg
scenario required: data = 4.5% from Aug-07
A B
1. May-07
2. Jun-07
3. Jul-07
4. Aug-07 4.5%
5. Sep-07 4.5%
6. Oct-07 4.5%

scenario2 required: data = $593,299 at Jun-07
A B
1. May-07
2. Jun-07 $593,299
3. Jul-07
4. Aug-07
5. Sep-07
6. Oct-07

Thanks heaps,


Dave

 

[Guest]

Scenario 1
Assume source data as posted in cols A to C,
with col B = 1st-of-month real dates, percentages in col C

If the lookup value is input in say, E1: Aug-07
then in F1: =INDEX(C:C,MATCH(E1,B:B,0))
will retrieve the value in col C corresponding to the lookup in E1,
ie 4.5% (with F1 formatted as percentage)

You could use the same formula for Scenario 2
except with F1 formatted as currency

 

[Guest]

Hey Max,

Thanks for helping out.

But what i need is for excel to locate the cell adjacent to the timeline and
enter the required percentage at the corresponding date. This would help to
produce large tables without having to enter data in manually.

So that I have an input section with the following information: 4.5% at/from
Aug-07
I require excel to find the date (Aug-07) along the timeline and enter 4.5%
adjacent to the relevant date.
A B
May-07
Jun-07
Jul-07
Aug-07 4.5%
Sep-07 4.5%
Oct-07 4.5%

To further complicate things, can the formula be expanded so that in three
months after the start date (Aug-07 + 3 months = Nov-07) another percentage
(5.7%) is entered:
May-07
Jun-07
Jul-07
Aug-07 4.5%
Sep-07 4.5%
Oct-07 4.5%
Nov-07 5.7%
Dec-07 5.7%

Thanx again for your attention

Dave

 

[Max]

Apologies, I probably read it the other way around earlier. Perhaps
just use Data > filter > autofilter on the dates col, filter out say;
Aug-07, then input as required? Otherwise, think you'd likely need a
sub to do the data population here. If nobody else jumps in here with
other views for you, suggest you put in a fresh posting in
excel.programming for insights from responders versed in vba. Good
luck.