0

I need a way to get information from worksheets AAA, BBB, CCC, etc. into the Summary Worksheet. Each worksheet Name will be listed in the Summary Worksheet, column A and in each worksheet in F1 (not shown in image). As new worksheets are added, and added to the Summary Worksheet, the Summary Worksheet will be sorted by column A and the items in Column A will end up shifting line numbers. (Ex. If item A1B1 was added to Summary Worksheet, column A after DDD, then Summary Worksheet was sorted on column A, A1B1 would be on top. See Example, column Q below.)

X = A blank field in Column O where the formula will go.

Y = The data I want. A Sum (calculated number) within the worksheet and will be in cell C3.

I would prefer this to be a formula that goes in the Summary Worksheet, column O, pulls the worksheet name (automatically) from column A, goes to the applicable worksheet and grabs the info. This way I can pull the formula down as the spreadsheet grows. So the formula for O1 would go something like, and I know this is wrong:

=IF(A1=””,””,???($C3, [something magical here-pull worksheet name from A1]!$C$1:$D$4,1,0))

Summary Corrected Example Summary

Worksheets Worksheets AAA, BBB & CCC

sotirov
  • 263
Pat
  • 36

2 Answers2

0

It is not quite clear, because in the sheet BBB your Y is in D3 and not C3.
From the text I understand you need the value from C3. Generally, you need to use INDIRECT function e.g.

=IF(A1="","",INDIRECT(A1&"!C3"))

or

=IF(A1="","",INDIRECT("'"&A1&"'!C3"))

The second version (with single quotes) is necessary when sheet name contains space or other special character or is a number. If your sheet names are simple texts (letters & digits only) you can use shorter version.

MGonet
  • 4,015
0

Either of the answers provided by MGonet are correct, listed below. Turns out there is some hidden issue in my worksheet that is preventing it from working.

=IF(A1="","",INDIRECT(A1&"!C3"))

or

=IF(A1="","",INDIRECT("'"&A1&"'!C3"))
sotirov
  • 263
Pat
  • 36