Formula help needed desperately quickly!

sean69

New Member
Joined
Oct 3, 2006
Messages
22
Hi,
I need some help with creating a formula in Excel. Can someone tell me if there is a cap on how many cells you can tell a cell to pick up in a formula? I’m trying to create a formula in a cell in the front sheet of a workbook that picks up the corresponding cells in back sheets in the same book, but as soon as I’ve entered 30 an error message comes up saying ‘you have entered too many arguments for this function’…….
Can anyone tell me how I can enable it to let me add more?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Guessing your using the Concatenate function,
Try Concatenating upto thirty cells into a hidden cell (eg white font) do this till you have all data concatenated into a few cells, then in your original cell use Concatenate to the new hidden cells.
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
Generally argument limits depends on a formula.

For example;
For CONCATENATE: limit is 29 Arguments.
For IF : limit is 7 If Arguments.

Please tell more about your formula.
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22
I'm not sure I know any more to say about my formula. Like I say it's in a cell in a front sheet that contains totals from backsheets, so in the cell I am typing: =SUM('Atkins'!C7,'Harpers'!C7....etc)
The only viable option for me is to try and find a way of adding more than 30 into the formula - can this not be done?
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749

ADVERTISEMENT

Sum function is limited to 30 arguments. But you can try to use + in between e.g.

Sum(cell1, cell2...cell30)+sum(cell31,cell32...etc).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Sean

It really would help if you told us more about what you want to do.

If you want to add up C7 from every sheet in a workbook you could use something like this.

=SUM(First:Last!C7)

Replace First & Last with the names of the first and last worksheets.
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22

ADVERTISEMENT

Sean

It really would help if you told us more about what you want to do.

If you want to add up C7 from every sheet in a workbook you could use something like this.

=SUM(First:Last!C7)

Replace First & Last with the names of the first and last worksheets.
I have just tried doing this and it's not letting me do it, do you know exactly what I need to put in terms of position of apostrophes etc? Sorry I can't elaborate more but I'm not really good with computers and technical jargon so I'm not really sure what else to tell you?

Patsys, thanks, just tried that too but it's not letting me do that either?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Sean

Could you please tell us what you actually want to do?
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22
I’m not sure how else I can put it than in my opening post: I want to add a formula into a cell on the front sheet of a workbook, let’s call this cell C7, for example.
I want the formula to add the sum total of all of the figures in the corresponding cells (C7) in the back sheets in the workbook. Each of these back sheets have a name, e.g. Atkins, Harpers etc
So I am typing in the formula ‘Atkins’!C7,’Harpers’!C7….
But when it reaches thirty, it won’t allow any more.
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
I have just tried doing this and it's not letting me do it, do you know exactly what I need to put in terms of position of apostrophes etc? Sorry I can't elaborate more but I'm not really good with computers and technical jargon so I'm not really sure what else to tell you?

Patsys, thanks, just tried that too but it's not letting me do that either?

Can not imagine why it is not letting you do it. Can you post you sheet so we could all see?

Norie's suggestion is probably better if you have all the sheets next to each other. You do not have to type the expression in the formula, all you need to do is type "=sum(" then go to cell C7 of the first sheet, then while pressing SHIFT, click on the last sheet then ENTER.
 

Forum statistics

Threads
1,141,136
Messages
5,704,485
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top