Macro lookup
 

hey all, yeh basically i have two worksheets, one with a row of data
(we'll call this data for now) that needs to be special pasted into the
other worksheet (week totals). on the week totals worksheet there is a
list of weeks, on the data worksheet there is a date. i need the data
from the data worksheet to be special pasted (i need only the values)
in the row on week totals which the date matches. if that doesn't make
sense i'll try and explain it again :)


--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

Macro lookup
 

Why not use =vlookup() or =index(match()) and then change the formulas to
values?

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

dudemango wrote:

hey all, yeh basically i have two worksheets, one with a row of data
(we'll call this data for now) that needs to be special pasted into the
other worksheet (week totals). on the week totals worksheet there is a
list of weeks, on the data worksheet there is a date. i need the data
from the data worksheet to be special pasted (i need only the values)
in the row on week totals which the date matches. if that doesn't make
sense i'll try and explain it again :)

--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

--

Dave Peterson

Macro lookup
 

yeh i thought and tried that, but i couldn't find a way to select the
row where the two dates match, i had considered using an IF statement
but i can't think think how to implement that. sorry to sound
unproffesional, but whats this index thing? because ive never actually
used it lol


--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

Macro lookup
 

Debra Dalgleish's site explains =index(match()) in detail.

But it's very useful when you want to do something very close to =vlookup(), but
the column to match up on is the left most in the range.

But reading your response makes it sound like this won't work. You may want to
expand on your explanation of the problem.

Maybe it'll help with a better response.

dudemango wrote:

yeh i thought and tried that, but i couldn't find a way to select the
row where the two dates match, i had considered using an IF statement
but i can't think think how to implement that. sorry to sound
unproffesional, but whats this index thing? because ive never actually
used it lol

--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

--

Dave Peterson

Macro lookup
 

right, hopefully these screenprints can help explain it.
screen1 shows a list of dates
screen 2 shows the data
in screen2 you will see there is a changable date, this changable date
corresponds to the list of dates in screen1. i need a way to
automatically select the cells to the right of the date that matches
the date given in screen 2.
e.g. say the date 20th of february is given in screen 2
i would need to press a macro button and it will select the cells to
the right of the cell in sheet1 containing 20th of february.
i also need to paste the two sets of data given on screen2 into the
appropriate cells on screen1. however these values must be pasted as
the same cells will be used again when set to a different date and may
have different values.
i did think of using a macro to change the cell to this formula
"=IF(A3=Sheet2!$B$2,Sheet2!C4)" then copying the value and special
pasteing it back into the same cell, but that means i would still have
to select the right cell to put it into. basically what im trying to do
is use vlookup to select a cell, not return a value. hope this helps :)


+-------------------------------------------------------------------+
|Filename: screen2.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4328 |
+-------------------------------------------------------------------+

--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

Macro lookup
 

I connect directly to the newsgroups--I don't go through excelforum. And I
don't open attachments, either.

You can try again in plain text or maybe someone else reading from excelforum
can jump in.

dudemango wrote:

right, hopefully these screenprints can help explain it.
screen1 shows a list of dates
screen 2 shows the data
in screen2 you will see there is a changable date, this changable date
corresponds to the list of dates in screen1. i need a way to
automatically select the cells to the right of the date that matches
the date given in screen 2.
e.g. say the date 20th of february is given in screen 2
i would need to press a macro button and it will select the cells to
the right of the cell in sheet1 containing 20th of february.
i also need to paste the two sets of data given on screen2 into the
appropriate cells on screen1. however these values must be pasted as
the same cells will be used again when set to a different date and may
have different values.
i did think of using a macro to change the cell to this formula
"=IF(A3=Sheet2!$B$2,Sheet2!C4)" then copying the value and special
pasteing it back into the same cell, but that means i would still have
to select the right cell to put it into. basically what im trying to do
is use vlookup to select a cell, not return a value. hope this helps :)

+-------------------------------------------------------------------+
|Filename: screen2.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4328 |
+-------------------------------------------------------------------+

--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998

--

Dave Peterson

Macro lookup
 

<sheet 1
Week Begining X TITLE X VALUE <- these are the column headings
16 January 2006
23 January 2006
30 January 2006
06 February 2006
13 February 2006

<sheet 2
06 February 2006 <- this date is changed using a spinner
1st column
TITLE
VALUE
2nd column
X TITLE
£200.00
these values in the 2nd column need to be pasted into the row in sheet
1 that contains the date given above in sheet 2

sorry if this is not accurate enough


--
dudemango
------------------------------------------------------------------------
dudemango's Profile: http://www.excelforum.com/member.php...o&userid=31137
View this thread: http://www.excelforum.com/showthread...hreadid=507998