Hi Everyone, I have two sheets in my excel file. First Sheet goes like
this
1/1/2006 1/2/2006 1/3/2006
Bill 10 30 50
Harry 20 40 60
Sam 30 50 70
Joshua 40 60 80

Second Sheet has formating like

Day <user will give input here in form of date OR the macro will ask
Bill <value of bill for that date
Harry <value of Harry for that date
Sam <value of Sam for that date
Joshua <value of Joshua for that date

Is there any formula or macro which will ask me for a date and will
insert the required values in sheet 2

=HLOOKUP(B$1,Sheet1!B$1:D$5,ROW(),0)

HTH
--
AP

"ashish128" a écrit dans le message de
oups.com...
Hi Everyone, I have two sheets in my excel file. First Sheet goes like
this
1/1/2006 1/2/2006 1/3/2006
Bill 10 30 50
Harry 20 40 60
Sam 30 50 70
Joshua 40 60 80

Second Sheet has formating like

Day <user will give input here in form of date OR the macro will ask
Bill <value of bill for that date
Harry <value of Harry for that date
Sam <value of Sam for that date
Joshua <value of Joshua for that date

Is there any formula or macro which will ask me for a date and will
insert the required values in sheet 2

Let Cell B1 in your sheet2 be your "input cell";
In Cell B2 enter:

=INDEX(Sheet1!$B$2:$D$5,MATCH(Sheet2!A2,Sheet1!$A$ 2:$A$5,0),MATCH(Sheet2!B$1,Sheet1!$B$1:$D$1,0)) <<watch wrapping here,,
and copy down to B5.
HTH


"ashish128" wrote:

Hi Everyone, I have two sheets in my excel file. First Sheet goes like
this
1/1/2006 1/2/2006 1/3/2006
Bill 10 30 50
Harry 20 40 60
Sam 30 50 70
Joshua 40 60 80

Second Sheet has formating like

Day <user will give input here in form of date OR the macro will ask
Bill <value of bill for that date
Harry <value of Harry for that date
Sam <value of Sam for that date
Joshua <value of Joshua for that date

Is there any formula or macro which will ask me for a date and will
insert the required values in sheet 2

Thanks Ardus, It Worked, I am really grateful to you.
I also wondered if you would have like to receive such thanks on your
mail or on web post. I prefer web post so that other user can see that
your suggestion worked.
Ashish
Ardus Petus wrote:
=HLOOKUP(B$1,Sheet1!B$1:D$5,ROW(),0)

HTH
--
AP

"ashish128" a écrit dans le message de
oups.com...
Hi Everyone, I have two sheets in my excel file. First Sheet goes like
this
1/1/2006 1/2/2006 1/3/2006
Bill 10 30 50
Harry 20 40 60
Sam 30 50 70
Joshua 40 60 80

Second Sheet has formating like

Day <user will give input here in form of date OR the macro will ask
Bill <value of bill for that date
Harry <value of Harry for that date
Sam <value of Sam for that date
Joshua <value of Joshua for that date

Is there any formula or macro which will ask me for a date and will
insert the required values in sheet 2

Thanks Jim,
your code worked for me . Though it is a bit complex but i will try to
understand it. Also What is the difference beyween using your code and
using Ardus Suggestion which is--- =HLOOKUP(B$1,Sheet1!B$1:D$5,ROW(),0)


By all ways thank you again.

Hi Ardus,
I faced a little problem in customizing this formula. Actually
my data Sheet 1 is as is but I need to change my sheet 2 as

Day <user will give input here in form of date
Bill <value of bill for that date Harry
<value of Harry for that date
-----------Empty Row--------------
-----------Empty Row--------------
Sam <value of Sam for that date Joshua <value of
Joshua for that date
-----------Empty Row--------------
-----------Empty Row--------------

Actually I want to use this formula anywhere on my sheet 2 and not in
sequence.

My approach is still another popular way to extract values from another
region of a workbook or worksheet;

=index(LookupRangewhereyourdatais,TheRow#youwant,T heColumn#youwant)
=match(TheNumberYou'reLookingFor,TheLookupRangeToL ookin,MatchType)

See Both Index() and Match() in Help - Powerful tools, if you know
How to use..

HTH
Jim May


"ashish128" wrote in message
ups.com:

Thanks Jim,
your code worked for me . Though it is a bit complex but i will try to
understand it. Also What is the difference beyween using your code and
using Ardus Suggestion which is--- =HLOOKUP(B$1,Sheet1!B$1:D$5,ROW(),0)


By all ways thank you again.

Hi Ardus,
I understood this formula and i have accomplished my task
sucessfully. Thanks to you