(1) "If" Auto Entry ....and...(2) Auto Open Worksheet or Second Spreadsheet

[JohnBud]

Hi, I have 2 issues and would appreciate some assistance please.

(1)
I wish that when entering certain predefined data in a cell that, that another cell enters a certain value.

As an example, IF cell D3 = "Comedy", the H3 should have 25.00
However if cell D3 has "Romantic", then H3 should have 10.00 etc. etc.
There being up to say 50 options for cell D3 and then 50 related amounts for cell H3.

The same equation then exists for D4 and H4 etc. etc

The list of items can be fixed and maybe of it were a drop down box of options that also be helpful.

Can this type of IF scenario be done in excel?


Thank you.

(2)
I wish that when entering a "Y" in a certain cell that it automatically take you to another area of the worksheet or to a separate worksheet or even a separate worksheet in another file.

As an example, IF cell F3 = "Y", then the cursor opens another worksheet or just automatically appears in an other area so that other data can be completed.

If F3 or G3 or H3 etc. etc. has "Y" entered, then I am prompted to another cell (maybe in another worksheet) to prompt me to put in certain data. That data will not be fixed, it will be different every time. it may be; director first name, director second name, genre of film, made in location country etc. etc.

The second of these is the least of the problems as at the worst I can just open a separate area and will just need to remember. Just a thought on the second issue, I wonder if say the "Y" flag is entered, that a warning note appears like a hint.

Anyway the first issue is the main one please so any assistance appreciated.

Again, many thanks

JohnBud

[Simi]

The first issue can be accomplished with a Vlookup.

your second task could be accomplished, but by the way I know it would make your sheet run slowly as you would have to check what cell data has just been entered in every time you enter data.


The Vlookup is fairly simple to use, it consists of making a table for your data comparisons and the results you want.
This table can be on the same sheet or another sheet. The first column needs to be your lookup values sorted in alphabetical order, the second column is the values you want to return.
so your formula for H3 would look like =VLOOKUP(D3,Y1:Z50,2,TRUE) D3 Is the value to lookup, Y1:Z50 is where your table is, 2 means we want to return the 2nd columns information (you can have more than 2 columns in the table) and TRUE means the data has to match exactly.
To ensure your data is matched exactly you can make the dropdown box as you requested by clicking on cell D3, and from the data tab, clicking on data validation. You then choose "List" from the allow menu, and then select the first column in your table in this example Y1:Y50.

I have also attached a sample file for you to reference.

Vlookup-example.xlsx

[JohnBud]

The first issue can be accomplished with a Vlookup.

your second task could be accomplished, but by the way I know it would make your sheet run slowly as you would have to check what cell data has just been entered in every time you enter data.


The Vlookup is fairly simple to use, it consists of making a table for your data comparisons and the results you want.
This table can be on the same sheet or another sheet. The first column needs to be your lookup values sorted in alphabetical order, the second column is the values you want to return.
so your formula for H3 would look like =VLOOKUP(D3,Y1:Z50,2,TRUE) D3 Is the value to lookup, Y1:Z50 is where your table is, 2 means we want to return the 2nd columns information (you can have more than 2 columns in the table) and TRUE means the data has to match exactly.
To ensure your data is matched exactly you can make the dropdown box as you requested by clicking on cell D3, and from the data tab, clicking on data validation. You then choose "List" from the allow menu, and then select the first column in your table in this example Y1:Y50.

I have also attached a sample file for you to reference.

Vlookup-example.xlsx

Thank you very much Simi, that is very helpful.

Regarding the second issue and on the basis of what you have said about it all running slow, is there a simple way that when say a "Y" is entered it throws up a flag, hint or something in the other cells to say that an entry is now expected and that details should be entered?

Maybe a warning in the other fields that data is expected as the entry in that other cell was "Y". I may not be explaining this very well...what I am trying to say is that is there a way that in completing say cell F3 as "Y", can it then show in cells J3, K3, L3 etc. that data is expected, maybe a word like "Expected" or something like that?

Likewise for cell F4 being "Y" that details is expected in J4, K4, L4 etc.?

Again, many thanks.

[JohnBud]

Sorry Simi. also forgot to ask regarding question1 that you kindly answered...what is the code if the table as you describe is in another spreadsheet file or probably more realistically just a different worksheet within the file, say "Sheet3"? How would your example =VLOOKUP(D3,Y1:Z50,2,TRUE) look?

Again, thank you.

[Simi]

I'm not sure if you can use a vlookup to use a table from a different workbook.
if it is on a different sheet it just changes the cell reference.
so =VLOOKUP(D3,Y1:Z50,2,TRUE) will turn into =VLOOKUP(D3,Sheet3!Y1:Sheet3!Z50,2,TRUE)

The new requirements for your second request can be done as such.
in cells J3, K3, L3 simply put the following formula =IF(F3="Y","Expected","") this formula can then be copied down to row 4 as well.

I need to note on the Vlookup formula, you should enter the table with $'s so that the range of the table won't change as you copy the formula down.
=VLOOKUP(D3,Sheet3!$Y$1:Sheet3!$Z$50,2,TRUE)

[JohnBud]

Brilliant Simi, thank you so very much.

I was struggling with the lookup value changing when pasting, so that was also very helpful...

[JohnBud]

Simi, so many things working now and going well but am not sure if you can help with this next one please.

I have tried to do this numerous ways, looked it up and the online help too but am missing something. I thought it would be similar to the original "IF" above but no joy in playing with that.

I would like cell M3 to look at N3 and if the value in N3 is greater than 3.0, add 2 to that value and display that new value in cell M3.
If however the value in N3 is equal to or less than 3.0 I would like to add just 1 to the value shown in N3 and display that in M3.

Then just copy this down the column.

Any guidance would again be appreciated...or if you rather just push me in the right direction rather than me keep asking and numerous making posts, please.

Many thanks.