referring to another sheet by referencing cell in present sheet

[neilsolaris]

Hi,

Is it possible that I can write a formula that uses the name of a cell in the present sheet to reference another sheet (in the same workbook) with that name?

I've kind of got it to work, but it wasn't as simple as I was hoping. Here's an example of what I want to do.

In column A I have the months listed, January to December. I also have 12 other worksheets, named January to December.

Let's say I want to reference cell B10 in the January sheet. I could write =January!B10, and that would work fine.

In the present worksheet, the word January is in cell A14. So if I type the formula instead as =A14!B10 it opens up a box called "Update Values: A14", and it requires me to specify the workbook and worksheet that I'd like to reference. The problem is, this is hardly time saving, and I want to be able to copy the formula to multiple other cells, so I don't think this method is practical.

Is there a solution to this? I'm using Excel 2007 by the way.

Many thanks for your help.

[JohnTopley]

Try

=INDIRECT("'" & A14 &"'!B10")

[neilsolaris]

Perfect, thanks a lot!

[neilsolaris]

I've just tried adapting the above formula to include a sumif. It doesn't return an error message, but it returns zero, whereas I'm expected something different (according to the data I entered). Here's my formula.

=sumif(INDIRECT("'"&$A5&"'!$C$19"):INDIRECT("'"&$A5&"'!$C$500"),”Bank”,INDIRECT("'"&$A5&"'!F$19"):INDIRECT("'"&$A5&"'! F$500"))

Basically, in first sheet where the formula is, cell A5 contains the word "April". There is a corresponding sheet named April. In the sheet named April, column C can be named either Bank or Cash. Column F contains a particular payment type, in a numeric currency value.

Here's the formula I'm trying to copy, using the Indirect function.

=SUMIF(April!$C$19:$C$500,"Bank",April!F$19:F$500). This one works, and returns the correct result.

Is anyone able to pinpoint what I'm doing wrong?

Many thanks.

[AliGW]

Try this:

=SUMIF(INDIRECT("'"&$A5&"'!$C$19:$C$500","Bank",INDIRECT("'"&$A5&"'!F$19:F$500")

[neilsolaris]

Thanks for the suggestion. I have a feeling that that was the first thing I tried initially. It came up with an error "too many arguments". Also, are there two parenthesis missing? I inserted two at the end, but I just realized that one of them probably needs to go in the middle. I'll play around with it to see if I can make it work.

Edit: I inserted a parenthesis in the middle and now it works perfectly. Thanks a lot! Here's what I changed by the way...

=SUMIF(INDIRECT("'"&$A5&"'!$C$19:$C$500"),"Bank",INDIRECT("'"&$A5&"'!F$19:F$500"))

[AliGW]

Yes, I missed that, sorry - no workbook from you, so couldn't test/check it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[neilsolaris]

Thanks for the suggestions, I'll do both now.

[AliGW]

By the way, is your profile correct? Excel 2007 is quite old - do you have MS365? If so, please update this entry.

[neilsolaris]

Yes, I have Excel 2007 still! I'm quite tight, I don't fancy paying for the latest version!

However, my spreadsheet does crash quite a lot, especially ones which are more complicated and contain VBA. I wonder if it would be more stable on a more up to date spreadsheet. If I were to upgrade to a later version, are there ones where I can just pay a one-off fee? Because with MS365, don't you have to make annual payments?

[AliGW]

I really don't think (personally) that buying standalone versions is cost-effective any longer. I would subscribe to the cheapest subscription version available and you will always be up-to-date. Starts at £60pa:

https://www.microsoft.com/en-gb/micr...365-products-b

[neilsolaris]

I'll definitely look into it.

[AliGW]

I resisted the subscription model for some time, but eventually succumbed when my daughter needed to use it on her own machine - I wish I'd gone that way sooner, to be honest, and new functionality is being added all the time, so it really is the way to go, in my view.

[neilsolaris]

I use Access, so probably I'd need to pay for the business version, costing £135.36 incl VAT per year. Still, it's not too much, and is tax deductible!

[AliGW]

If you are not self-employed and the company that employs you subscribes, then you are allowed to have a copy of their subscription on your machine at home. Just a thought ...

MS365 Apps might be enough at £94.80pa:

https://www.microsoft.com/en-gb/micr...iness-products

[neilsolaris]

Thanks for the info. Unfortunately I'm self-employed only, so I wouldn't qualify for that. I'll keep it in mind for the future though.

[AliGW]

Maybe just the Business Apps version, then - you could claim the VAT back, at least, and charge the rest to your business.

[neilsolaris]

I'm not VAT registered, I'm a poor musician having lost most of my work because of the pandemic! But I'll get a 29% discount with income tax and class 4 NIC!

[AliGW]

Better than a poke in the eye ...

Sorry for your current predicament - it looks like you will soon be getting work again, though - fingers crossed!

[neilsolaris]

Thanks! In fact I did my first concert to a live audience last night since early last year. Other than that it's been mostly recordings.

[AliGW]

I've just retired from teaching after 31 years - work has continued for our peripatetic musicians throughout, firstly via Zoom/Teams, and latterly back in school with social distancing observed. They've been the lucky ones, I guess.

[neilsolaris]

Enjoy your retirement! Yes, for the teachers it was work as usual I guess. One of my colleagues told me yesterday that trying to teach violin online was a nightmare, because of the poor sound quality, but for her guitar teaching husband it was much easier.

Going back to my formula, it works really well, except it's not quite as labour saving as I originally hoped. Because the F column (in this case) in the formula is within inverted commas, I can copy and paste the formula down no problem, but if I copy it to the next column to the right, instead of referencing the G column it stays at F. I guess there's no way around this?

I'm actually editing a friend's tax spreadsheet to make it automated, but keeping her present layout. However, I think having tables and pivot tables would be much more efficient. I'll try to persuade her to change I think!

[AliGW]

This is where using INDIRECT gets tricky, but there are ways round it.

I'm going to advise you to open a new thread with a title that refers to making INDIRECT array references dynamic.

[neilsolaris]

Now that you've given me some terms to type into Google I can do some research first, and post again if I get stuck!

Many thanks for your help.

[AliGW]

I won't be getting stuck but you might!

if you need help, do open that new thread, please.

[neilsolaris]

Oops, I meant I* (I wouldn't be that rude intentionally!).

Sure, I'll post on a new thread soon.