Yes/no drop down list needs to populate a contents page with matching page numbers

Hi everyone,

I've attached a file below and I would be very grateful if anyone could take a look at what I hope isn't an overly complicated task.

I have five sheets, the first being an entry sheet with drop down y/n answers to questions. If the answer to the first question (A4) is 'Yes' (D4) then I would like the related sheet (Sheet 3 - Spain) to be visible.

The second page (Contents Page) will then need to be populated so that a row appears with the text in ('Spain!A1'). As it stands that would mean that in the contents page sheet A4 would = "Spain rain". I would then need a page number to be added to the visible sheet (Spain) this number would be reflected in ('Contents Page!C4').

Then for the second row of questions in the entry sheet, if the answer if 'No' (it currently is), I would like the relevant sheet (Plain) to be hidden and for the contents page to not have a row for it, and for the page numbering to reflect this.

As the third row of questions has an answer 'Yes', I would like that sheet to be visible, numbered logically to be the next one and for the contents page to show it in the next row with the page number it has been given.


I appreciate that this is the weekend so I have made a simple example of this based on an old nursery rhyme (for ease) but the ability to manipulate data like this would be seriously useful to me so I thank anyone in advance who might be able to help me with this.

Many thanks,

Limebaish