My question probably sounds funny, but bare with me for a second. What I am trying to do:

I got an excel for 40-50 odd sheets, and a summary page. On the summary page, I got a line for each sheet where I am trying to draw specific data from each sheet. What I am doing right now is going to each sheet and click the 3-4 cells that I want added together to form the one number I want, and I have to do this for 10 difference values for each sheet. Is there a formula or formula that can do this?

What I have in mind is something like this:

I will have a column (say A) with all the sheet names. Then a column (say B) with the line number of the data I want. Then on the next column, I will have a formula like "=concatenate("'","A1","'!","X","B1")" Now obviously concatenate isn't a sum formula and this will just give me text like this " 'sheet1'!X## " where X is the column value I chose, and ## is B1's value. I hope to be able to do this and then just paste the for over 40-50 lines all referring to different sheets and rows on the sheet (column is the same for all).

I don't quite understand what you are trying to do, but I think that you are looking for the INDIRECT function. Read up on the function in the Excel Help files or DAGS.

The INDIRECT function returns the value in the cell represented by the text string used (or built) as the argument.

e.g. If I start with this:

A B 1 Sheet1 2 2 Sheet2 5then this will return the

contentsof Sheet1!B2=INDIRECT(A1 & "!B" & B1)

If I wrap a SUM function around a couple of INDIRECT functions, I can SUM the values in the referenced cells:

=SUM(INDIRECT(A1 & "!B" & B1), INDIRECT(A2 & "!B" & B2))

would return the same value as:

=SUM(Sheet1!B2, Sheet2!B5)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History