vlookup? what adam did on the 11/11/05?!

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
Adam---10/11/05---12
Adam---11/11/05---6
Adam---12/11/05---4
Adam---13/11/05---3
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

Many thanks in advance
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

In B3 enter the formula
Code:
=SUMPRODUCT(--(Sheet1!$A$1:$A$11=$A$1),--(Sheet1!$B$1:$B$11=A3),(Sheet1!$C$1:$C$11))

Adjust the ranges in Sheet1 to suit. Copy down as required.


HTH

Tony
 

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
That is awesome little tweaking and it works a treat! Out of interest what does the -- do? Think I understand how it works otherwise :)

Thank you you've really helped me out!
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

The -- converts the boolean value of True / False to a number. The first - will make it negative, the second will convert it back to positive. True will come out as 1 and False as 0.


Tony
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Sethrow said:
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
Adam---10/11/05---12
Adam---11/11/05---6
Adam---12/11/05---4
Adam---13/11/05---3
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

Many thanks in advance

See,

http://www.mrexcel.com/board2/viewtopic.php?t=179640&start=10
 

Watch MrExcel Video

Forum statistics

Threads
1,118,058
Messages
5,569,957
Members
412,300
Latest member
Chaneycr
Top