Formula help needed desperately quickly!

[sean69]

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?

 

[JamieDuncan]

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]

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]

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]

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]

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]

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]

Sean

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

 

[sean69]

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]

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.