~Locating Cells to Begin Data Flow
 

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

~Locating Cells to Begin Data Flow
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"¤§Tarzan§¤" wrote:
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

~Locating Cells to Begin Data Flow
 

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" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"¤§Tarzan§¤" wrote:
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

~Locating Cells to Begin Data Flow
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On May 27, 10:23 am, ¤§Tarzan§¤
wrote:
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