Moving a value where two cells meet

Navman

New Member
Joined
Nov 22, 2005
Messages
3
Hi all,

The setup is as follows: In Sheet1 I have a list of ID's in column A (A2:A10)and in Row 1 (B1:G1) I have list of dates.

In sheet2 I have imported data from a text file where in column A it has some ID's, column B the date that ID was logged and column C the time it was logged. e.g -> In sheet2, cell A1 -> 12345, Cell B1 -> 22/11/05, Cell C1 -> 10:35.

Now the part I'm really stuck! How do I search sheet1 for an ID match and a date match from sheet2 (match for A1 and B1) and where both cells meet in sheet1 insert the third variable.

e.g. If cell A1 from Sheet2 matches cell A3 in Sheet1 and cell B1 matches cell G1 in Sheet1, Insert the time (C1) where both meet , which is G3. And add a loop so that it does this for all the imported data from Sheet2.

Hope this makes sense. I need to search for a match for two variables and insert the third (time) variable where both meet. Like a timetable.

Please, please help! If you want and e-mail of the workbook let me know. I need this ASAP.

Navman :confused:
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Suppose you name the ranges in sheet2 as IDs, Dates, and Times. Then, in sheet1 B2 enter the array formula =MAX((IDs=Sheet1!$A2)*(Dates=Sheet1!B$1)*Times) Note the use of absolute and relative addresses. Copy B2 as far across and down as needed.

An array formula is entered *not* by pressing the ENTER key but the combination of CTRL, SHIFT, and ENTER.
 

neofytosc

New Member
Joined
Nov 22, 2005
Messages
10
You may also try the Conditional Sum Wizard
=SUM(IF(date_range=B$1;IF(id_range=$A2;time_range;0);0))

and CTRL, SHIFT, and ENTER

Regards
 

Navman

New Member
Joined
Nov 22, 2005
Messages
3
THANK YOU VERY VERY VERY MUCH 'tusharm', it worked like a charm. Unfortunately 'neofytosc' I couldn't get yours to work even after renaming the IDs, Dates, Times ranges! But thanks anyway.

OK :biggrin: !

The thing is that I will continuously import data into the same columns in Sheet2. How do I keep the existing time values in Sheet1 and just append the new data imported i.e. Use Sheet2 as a buffer just to move data to Sheet1, while retaining the data values passed in Sheet1 each time. Or better still MOVE the actual time values rather than refer to them.
(Is there a better way to do what I'm trying to do!, since I'm making this for a novice that can't rename and define the ranges IDs, Dates, Times etc. each time he imports data from a text file, since the ranges get cleared?)

Also, code so that if there isn't an instance where an ID and Date have a time in Sheet2 that a default 'Absent' value is inserted/displayed and the cell also goes RED where both correspond in Sheet1. I'm thinking this may need to be done in VBA.

Much Appreciated.
Navman
 

Navman

New Member
Joined
Nov 22, 2005
Messages
3
I got neofytosc's code to work. It should be as follows

=SUM(IF(date_range=B$1,IF(id_range=$A2,time_range,0),0))

and CTRL, SHIFT, and ENTER

Neo basically used ; (semi-colons) instead of , (commas).

Regards
_________________

Navman
 

Forum statistics

Threads
1,078,094
Messages
5,338,193
Members
399,212
Latest member
Braincraft

Some videos you may like

This Week's Hot Topics

Top