Is it a Tab name format problem?

[Hugo]

I am sure that this is a really dumb question but even after trawling all over this unbelievably fantastic site I still cant find the answer.

(I would have used the HTML Maker utility to show my problem but cant get it to work – seem to be missing msoutil9.olb – whatever that is.)

To simplify the problem:

I have a workbook with 3 worksheets, Sheet1 Sheet2 Sheet3

Sheet1
Column A B
Row 1 Cat 10
Row 2 Dog 50
Sheet2
Column A B
Row 1 Cat 20
Row 2 Dog 100

I want to make Sheet3 a summary sheet which shows the values and ends up as
Column A B C
Row 1 Sheet1 Sheet2
Row 2 Cat 10 20
Row 3 Dog 50 100

I use this formula in Sheet3 Cell B2
=INDIRECT(B$1&"!"&CELL("address",$B1))

when I drag it across and down the cells fill up correctly and it works fine.

But in the real world the worksheet tab names are dates (1 Oct 02 etc.) and when I change the cells on Sheet3 B1 to 1 Oct 02 and C1 to 2 Oct 02 I get the answer:

Column A B C
Row 1 1 Oct 02 2 Oct 02
Row 2 Cat '37530'!B1 '37531'!B1
Row 3 Dog '37530'!B1 '37531'!B1


Apart from the obvious problem that the sheet reference converts a date to its number and it does not recognize it as a worksheet name reference I also see that when dragging across the date numbers increase correctly, but dragging down the cell reference B1 does not become B2.

I have also tried VLOOKUP but have a similar problem in getting the formula to recognize a date entry in a cell as a reference to a worksheet name.

Any ideas from the less dumb?

 

[SimonP]

Hugo,
It isn't the fact that your worksheet tab is named in a date format that's the problem - it's because it contains spaces.
When Excel references a tab name that contains spaces it adds a ' at the beginning and end of the tab name.

So you need to either:

1) take the spaces out of the tab names (replace with underscores)
2) Enter the tab name on your summary sheet as ''1 Oct 02'
3) Change your formula as follows:

=INDIRECT("'"&B$1&"'!"&CELL("address",$B1))


Hope this helps

Simon

 

[Hugo]

Simon,
Many, many thanks for your prompt and VERY helpful reply.

You were spot on with your advice about the spaces in the tab names causing the problem.
When I took them out it worked perfectly.

But I have dozens of them going back several years so its easier for me to enter the tab names in the summary sheet as ‘’1 Oct 02’ and use the existing tab names, again works perfectly.

Had a problem with your formula

=INDIRECT("'"&B$1&"'!"&CELL("address",$B1))

which gives me a #REF!
so I used

=INDIRECT(B$1&"!"&CELL("address",$B1))

and that works fine. (Don’t ask me why – I have no idea!)

Now if only I could find a way to drag across ‘1 Oct 02’ ’2 Oct02’ or use replace or something like that to get ‘3 Oct 02’ ‘4 Oct 02’ etc. in successive columns then that would be bliss.

Hugo

 

[Hugo]

Since posting the above I have now found

=TEXT(B1,"'d mmm yy'")

I auto fill the dates 1 Oct 02 2 Oct 02 etc. in row 1 and then in row 2 I fill across again converting them to ‘1 Oct 02’ ‘2 Oct 02’ etc. using the above.