Is it a Tab name format problem?

Hugo

New Member
Joined
Oct 10, 2002
Messages
13
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

SimonP

New Member
Joined
Sep 25, 2002
Messages
48
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

New Member
Joined
Oct 10, 2002
Messages
13
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

New Member
Joined
Oct 10, 2002
Messages
13
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.
 

Forum statistics

Threads
1,144,358
Messages
5,723,891
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top