Data validation list question

[xoxonutty]

Hi All, i'm a newbe so bear with me if this question has been asked and or answered already. Is there a formula that i can use which allows me to select different tabs / sheets using a drop down on my main sheet?. Or a series of formulas that allow me to select a sheet from a dropdown, input data on my main sheet and have it populate or copied to the sheet with the name I selected. Thanks in advance for any help offered.

 

[mumps]

Where on the main sheet would you input your data? Where on the selected sheet would you want to paste that data?

 

[xoxonutty]

i would like to enter it in the area selected and have it reflected in the same area in the other sheets

, all the sheets are the same the only unique factor will be the name.
Trying to upload a pic

 

[xoxonutty]

https://photos.app.goo.gl/N4xnCFhZ5A1NgiFh7

 

[mumps]

Rather than a picture, can you upload a copy of your file. It would make it easier to test a possible solution. It looks like you want the range B3:H33 copied to the selected sheet. Is this correct?

 

[xoxonutty]

yes thats exactly what i want , using a validation list to select between sheets. i will upload the file.

 

[xoxonutty]

https://docs.google.com/spreadsheets/d/1fxl15_zd4gdIcyh2GPF61wXKWFp7dvesmTvDtHmkSRE/edit?usp=sharing

uploaded

 

[mumps]

Click here to download your file. I've had to unmerge all your merged cells in the Main sheet and Week sheets in order to get the macro to work. You should avoid merging cells at all cost because they cause havoc for Excel macros. Enter the data in Main sheet and then select a sheet from the drop down in cell B2.

 

[xoxonutty]

Man This is amazing, it took me at least a day searching the internet and YT to find this and could not!. You did it in less than 2 Min. Thank you , thank you, its exactly what i needed. Is there any way i can see the formula so I can apply it to any other sheets i add.

 

[KUYJS]

You may use INDIRECT formula for this
Example:

indirect($B$2&"!"&A$3&row(A2))

where, cell b2 has fixed validation for your all sheet names like week1,week2...
Row 3 has column headers (A, B, C, D...)
that means: $b$2 is taking your required sheet/tab
A$3 is checking column number from target sheet from where value will come
Row(A2) will check row number from target sheet from where value will come

Regards,
Tarun