Worksheet formula 1

[clifftech]

I need to get values from numerous worksheets into a worksheet named "Summary". Summary worksheet has a list of all the worksheets names in column A (for example cell A1= "Worksheet_Date1") How do I pull information from cell D15 in Worksheet_Date1 by referencing cell A1 in the summary sheet?

I have tried =A1&"!"&D15 (where A1 is the worksheet name and D15 is the cell I need the value) but it doesn't give the value.

What am I missing?

 

[SkipVought]

Hi,

The short answer is that you do not type ANY REFERENCE. You simply point to the sheet tab for the sheet you want to reference, and then the cell range, and that would result in...
[tt]
=Worksheet_Date1!D15
[/tt]
If your worksheet name is in A1...
[tt]
=INDIRECT(A1&"!D15")
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Thanks for the quick response...

I tried =INDIRECT(A1&"!D15") and get #REF!

 

[SkipVought]

Then, the value that you are referencing in A1 is incorrect!

Do you have a SPACE in the sheet name?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, if it were my workbook design, I would invest my effort, CONSOLIDATING all the detail data into ONE table on ONE sheet, without a doubt!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

The space was the problem. Works great.

I am using this macro to list all the worksheets in a summary worksheet. Is there a better way (function?)to list the worksheets?

Sub SheetNames()
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub

The worksheet is not my design...I'm stuck with it!

 

[SkipVought]

You cannot insert an sheet and consolidate the data. I would not hange ANYTHING ELSE. You could even make that consolidation sheet HIDDEN. It would save you sooooo much time and effort!!! Excel is designed to work on ONE TABLE at a time not multiple tables. Multiple tables is a curse!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Good advice.

Thanks for your help...