searching and collating values in multiple sheets

[dave99]

Ok firstly in your responses (assuming there are any!) please conside
me a retard at least that way I'll be more familiar with the terminlog
and formulae used. This is going to be a pain to explain as I cant us
tables on this forum it seems so forgive the formatting

Also bear in mind im operating off a very locked system and I have n
net access there and a very cut down version of excel so I dont hav
access to plugins or some of the more advanced functions only availabl
on custom installs.

In excel how can I count a specific set of values and have it retur
them:

so there would be a user entry box, enter a name and it looks up al
the data associated with that name (which would be amount of account
opened in this case and accounts w/c opened both of which are simpl
numbers which would add up across a few worksheets representing days o
the week.)

The spreadsheet setup is currently this

Worksheets:

Sunday, monday, etc etc, saturday

on each worksheet is this topline below which a few 100 odd space
ready for the data that I input.

Accounts

WW LIN CW HOM F&B ACC MEN COS CS NAME

Accs w/c

WW LIN CW HOM F&B ACC MEN COS CS

All of this is in the same line on the sheet and the only usefu
information under each is a simple "1" this is the only value tha
appears in these columns apart from obviously the name of the person
There is always a "1" in one of the columns if a name is also entered
these are entered by me from the data im working off, it nice an
simply means NAME has opened 1 account the next box along is acc w/
and doesnt always have a value in, but if it does then that value wil
always match the same header in "accounts" and will also be a "1"

So what I'd want is nice and simply say I want to look up name "blah
then it will list

account acc w/c
blah 2 1

for example and it would list that if it found 2 accounts opened a
well as 1 acc w/c for that name.

More ideally I'd like a permanant output based of all the worksheet
which lists EVERYONE found and exactly how many accounts and acc w/
are attributed to them.

sorry thats all a bit of longwinded way of explaining, this is al
probably laughably easy to do but I would be very grateful if someon
could help as this would save me soooooo much time.

thanks

 

[pinmaster]

Can you give some more details. Like how many account columns do you
have, from which column to which column, how many rows, are there
specific columns you want to only look into if so which ones? Which row
is your Header row? Which columns do you have your names into? Things of
that nature. In the meantime have a look at the formula below, you might
be able to adapt it to your specifications.

=SUMPRODUCT((Sheet2!$A$2:$A$20=$A2)*(Sheet2!$B$1:$D$1<>""),Sheet2!$B$2:$D$20)
where:
Sheet2!$A$2:$A$20 in the range with the list of names in sheet 2
A2 is the cell with the name you want to lookup in your lookup sheet
Sheet2!$B$1:$D$1 is your header row with account names
Sheet2!$B$2:$B$20 is the range with your values

if you want to add the values from a specific column then you would use
something like

=SUMPRODUCT((Sheet2!$A$2:$A$20=A2)*(Sheet2!$B$1:$D$1="acc1"),Sheet2!$B$2:$D$20)
acc1 would be an account name in B1, C1 or D1 of Sheet 2

HTH
JG