Creating a formula in Name Manger to link to a specific word

[Marcel Coetzee]

I would like to create a formula that will give me a figure from a different worksheet dependent on a specific phrase. I have attached a worksheet and will try and explain what I need from it.

I have a tab called "Quarterly Data". This tab needs to give me actual figures for months past, and forecasted figures for months coming. I have seperate worksheets for the actual figures and the forecasted figures.

So for January - June, I need to lookup the data for each category (Sales, cost of sales, interest, expenses) from the actuals tab, and for July - December, I need to look up the data for each category from the Forecast tab.

I need the formula in the quarterly tab to link to the specific word "Actual" or "Forecast" as in line 6. The reason being is that I want the quarterly worksheet to work as simple as changing the words in this line from "forecast" to "actuals" and it should automatically change the formula to the go pick up the actual data for that specific month.

I know this can be done with a "name manager" formula, I'm just not sure how to do it

[FDibbins]

Hi and welcome to the forum

Try this...

For ease, 1st, change the Actual Data sheet to just Actual

Then in B8, copied down and across...
=OFFSET(INDIRECT(B$6&"!B8"),ROW()-8,COLUMN()-2)

[ajryan88]

Hi,

You don't need the Name Manager for that, you can actually use the INDIRECT function to refer to a worksheet by its name, however you may find it simpler just to use an IF function due to the fact that "Actual" is only a partial match to the worksheet name.

If however, you decide to change row 6 to say "Actual Data" or "Forecast" (or even just change the sheet name to "Actual"), then you could use the following formula (for example) to get a value from the appropriate worksheet:

Please Login or Register  to view this content.

This would return "FOR MTH", which is the value of cell B4 on the "Forecast" worksheet. You could then incorporate this INDIRECT function into your lookup function to suit your needs.

If you don't want to change the name of the worksheet or the text in row 6, then you must use an IF function like so:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

As you can see, it's probably much simpler to make the string and the worksheet name consistent.

Hope this helps

[Marcel Coetzee]

Hi FDibbins

Thanks for the help, however, I can't change the name of the Actual Data as on my spreadsheet that I will eventually use, the name will not be the same as "actual". How would I incorporate a worksheet name that does not agree?

Thanks!

[Marcel Coetzee]

Hi ajryan88

Thanks for the help, however, I need the spreadsheet to return the value in the cell. At the moment with your last formula, it returns "FOR MTH". I am unable to change the name of the worksheet, as in my real worksheet, the name will not be the same as Actual.

[ajryan88]

Hi Marcel,

Yes my formula was only an example, you will have to change the references to match the data that you want returned.

And I have demonstrated the use of an IF formula for use if the worksheet name is not the same.

[Marcel Coetzee]

Hi ajryan88

I think I got the 1st cell to work, however, is there an easy way to copy this formula down and across without changing each formula in each cell?

[FDibbins]

Hi FDibbins

Thanks for the help, however, I can't change the name of the Actual Data as on my spreadsheet that I will eventually use, the name will not be the same as "actual". How would I incorporate a worksheet name that does not agree?

Thanks!

=OFFSET(INDIRECT(IF(B$6="actual","'"&B$6&" Data'",B$6)&"!B8"),ROW()-8,COLUMN()-2)
note the change from my original sugge4stion...
=OFFSET(INDIRECT(B$6 &"!B8"),ROW()-8,COLUMN()-2)
If you have a space in a sheet name, excel needs the sheet name to be between 's...
=sheet1!a1
='sheet 1'!A1

If you will have a different name in your sheet, but wont have the same name in your table, you may need to use a nested IF() or a vlookup() table

edit: aj's suggestion will not easily copy down or across without manually adjusting the references, thats why I included the offset()

[FDibbins]

Thanks for the feedback Marcel, btw, whats a dutchie doing in Aus? (SA ex-pat here)