Selecting data on worksheet without using sheetnames?

**WRL** · 05-04-2012, 03:55 AM

Hi everyone, I was kindly assisted yesterday with a problem I had. But soon realised that I am facing another problem. I have a workbook that contains the exact same spreadsheet with the only difference being that every new sheet having unique fiscal data for every new year. The formulas are refering to the previous year's data (on the worksheet to the right) to do calcs. But if I want to add a new worksheet for the new year, all the formulas are referring to the old fiscal years data. Would it mean I will have to manually change every formula on every new sheet to point to the right (previous year) worksheet?

I have attached the workbook fyi, the column with the problem is "SPLY". Is there a way to workaround this please anybody?

Budget1213_2.xlsx

**arlu1201** · 05-04-2012, 04:29 AM

This might be a lil long-winded solution (and you may get better ones here from the formula experts). But if you want to try it.

1. Use any un-occupied cell which wont be over-written in future and put in the current sheet name. For e.g BA1
2. Use the next cell to its right and calculate the current year - 1 to give you the previous year's worksheet name. For e.g. BB1
3. In your sumproduct formula, try using the indirect function to involve the value in cell BB1 so it can be used to reference the right previous year's worksheet.

Hope this makes sense.

**WRL** · 05-04-2012, 04:57 AM

Thanks Arlette, sounds a bit complicated. But I'll give it a go and get back to you. Thank you for your reply.

**WRL** · 05-04-2012, 05:35 AM

Ok... uhm. I'm lost.

What did you want me to do again?

**arlu1201** · 05-04-2012, 05:39 AM

Did the suggestions in post 2 work? Are you getting any error?

**WRL** · 05-04-2012, 06:11 AM

I'm actually trying to do what you said, but I'm getting confused as to what it is exactly that you are requesting?

**arlu1201** · 05-04-2012, 06:19 AM

Let me work it out for you. Just to get some clarity -

You have this formula =SUMPRODUCT((E2:P2<>"")*('11_12'!E2:P2<>"")*'11_12'!E2:P2)*0.975 in R2 of worksheet 12_13. So if i make a copy of this sheet and name it as 13_14, the formula should change to =SUMPRODUCT((E2:P2<>"")*('12_13'!E2:P2<>"")*'12_13'!E2:P2)*0.975 right?

**WRL** · 05-04-2012, 06:26 AM

Hi Arlette, yes that's correct. So with every new year that I create a new blank copy of the budget the formula in the SPLY must be updated to refer to the previous year's worksheet e.g.: 13_14 should refer to worksheet 12_13, which in turn needs to refer to 12_11.

**arlu1201** · 05-04-2012, 07:17 AM

Try this.

Considering that you have only 2 sheets in your workbook, namely, 11_12 and 12_13, put this formula in sheet 12_13 cell AA2 =MID(CELL("Filename",I7),SEARCH("]",CELL("Filename",I7),1)+1,32)

Put this formula in sheet 12_13 cell AB2 =LEFT(AA2,2)-1 & "_" & RIGHT(AA2,2)-1

Then change the formula in sheet 12_13 cell R2 (try on 1 cell first) =SUMPRODUCT((E2:P2<>"")*(INDIRECT("'"&AB2&"'!E2:P2")<>"")*INDIRECT("'"&AB2&"'!E2:P2"))*0.975

Then copy this sheet and rename to 13_14. The values in AA2 & AB2 will change automatically. Just check if the values in R2 change as they should.

Please let me know if it works at your end.

**WRL** · 05-04-2012, 07:20 AM

Thanks, will test and revert back!

**WRL** · 05-04-2012, 07:43 AM

First of all. You are a genuis. Secondly, thank you kindly. I don't unfortunately understand what you did. But I will now look at the different functions and first look at what they do individually and then look at the bigger picture.

Thank you once again, much appreciated! I shall mark the thread as "solved" and click on your rep button. You deserve it.

**arlu1201** · 05-04-2012, 07:48 AM

Thank you, glad it worked.

To explain to you the formula, its very simple -

Cell AA2 is a formula for the sheetname of the current tab.
Cell AB2 just uses the left & right functions to get the previous year.
The indirect function embedded in your sumproduct function picks up the sheetname from cell AB2 and gets the values from that sheet.

Selecting data on worksheet without using sheetnames?

LinkBack

Thread Tools

Rate This Thread

Display

Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Re: Selecting data on worksheet without using sheetnames?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions