Add a New Tab within a Workbook containing a Dashboard using CHOOSE and IF

Hi.
I downloaded a dashboard template that has two tabs of data and I would like to add additional tabs.

I would have to modify the IF and CHOOSE functions, can anyone help?

Formula view:
Capture.jpg

Here's the pretty view:
Capture2.jpg

Welcome to the forum.

Please note that the screenshot you uploaded makes it extremely difficult for anyone to assist you. It is lower resolution, so the wording is hard to make out. Formula view only shows part of the formula. There are multiple tabs, only one is shown, and none of the formulas I can see contain an IF or a CHOOSE to modify in any way per your request.

Please copy the formulas in full and post with them, or upload a copy of the template and explain exactly what you need help with.

Thanks

An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like.

Here is the file.

Originally Posted by slucille

I tried. I have small experience in Excel.

OK, I see what's going on. I have updated your file for you.

CTA Optimization_7-18-2014.xlsx

I'll explain how this all works.

There are two sets of data on identical layouts on sheets VALO and Opalescence (originally named WebDataS1 and 2 in the template). The Dashboard page digests the data on those two sheets. There are two option buttons, S1 and S2, linked to cell C2, which shows 1 and 2 respectively, depending on which button is selected. C2 is given the name RefSite.

There is another formula in E2 such that if C2=1, the formula gives WebDataS1 and if C2=2 it gives WebDataS2. These names are also range names of the data on the two data sheets. E2 is given the name RefData.

All of the formulas in the rest of the page refer to INDIRECT($E$2), which is either WebDataS1 or WebDataS2. The INDIRECT function changes this range name to the actual range. All of this long series of connections means that when the user selects the S1 option button, all the formulas now point to the data on the first sheet. When the user selects S2, they point to the data on the second sheet.

I have configured your file to use Opalustre as the third sheet. To do this, I selected its data and gave it the range name WebDataS3 (you had it named WebDataS15 but I thought 3 made more sense). Then I had to add a third option button. Last, I had to update the formula in E2. It was

Formula:

Please Login or Register  to view this content.

To select among three sheets, it is a natural to use CHOOSE instead of adding a nested IF. This is where CHOOSE comes into play, although it doesn't exist in the original sheet.

Formula:

Please Login or Register  to view this content.

Now selecting the S3 button will cause all the formulas to refer to the new third sheet.

If you wanted to keep adding more sheets, I would change the design. First, instead of option buttons I would use some type of dropdown box, either data validation or a combobox, with a list of worksheet names. I would change the INDIRECT to reference the selected sheet name instead of a named range. Then you don't need C2 or E2 anymore. Then you can keep adding sheets by just adding each sheet name to a list, instead of adding controls and updating formulas. If that's what you need let me know.

Actually I went ahead and did it. CTA Optimization_7-18-2014.xlsx

To add a new sheet:

1. Copy any of the three existing data sheets, to any location.
2. Add the name of the new copy at the end of the list on sheet "Sheet List".

That's it.