SpreadEveryone: The Fetchland Excel wire

101 watchers
GregP
GregP
Apr 2019
9:24am, 12 Apr 2019
111,863 posts
  •  
  • 0
GregP
Hello. The answer to this might well be that it cannae be done but...

Can I "indirect" to a cell in a sheet who's name has spaces in it? I can't get it to work.
Diogenes
Diogenes
Apr 2019
9:26am, 12 Apr 2019
35,856 posts
  •  
  • 0
Diogenes
Quicker to rename the sheet?
GregP
GregP
Apr 2019
9:29am, 12 Apr 2019
111,864 posts
  •  
  • 0
GregP
Not an option. ExcelForum has it covered though:

=INDIRECT("'"&A2&"'!a1")
Sam Jelfs
Sam Jelfs
Apr 2019
9:33am, 12 Apr 2019
260 posts
  •  
  • 0
Sam Jelfs
Put the sheet name in a cell and reference that cell in your indirect.

A1: Target Sheet
B1: =indirect("'"&A1&"'!B2'")

Will return cell B2 on sheet "Target Sheet"
GregP
GregP
Apr 2019
9:37am, 12 Apr 2019
111,865 posts
  •  
  • 0
GregP
Yep - it was the quotes I was missing. If your sheets are called things like AllTheFetchies then

=INDIRECT(A2&"!a1")

would work

but not if the sheet was called All The Fetchies
GlennR
GlennR
Apr 2019
10:30am, 12 Apr 2019
41,900 posts
  •  
  • 0
GlennR
Spaces are a curse. Iā€™m not disciplined enough to eliminate them completely though.
Curly45
Curly45
Apr 2019
10:45am, 12 Apr 2019
3,885 posts
  •  
  • 0
Curly45
Underscores are the answer šŸ˜‰
GlennR
GlennR
Apr 2019
11:00am, 12 Apr 2019
41,904 posts
  •  
  • 0
GlennR
Of course, and if you name ranges Excel puts them in for you.
Curly45
Curly45
Apr 2019
2:04pm, 12 Apr 2019
3,886 posts
  •  
  • 0
Curly45
For me I use underscores by habit as you cant find documents/files so easily using the terminal if they have spaces in the file names...
GlennR
GlennR
Apr 2019
3:22pm, 25 Apr 2019
42,112 posts
  •  
  • 0
GlennR
I'm going to assume that everybody else knew how to changes signs in Excel but didn't tell me?

Enter -1 into any free field.
highlight it and copy.
Select the range you want to change the sign of.
Right click, point to paste special and choose Paste Special.
In the new dialog Paste All and apply Operation Multiplication.
OK - all numbers have their sign changed.

Feel free to point and laugh.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work








Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 113,884 Fetchies!
Already a Fetchie? Sign in here