Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sue
 
Posts: n/a
Default Does excel recognise names rather than cells?

Can excel pick up totals on other sheets and workbooks for clients, where the
clients information is never on the same row no. on each sheet, without it
being a lengthy case of having to alter the formula to accomodate the cell
change address? In other words how do I get it to recognise the name rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from clients
and the total. On a summary sheet I want excel to pick up those 1/3rd totals
for each client so I can then total those to give the yearly figure for each
client. The reason the cell address is not consistent on each 1/3rd period
sheet is because each client's time span with us is only ever a maximum of 2
years but usually less. So that means the current clients move up the row on
the sheet as other clients finish hence the different row numbers.
  #2   Report Post  
Ashish Mathur
 
Posts: n/a
Default

Hi,

You may use the index function coupled with the match function to accomplish
this

Index(entire range,match(clint name,range of client names,0),column number
of amounts)

Regards,

Ashish Mathur

"Sue" wrote:

Can excel pick up totals on other sheets and workbooks for clients, where the
clients information is never on the same row no. on each sheet, without it
being a lengthy case of having to alter the formula to accomodate the cell
change address? In other words how do I get it to recognise the name rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from clients
and the total. On a summary sheet I want excel to pick up those 1/3rd totals
for each client so I can then total those to give the yearly figure for each
client. The reason the cell address is not consistent on each 1/3rd period
sheet is because each client's time span with us is only ever a maximum of 2
years but usually less. So that means the current clients move up the row on
the sheet as other clients finish hence the different row numbers.

  #3   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients, where
the
clients information is never on the same row no. on each sheet, without it
being a lengthy case of having to alter the formula to accomodate the cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum of
2
years but usually less. So that means the current clients move up the row
on
the sheet as other clients finish hence the different row numbers.



  #4   Report Post  
Sue
 
Posts: n/a
Default

Hi,
I am still having problems. I have tried both suggestions and I cannot seem
to get either one to work. Both come up with incorrect formula. I am wanting
my summary sheet to show each of the 1/3rd totals for each client and then I
will sum those figures. Summary sheet will contain 6 columns as follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end of the
year or not. The sheets 1 - 4 show only clients that are active during that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients, where
the
clients information is never on the same row no. on each sheet, without it
being a lengthy case of having to alter the formula to accomodate the cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum of
2
years but usually less. So that means the current clients move up the row
on
the sheet as other clients finish hence the different row numbers.




  #5   Report Post  
Biff
 
Posts: n/a
Default

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd


Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I cannot
seem
to get either one to work. Both come up with incorrect formula. I am
wanting
my summary sheet to show each of the 1/3rd totals for each client and then
I
will sum those figures. Summary sheet will contain 6 columns as follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end of
the
year or not. The sheets 1 - 4 show only clients that are active during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients,
where
the
clients information is never on the same row no. on each sheet, without
it
being a lengthy case of having to alter the formula to accomodate the
cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum
of
2
years but usually less. So that means the current clients move up the
row
on
the sheet as other clients finish hence the different row numbers.








  #6   Report Post  
Sue
 
Posts: n/a
Default

Hi Biff,
I have ended up putting the scenario also under Live Meeting titled Excel
Worksheet Functions dated 15/05/05. I have given a more complete example of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in column
B and the total for the 1/3rd periods worth of payments is in column M.
I want my summary sheet no. 5 to have six columns, being (A) for the clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd


Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I cannot
seem
to get either one to work. Both come up with incorrect formula. I am
wanting
my summary sheet to show each of the 1/3rd totals for each client and then
I
will sum those figures. Summary sheet will contain 6 columns as follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end of
the
year or not. The sheets 1 - 4 show only clients that are active during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients,
where
the
clients information is never on the same row no. on each sheet, without
it
being a lengthy case of having to alter the formula to accomodate the
cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum
of
2
years but usually less. So that means the current clients move up the
row
on
the sheet as other clients finish hence the different row numbers.






  #7   Report Post  
Biff
 
Posts: n/a
Default

Hi!

I'm a little confused but it's not a problem!

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd


Sheets 1 - 4


So, are the *ACTUAL* sheet names Sheet1, Sheet2, Sheet3 and Sheet4?

Or, is the first sheet in the workbook named 1st 3rd, the second sheet named
2nd 3rd, etc.?

I based my formulas on the sheet names being Sheet1, Sheet2, etc.

Assume on your Summary sheet:

A1:F1 are the column headers:

Client
1st 3rd
2nd 3rd
3rd 3rd
4th 3rd
Total

A2:An = client names

In B2 enter any one of these formulas and copy across to E2 then down as
needed:

=VLOOKUP($A2,INDIRECT("sheet"&COLUMN(A1)&"!B:M"),1 2,0)

=SUMIF(INDIRECT("sheet"&COLUMN(A1)&"!B:B"),$A2,IND IRECT("sheet"&COLUMN(A1)&"!M:M"))

=INDEX(INDIRECT("sheet"&COLUMN(A1)&"!M:M"),MATCH($ A2,INDIRECT("sheet"&COLUMN(A1)&"!B:B"),0))

Biff

"Sue" wrote in message
...
Hi Biff,
I have ended up putting the scenario also under Live Meeting titled Excel
Worksheet Functions dated 15/05/05. I have given a more complete example
of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in
column
B and the total for the 1/3rd periods worth of payments is in column M.
I want my summary sheet no. 5 to have six columns, being (A) for the
clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the
third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd


Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in
column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I cannot
seem
to get either one to work. Both come up with incorrect formula. I am
wanting
my summary sheet to show each of the 1/3rd totals for each client and
then
I
will sum those figures. Summary sheet will contain 6 columns as
follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for
the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end
of
the
year or not. The sheets 1 - 4 show only clients that are active during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of
your
sheets. All the sheets have the identical format in that client names
are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients,
where
the
clients information is never on the same row no. on each sheet,
without
it
being a lengthy case of having to alter the formula to accomodate
the
cell
change address? In other words how do I get it to recognise the
name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those
1/3rd
totals
for each client so I can then total those to give the yearly figure
for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a
maximum
of
2
years but usually less. So that means the current clients move up
the
row
on
the sheet as other clients finish hence the different row numbers.








  #8   Report Post  
Sue
 
Posts: n/a
Default

Hi Biff,
I am finally getting at least a figure showing on my summary sheet instead
of error messages but it is not picking up the right figures.
My sheet names are for example "Term 1", "Term 1 Page 2", "Term 2", "Term 2
Page 2", "Term 3", Term 3 Page 2", "Term 4", "Term 4 Page 2 and "Summary
Sheet".
On my "Term Sheets" the clients names are in column B running down from row
3 to row 39, and the total payments column for the term is column M, running
down from rows 3 - 39. Also does it matter that the totals in column M have
a sum calculation sourced from colums E - L, rows 3 - 39?
My "Summary Sheet" for the year has client names in column A. The Term
Totals are to be in columns B - E inclusive and the total for the year is in
column F. The idea is to get the worksheet to read the name in column A for
each row on the summary sheet and then search through the Term 1 sheets (for
example being Term 1 and Term 1 page 2 in this scenario) and find that same
name (in other words match it). Once matched, to then travel across the row
to locate the term total in column M and insert that figure only, into my
summary sheet in colum B. The same process would take place for Summary
sheet column C looking in Term 2 and Term 2 Page 2 sheets and column D
looking in sheets Term 3 and Term 3 page 2 etc.
Have I made it any clearer?
I know it sounds complicated but in reality it is straightforward. It's my
explaining perhaps. I don't know.
I hope it can be done as above. Is it possible to be done that way and how?


"Biff" wrote:

Hi!

I'm a little confused but it's not a problem!

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd


Sheets 1 - 4


So, are the *ACTUAL* sheet names Sheet1, Sheet2, Sheet3 and Sheet4?

Or, is the first sheet in the workbook named 1st 3rd, the second sheet named
2nd 3rd, etc.?

I based my formulas on the sheet names being Sheet1, Sheet2, etc.

Assume on your Summary sheet:

A1:F1 are the column headers:

Client
1st 3rd
2nd 3rd
3rd 3rd
4th 3rd
Total

A2:An = client names

In B2 enter any one of these formulas and copy across to E2 then down as
needed:

=VLOOKUP($A2,INDIRECT("sheet"&COLUMN(A1)&"!B:M"),1 2,0)

=SUMIF(INDIRECT("sheet"&COLUMN(A1)&"!B:B"),$A2,IND IRECT("sheet"&COLUMN(A1)&"!M:M"))

=INDEX(INDIRECT("sheet"&COLUMN(A1)&"!M:M"),MATCH($ A2,INDIRECT("sheet"&COLUMN(A1)&"!B:B"),0))

Biff

"Sue" wrote in message
...
Hi Biff,
I have ended up putting the scenario also under Live Meeting titled Excel
Worksheet Functions dated 15/05/05. I have given a more complete example
of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in
column
B and the total for the 1/3rd periods worth of payments is in column M.
I want my summary sheet no. 5 to have six columns, being (A) for the
clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the
third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd

Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in
column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I cannot
seem
to get either one to work. Both come up with incorrect formula. I am
wanting
my summary sheet to show each of the 1/3rd totals for each client and
then
I
will sum those figures. Summary sheet will contain 6 columns as
follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for
the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end
of
the
year or not. The sheets 1 - 4 show only clients that are active during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of
your
sheets. All the sheets have the identical format in that client names
are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients,
where
the
clients information is never on the same row no. on each sheet,
without
it
being a lengthy case of having to alter the formula to accomodate
the
cell
change address? In other words how do I get it to recognise the
name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those
1/3rd
totals
for each client so I can then total those to give the yearly figure
for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a
maximum
of
2
years but usually less. So that means the current clients move up
the
row
on
the sheet as other clients finish hence the different row numbers.









  #9   Report Post  
Biff
 
Posts: n/a
Default

Hi!

The idea is to get the worksheet to read the name in column A for
each row on the summary sheet and then search through the Term 1 sheets
(for
example being Term 1 and Term 1 page 2 in this scenario)


So, this needs to "search" 2 sheets for each "Term" ?

Suppose you want to total client XYZ. Does that mean client XYZ could be
listed on sheet Term 1 *OR* sheet Term 1 page 2 and the same for the other
"Terms" ?

I don't suppose you could send me the file, could you? From the sound of the
data it's probably "confidential".

Maybe you can make up a dummy file by making a copy and deleting the client
names. Basically, the client names are irrelavent.

If you want to do that, you can contact me he

xl "can" help at comcast period net

Get rid of the quoted stuff and change the obvious.

Biff

"Sue" wrote in message
...
Hi Biff,
I am finally getting at least a figure showing on my summary sheet instead
of error messages but it is not picking up the right figures.
My sheet names are for example "Term 1", "Term 1 Page 2", "Term 2", "Term
2
Page 2", "Term 3", Term 3 Page 2", "Term 4", "Term 4 Page 2 and "Summary
Sheet".
On my "Term Sheets" the clients names are in column B running down from
row
3 to row 39, and the total payments column for the term is column M,
running
down from rows 3 - 39. Also does it matter that the totals in column M
have
a sum calculation sourced from colums E - L, rows 3 - 39?
My "Summary Sheet" for the year has client names in column A. The Term
Totals are to be in columns B - E inclusive and the total for the year is
in
column F. The idea is to get the worksheet to read the name in column A
for
each row on the summary sheet and then search through the Term 1 sheets
(for
example being Term 1 and Term 1 page 2 in this scenario) and find that
same
name (in other words match it). Once matched, to then travel across the
row
to locate the term total in column M and insert that figure only, into my
summary sheet in colum B. The same process would take place for Summary
sheet column C looking in Term 2 and Term 2 Page 2 sheets and column D
looking in sheets Term 3 and Term 3 page 2 etc.
Have I made it any clearer?
I know it sounds complicated but in reality it is straightforward. It's
my
explaining perhaps. I don't know.
I hope it can be done as above. Is it possible to be done that way and
how?


"Biff" wrote:

Hi!

I'm a little confused but it's not a problem!

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd


Sheets 1 - 4


So, are the *ACTUAL* sheet names Sheet1, Sheet2, Sheet3 and Sheet4?

Or, is the first sheet in the workbook named 1st 3rd, the second sheet
named
2nd 3rd, etc.?

I based my formulas on the sheet names being Sheet1, Sheet2, etc.

Assume on your Summary sheet:

A1:F1 are the column headers:

Client
1st 3rd
2nd 3rd
3rd 3rd
4th 3rd
Total

A2:An = client names

In B2 enter any one of these formulas and copy across to E2 then down as
needed:

=VLOOKUP($A2,INDIRECT("sheet"&COLUMN(A1)&"!B:M"),1 2,0)

=SUMIF(INDIRECT("sheet"&COLUMN(A1)&"!B:B"),$A2,IND IRECT("sheet"&COLUMN(A1)&"!M:M"))

=INDEX(INDIRECT("sheet"&COLUMN(A1)&"!M:M"),MATCH($ A2,INDIRECT("sheet"&COLUMN(A1)&"!B:B"),0))

Biff

"Sue" wrote in message
...
Hi Biff,
I have ended up putting the scenario also under Live Meeting titled
Excel
Worksheet Functions dated 15/05/05. I have given a more complete
example
of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in
column
B and the total for the 1/3rd periods worth of payments is in column
M.
I want my summary sheet no. 5 to have six columns, being (A) for the
clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the
third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4
is
4th
3rd

Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in
column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I
cannot
seem
to get either one to work. Both come up with incorrect formula. I
am
wanting
my summary sheet to show each of the 1/3rd totals for each client
and
then
I
will sum those figures. Summary sheet will contain 6 columns as
follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column
for
the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4
is
4th
3rd and sheet 5 is the summary sheet. This summary sheet will show
all
clients active during the year whether they are with us as at the
end
of
the
year or not. The sheets 1 - 4 show only clients that are active
during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of
your
sheets. All the sheets have the identical format in that client
names
are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use
this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for
clients,
where
the
clients information is never on the same row no. on each sheet,
without
it
being a lengthy case of having to alter the formula to accomodate
the
cell
change address? In other words how do I get it to recognise the
name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year
from
clients
and the total. On a summary sheet I want excel to pick up those
1/3rd
totals
for each client so I can then total those to give the yearly
figure
for
each
client. The reason the cell address is not consistent on each
1/3rd
period
sheet is because each client's time span with us is only ever a
maximum
of
2
years but usually less. So that means the current clients move
up
the
row
on
the sheet as other clients finish hence the different row
numbers.












  #10   Report Post  
Sue
 
Posts: n/a
Default

Hi,
I have stripped back the file and inserted a few invented names and figures
to help see if the formula works when you try it.
Only thing is I can't get the file emailed to you. It is saying not correct
email address. I have put in these two scenarios literally, as follows:
xl "can"

and they won't take.
Sue


"Biff" wrote:

Hi!

The idea is to get the worksheet to read the name in column A for
each row on the summary sheet and then search through the Term 1 sheets
(for
example being Term 1 and Term 1 page 2 in this scenario)


So, this needs to "search" 2 sheets for each "Term" ?

Suppose you want to total client XYZ. Does that mean client XYZ could be
listed on sheet Term 1 *OR* sheet Term 1 page 2 and the same for the other
"Terms" ?

I don't suppose you could send me the file, could you? From the sound of the
data it's probably "confidential".

Maybe you can make up a dummy file by making a copy and deleting the client
names. Basically, the client names are irrelavent.

If you want to do that, you can contact me he

xl "can" help at comcast period net

Get rid of the quoted stuff and change the obvious.

Biff

"Sue" wrote in message
...
Hi Biff,
I am finally getting at least a figure showing on my summary sheet instead
of error messages but it is not picking up the right figures.
My sheet names are for example "Term 1", "Term 1 Page 2", "Term 2", "Term
2
Page 2", "Term 3", Term 3 Page 2", "Term 4", "Term 4 Page 2 and "Summary
Sheet".
On my "Term Sheets" the clients names are in column B running down from
row
3 to row 39, and the total payments column for the term is column M,
running
down from rows 3 - 39. Also does it matter that the totals in column M
have
a sum calculation sourced from colums E - L, rows 3 - 39?
My "Summary Sheet" for the year has client names in column A. The Term
Totals are to be in columns B - E inclusive and the total for the year is
in
column F. The idea is to get the worksheet to read the name in column A
for
each row on the summary sheet and then search through the Term 1 sheets
(for
example being Term 1 and Term 1 page 2 in this scenario) and find that
same
name (in other words match it). Once matched, to then travel across the
row
to locate the term total in column M and insert that figure only, into my
summary sheet in colum B. The same process would take place for Summary
sheet column C looking in Term 2 and Term 2 Page 2 sheets and column D
looking in sheets Term 3 and Term 3 page 2 etc.
Have I made it any clearer?
I know it sounds complicated but in reality it is straightforward. It's
my
explaining perhaps. I don't know.
I hope it can be done as above. Is it possible to be done that way and
how?


"Biff" wrote:

Hi!

I'm a little confused but it's not a problem!

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is
4th
3rd

Sheets 1 - 4

So, are the *ACTUAL* sheet names Sheet1, Sheet2, Sheet3 and Sheet4?

Or, is the first sheet in the workbook named 1st 3rd, the second sheet
named
2nd 3rd, etc.?

I based my formulas on the sheet names being Sheet1, Sheet2, etc.

Assume on your Summary sheet:

A1:F1 are the column headers:

Client
1st 3rd
2nd 3rd
3rd 3rd
4th 3rd
Total

A2:An = client names

In B2 enter any one of these formulas and copy across to E2 then down as
needed:

=VLOOKUP($A2,INDIRECT("sheet"&COLUMN(A1)&"!B:M"),1 2,0)

=SUMIF(INDIRECT("sheet"&COLUMN(A1)&"!B:B"),$A2,IND IRECT("sheet"&COLUMN(A1)&"!M:M"))

=INDEX(INDIRECT("sheet"&COLUMN(A1)&"!M:M"),MATCH($ A2,INDIRECT("sheet"&COLUMN(A1)&"!B:B"),0))

Biff

"Sue" wrote in message
...
Hi Biff,
I have ended up putting the scenario also under Live Meeting titled
Excel
Worksheet Functions dated 15/05/05. I have given a more complete
example
of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in
column
B and the total for the 1/3rd periods worth of payments is in column
M.
I want my summary sheet no. 5 to have six columns, being (A) for the
clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the
third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4
is
4th
3rd

Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in
column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I
cannot
seem
to get either one to work. Both come up with incorrect formula. I
am
wanting
my summary sheet to show each of the 1/3rd totals for each client
and
then
I
will sum those figures. Summary sheet will contain 6 columns as
follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column
for
the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4
is
4th
3rd and sheet 5 is the summary sheet. This summary sheet will show
all
clients active during the year whether they are with us as at the
end
of
the
year or not. The sheets 1 - 4 show only clients that are active
during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of
your
sheets. All the sheets have the identical format in that client
names
are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use
this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for
clients,
where
the
clients information is never on the same row no. on each sheet,
without
it
being a lengthy case of having to alter the formula to accomodate
the
cell
change address? In other words how do I get it to recognise the
name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year
from
clients
and the total. On a summary sheet I want excel to pick up those
1/3rd
totals
for each client so I can then total those to give the yearly
figure
for
each
client. The reason the cell address is not consistent on each
1/3rd
period
sheet is because each client's time span with us is only ever a
maximum
of
2
years but usually less. So that means the current clients move
up
the
row
on
the sheet as other clients finish hence the different row
numbers.













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want Excel to allow cells with formulas and unrelated text blueboy Excel Discussion (Misc queries) 9 March 4th 05 01:22 AM
Cells are being selected automatically in Excel Ian Excel Discussion (Misc queries) 2 February 26th 05 12:31 PM
Excel: To Display Formula in the cells Instead of Value [email protected] Excel Discussion (Misc queries) 1 December 10th 04 10:48 PM
How to get excel cells to change colors depending on value KV Excel Worksheet Functions 2 November 25th 04 10:50 AM
How do I password protect cells in a spreadsheet created in Excel Tbenson37303 Excel Worksheet Functions 0 November 22nd 04 10:21 PM


All times are GMT +1. The time now is 06:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"