Chnaging Cell reference through macro

[ashish128]

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

 

[Ardus Petus]

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

HTH

 

[Guest]

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]

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

 

[ashish128]

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$5,ROW(),0)


By all ways thank you again.

 

[ashish128]

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.

 

[JimMay]

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

=index(LookupRangewhereyourdatais,TheRow#youwant,TheColumn#youwant)
=match(TheNumberYou'reLookingFor,TheLookupRangeToLookin,MatchType)

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

HTH
Jim May

 

[ashish128]

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