Date + relative number reference

's Computer Specifications

Hi all

I have a spreadsheet with dates in row 3 going from today till the end of
the year i.e 26/apr/2010

In row 16 directly below the date i have a number i.e. 12

What i would like to do is: on another sheet, reference todays date i.e.
26/apr/2010 and display the number 12, but i would like this to happen when
the user opens the sheet automatically

In summary, tomorrow the 27/apr/2010 i would like the formula to look at
todays date and then display the numer that is 13 rows directly below it

Is this possible?

Thanks you for your help

Derek

's Computer Specifications

Try;

=HLOOKUP(TODAY(),16:17,2,FALSE)

--
Regards
Dave Hawley
www.ozgrid.com
"Derek M" <(E-Mail Removed)> wrote in message
news:98213C2A-E825-46E5-8197-(E-Mail Removed)...
> Hi all
>
> I have a spreadsheet with dates in row 3 going from today till the end of
> the year i.e 26/apr/2010
>
> In row 16 directly below the date i have a number i.e. 12
>
> What i would like to do is: on another sheet, reference todays date i.e.
> 26/apr/2010 and display the number 12, but i would like this to happen
> when
> the user opens the sheet automatically
>
> In summary, tomorrow the 27/apr/2010 i would like the formula to look at
> todays date and then display the numer that is 13 rows directly below it
>
> Is this possible?
>
> Thanks you for your help
>
> Derek

's Computer Specifications

Hi Derek

Try the below. Adjust the sheet name to suit

=INDEX(Sheet1!16:16,MATCH(TODAY(),Sheet1!3:3,0))

--
Jacob (MVP - Excel)

"Derek M" wrote:

> Hi all
>
> I have a spreadsheet with dates in row 3 going from today till the end of
> the year i.e 26/apr/2010
>
> In row 16 directly below the date i have a number i.e. 12
>
> What i would like to do is: on another sheet, reference todays date i.e.
> 26/apr/2010 and display the number 12, but i would like this to happen when
> the user opens the sheet automatically
>
> In summary, tomorrow the 27/apr/2010 i would like the formula to look at
> todays date and then display the numer that is 13 rows directly below it
>
> Is this possible?
>
> Thanks you for your help
>
> Derek

's Computer Specifications

Thats fantastic, thanks guys, got it to work using HLOOKUP

Regards - Derek

"Jacob Skaria" wrote:

> Hi Derek
>
> Try the below. Adjust the sheet name to suit
>
> =INDEX(Sheet1!16:16,MATCH(TODAY(),Sheet1!3:3,0))
>
> --
> Jacob (MVP - Excel)
>
>
> "Derek M" wrote:
>
> > Hi all
> >
> > I have a spreadsheet with dates in row 3 going from today till the end of
> > the year i.e 26/apr/2010
> >
> > In row 16 directly below the date i have a number i.e. 12
> >
> > What i would like to do is: on another sheet, reference todays date i.e.
> > 26/apr/2010 and display the number 12, but i would like this to happen when
> > the user opens the sheet automatically
> >
> > In summary, tomorrow the 27/apr/2010 i would like the formula to look at
> > todays date and then display the numer that is 13 rows directly below it
> >
> > Is this possible?
> >
> > Thanks you for your help
> >
> > Derek

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
HLOOKUP - Relative cell reference in row index number	Nick	Microsoft Excel Misc	0	13th Nov 2009 12:50 PM
Using an offset formula for the reference in a relative reference	=?Utf-8?B?Q3VkYQ==?=	Microsoft Excel Worksheet Functions	6	15th Nov 2006 05:12 PM
Getpivot date w/ relative reference	=?Utf-8?B?U3BpZGV5?=	Microsoft Excel Worksheet Functions	8	14th Nov 2006 04:34 PM
editing a reference number to reflect date raised.	mcmillad	Microsoft Excel Discussion	2	7th Jun 2006 10:41 AM
How do I extract a date as text not the 1900 reference number	=?Utf-8?B?QWRhbQ==?=	Microsoft Excel Misc	3	23rd Mar 2005 05:04 PM