Transfer Text from one cell to another cell in a certain sheet based on text input in another cell

[SarahJ330]

Yep this is a doozer! I have a table in sheet 1, if a tick is placed in a cell on a certain row I would like text connected to that cell to be transferred to a cell in another sheet. Next trick is there may be multiple ticks in a row, so how do I make sure no info is overwritten? And last sneeky trick is there are multiple rows and each row will need a different sheet. Can it be done? :)

May the force be with you :)

Sarah, a teacher in need of amazingness

[#2]

You could create a formula with the FILTER function. It's difficult to draw one up for you without a sample workbook or knowledge of the data arrangement.
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

[#3]

@SarahJ330 

I agree with @Patrick2788 on the potential usefulness of FILTER for your need. Here's another link to the video that introduced me to the FILTER function (along with some other of the "dynamic array" functions) and taught me how to use them.

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#4]

@mathetes and @Patrick2788 Wowzers you guys it's new years day!!! Seriously appreciative though!!! If this cunning plan works it will save teachers some serious documenting time to make some resources. So I will definitely look at the links you have both sent me. 

But thought I would send some images to ask if this cunning plan is achievable.

These are the original word docs, we link each task (say one of the yellow rows) to the achievement standards AS and content descriptions CD up the top. Then we put all of that info in another table with planning and assessment intentions on the right.

I put them on Excel and I'm very proud to say I formatted the AS with the appropriate CD in the first sheet, and linked topic name of each row to a new sheet for each task, yay me. Not sure if I should have a sheet for each task or sheet for each class or colour?

(so these are 2 different year levels but I thought it would be better to screen shot an eg of each page)

Ok so the deal is I would like to automatically copy the ticked AC blurbs (say the light blue row 4 and it is ticked on E4) in the sheet 2 B5 cells and the chosen crossed CD(L4) to the A7 cell, but there will be more than one tick and cross in each row. Can it be done? And should I just put multiple tables on top of each other for each different coloured class or make a bazzillion sheets?

Did that make any sense?

Sarah

[#5]

@SarahJ330 

"if a tick is placed in a cell on a certain row I would like text connected to that cell to be transferred to a cell in another sheet"

Excel doesn't really work like that in that you cannot instruct it to transfer data.

A more useful view might be

1. You have a data source that comprises a list of tasks, a list of achievement standards and a list of content descriptions.

2. There are many to many relationships between the Tasks and the Achievement Standards represented by a crosstab of check marks.  Given an item from one of the lists you can look up a list of related attributes.  The same applies to the content descriptions.

3. To select the AS specific to any given task, you could use a formula such as

= FILTER(AS, XLOOKUP(currentTask, task, crosstab="x"))

Given that, your focus should shift to the sheets that present information based upon the data source.  How you present the information depends entirely on your use case.  For each cell of the report, you decide what data you wish to reference and how you wish to present it.  Do not get carried away with the formatting at this stage; you are engaged in software design, not graphic art design. 

[#6]

@SarahJ330 

Note carefully that last piece of advice from @Peter Bartholomew 

Do not get carried away with the formatting at this stage; you are engaged in software design, not graphic art design. 

One of the most important principles of spreadsheet development. Make the spreadsheet function as you want first.  Make it work as desired. THEN make it pretty. Introduce colors. Merge cells for headers.

Many of the things that make a spreadsheet "pretty" -- notably merging cells, other formatting things -- when introduced too early in the developmental process, actually can interfere with smooth functioning.

[#7]

Right. I'll bare it down and start again. Thx.
Just to be clear you can't link the tick to copy txt to another cell or just not to another sheet?
And of you copy cells do the formulas if that what you call them copy too? They don't seem to

[#8]

My eyes were drawn to the search bar in your screen caps that reads "Tell me what you want to do". Unless you're working in Office 365 that has not been updated since 2018, this may indicate you're working in Office 2016 Pro. This is important because 2016 Pro does not have access to FILTER nor dynamic arrays. For your task, it's certainly still do-able but would require more 'baby-sitting' the spreadsheet (and likely some INDEX formulas with nested SMALL arrays), so to speak.

Do you know which version of Office you're running?