My pseudo formula is

=IF CellX = “1” RETURN “text1”, IF CellX = “2” RETURN “text2”, IF CellX = …

All cells will have text, no int or other numerical.

It’s just to create a quick ‘next steps’ list for my class i.e. if they’re level 1 then it will say their next steps.

Thanks a lot

I’d do that by putting the list of options on a different sheet and using a VLOOKUP formula. I’d have to have it in front of me to work it out exactly. I’ll check back later but sure someone else will have it sorted by then.

Right, I had to do this on Excel, Google sheets is blacklisted through the network here, but I’m pretty sure this will work. On ‘sheet2’ put your numbers 1,2,3 etc. in column A and the corresponding text in B. Assuming the pupil puts their level in cell A1 on sheet 1, put this formula in cell B1.

=VLOOKUP(A1,Sheet2!A1:B4,2)

This has the advantage that you can change the next step text on sheet 2 without having a huge messy nested IF function to alter. You can extend the array to any number of levels by changing B4 in the formula to B10, B20 or however many you want to search through and it’ll work exactly the same.

Thanks a lot.

Out at the moment but will give it a try later.

Assuming it’s basic numerical progression use index rather than vlookup and restrict your input cell’s possible values accordingly

E.g. =index(sheet2!a1:a20,[cell ref]).

Will return the value in the corresponding 1st to 20th cell in the range sheet2!a1:a20.

Other ranges are available

you can also use index across columns should you wish.

vlookup seems more complex than I was expecting.

I’m not sure index is exactly what I’m after… maybe it is. I’ll have a play with it at lunchtime (the exciting life of teaching).

I want a three column sheet with three variables. Name, level and next steps.

If I type 1 into the level cell (say B1), I’d like the next step (C1) to auto-fill with the text.

Would it be a big ask to share the sheet with you and ask you to take a look?

makecoldplayhistory@gmail.com

I’ll send you my Excel spreadsheet over when I get to work later. It should open in Sheets, if it doesn’t I can sort that this evening. Think I understand what you want.

Thank you very much.

Here are screenshots of my two sheets.

Ok do you want to choose “pre level 1” then have can and next auto complete?

If so…

For good practice – Get rid of the blank rows, don’t have them in working data except to break unrelated ranges, they’re fine on your output sheet but in working info use lines and borders to provide a visual break if needed.

Then format your control, it will have a linked cell option. Set this to a free cell on your working sheet (sheet2!z1).

Check the output in “z1” it will be either the chosen text or a number (position in sequence)

If its text then your “can” cell wants to contain =vlookup(sheet2!b$4:d$60,sheet2!z1,2,false) where 60 is your bottom cell in the table of can/next.

“Next” cells as above but change the column number from 2 to 3

If the output is numerical then “can” becomes
=index(b$4:b60, sheet2!z1)
Next
Same but change the column from b to d[/s]

Ignore that it won’t work. I’ll send something across later

Ah – that’s a bit more complicated than I envisaged! It’s a lunch time job, rather than 2 mins while I’m watching the kettle boil task.

So you are going to need to start with an IF statement that modifys the lookup range of the can and level fields. Which might need to be done by concatenation, if you don’t want to break out the VBA. Will take some thought.

Daft question op but what is this for?

I love excel but this really really doesn’t look like an excel task to me. It wants to be a proper database as best i can see.

The can and next are both copied over from sheet 2. The only data I’d want to enter is the stage.

Can I make a long and ugly (but functional) IF and ELSE formula?

=IF sheet1D2 = Pre Level 1, Sheet1 C2=Sheet2 D4 and Sheet1 D2 = Sheet2 D4, ELSE Sheet1D2 = Early Level 1 etc.

Thanks for any help you can offer but please don’t waste too much of your time. I can copy and paste.

It just struck me.
Multiply the module number by the level. This will give you a list of discrete numbers that form column 1 of the look up array, after that it’s easy. You might need a couple of hidden columns that generates the level number as an integer rather than the text in your drop down box and multiples that by the module number.

Can I make a long and ugly (but functional) IF and ELSE formula

Yes. It might be a bit of PITA to unpick if you find a bug in it in two years time but if you understand nesting ifs and don’t get vlookup it would be a better approach for You.

=if(A1=1,”text for level1”,if(a1=2,”text for level2”,if(a1=3,”text for L3”,”error?”)))

I’ve got it.

=IF(D2 = "Pre Level One",H4, IF(D2 = "Early Level One", I4))

I can lengthen the code with the extra IF statements.

Just one question.

How do I reference Sheet2 A1 instead of H4?

Thanks a lot for the help.

Put Sheet2! In front of the cell reference.

You are going to generate some massive formula doing it that way, with good scope for errors due to missing parentheses, typos etc.

How do I reference Sheet2 A1 instead of H4?

sheet2!a1

If you use a$1 etc you should be able to copy paste those entries down your various names if you wish. Rather than needing to retype each one.

The more i think the more it’s a match index function i believe

Assuming text output to d2 etc….
Can=

=index(sheet2!c$4:c$32,match(d2,sheet2!b$4:b$7)+(b2-1)*5)

Next=
=index(sheet2!d$4:d$32,match(d2,sheet2!b$4:b$7)+(b2-1)*5)

The 5 at the end of each of those would be 4 if you get rid of empty lines

C$32 wants replacing with the relevant cell for the end of the range.

That should scale i think…

Google Sheets (unlike Excel) doesn’t like you clicking into other sheets in the workbook when adding the cell reference to an if formula, stupidly! You can do it with a straight reference, but not as part of an if formula, so you actually need to type Sheet2!A1 instead.

Anyway, MCH now has a version with both a vlookup and a nested if he can choose from!

Well done njee20. I’d realised that my suggested solution wouldn’t work as I thought, so was just about to try and work it out again. I can use the time much less productively now.

Thanks to all of you.

I’ve just signed up to a Udemy Excel / Sheets course too. So much more useful than the years of coding at Uni!