• Welcome to DOSBODS

    Please consider creating a free account to be able to access all the features of the DOSBODS community. It only takes 20 seconds!

Reck B

Excel nerds - HELP !

Recommended Posts

Posted (edited)

I have a drop-down in cell C3 of an excel workbook.

The 2 options are

  • New Enquiry
  • Annual Review

Depending which option has been chosen in cell C3, I want to display the text from one of 2 cells from another worksheet, call Statements.

New Enquiry should display text from Statements!J3
Annual Review should display text from Statements!B3

Here's what I've botched together;

=IF(C3="New Enquiry",Statements!J3,"",IF(C3="Annual Review",Statements!B3,""))

 

 

Why the shitting hell isn't that working?

 

 

 

 

 

 

Edited by Reck B

Share this post


Link to post
Share on other sites

Does this work:

=IF(C3="New Enquiry ",Statements!J3,IF(C3="Annual Review",Statements!B3,""))

This assumes that the region "Statements" is properly defined.

Share this post


Link to post
Share on other sites
4 minutes ago, DTMark said:

Does this work:

=IF(C3="New Enquiry ",Statements!J3,IF(C3="Annual Review",Statements!B3,""))

This assumes that the region "Statements" is properly defined.

It did after I took the rogue space after Enquiry ;-)

Muchas gracias !

Share this post


Link to post
Share on other sites

Jolly good.

I tend to find that auto-complete helper thing with formulae in Excel is more of a hindrance and usually just "muddle through" until either it works, or I lose my temper with it.

Share this post


Link to post
Share on other sites

The part that makes excuses for their work like claiming that a space randomly inserted itself.

That said, the reason I was able to reply so quickly was because I had Excel open so it took just moments to replicate that setup.

And, there is no space in the formula in Excel. It did indeed randomly insert itself.

Thank you. Come again.

Share this post


Link to post
Share on other sites
16 minutes ago, DTMark said:

The part that makes excuses for their work like claiming that a space randomly inserted itself.

That said, the reason I was able to reply so quickly was because I had Excel open so it took just moments to replicate that setup.

And, there is no space in the formula in Excel. It did indeed randomly insert itself.

Thank you. Come again.

I'm back..

So I want to throw another option in the drop-down called Change in Circumstances which should also return the text from Statements!B3

 

My brain says this should work.

 

=IF(C3="New Enquiry",Statements!J3,IF(C3="Annual Review",Statements!B3,IF(C3="Change in Circumstances",Statements!B3"")))

 

But excel is offended by my idiocy. What have I done wrong?!

 

 

Share this post


Link to post
Share on other sites
2 minutes ago, Reck B said:

I'm back..

So I want to throw another option in the drop-down called Change in Circumstances which should also return the text from Statements!B3

 

My brain says this should work.

 

=IF(C3="New Enquiry",Statements!J3,IF(C3="Annual Review",Statements!B3,IF(C3="Change in Circumstances",Statements!B3"")))

 

But excel is offended by my idiocy. What have I done wrong?!

 

 

You've got four sections separated by commas rather than three, you need to have those last two within brackets and working as an IF statement.

Share this post


Link to post
Share on other sites
1 hour ago, DTMark said:

Excel ............ or I lose my temper with it.


It wouldn't do the dates for me the other week........ the drag a list of dates out. Love it for bee calendars. Hugely frustrating. 

Share this post


Link to post
Share on other sites
Posted (edited)
1 hour ago, ashestoashes said:

=IF(C3="New Enquiry",Statements!J3,IF(or(C3="Annual Review", C3="Change in Circumstances"),Statements!B3,""))

 

 it worked perfectly. many thanks..

 

 

 

 

Edited by Reck B

Share this post


Link to post
Share on other sites
22 minutes ago, Reck B said:

 it worked perfectly. many thanks..

Wait until you have 18 more posts and find a whole thread about asking people to work for free.

Just sayin ...

Share this post


Link to post
Share on other sites
Posted (edited)

'kin 'ell. None of it's working now...How hard should this be?

If C3 = "Annual Review", show F1
If C3 = "Change In Circumstances", show F1
IF C3 = "New enquiry", show F2

I've found that excel does some spazzy things if the cell is merged, or set as general/text whatever.

 

Reck B spends a whole day on an excel formula..

 

 

 

 

Edited by Reck B

Share this post


Link to post
Share on other sites

An easy cheat:

Make 3 new columns

Put separate formulae into each one to insert your value if the condition matches

In the 'real' column put in another IF block to take whichever of the three is not empty

Hide the first 3 columns

Share this post


Link to post
Share on other sites
35 minutes ago, Hopeful said:

Can't stand excel, or any spreadsheets tbh, my brain just doesn't seem to like them, can't 'visualise' them at all. much prefer SAS

Who dares wins

Share this post


Link to post
Share on other sites
1 hour ago, Reck B said:

'kin 'ell. None of it's working now...How hard should this be?

If C3 = "Annual Review", show F1
If C3 = "Change In Circumstances", show F1
IF C3 = "New enquiry", show F2

I've found that excel does some spazzy things if the cell is merged, or set as general/text whatever.

 

Reck B spends a whole day on an excel formula..

 

 

 

 

Or certainly at the very least as much time as it would take to pull an old carpet up.

Share this post


Link to post
Share on other sites
1 hour ago, Hopeful said:

Can't stand excel, or any spreadsheets tbh, my brain just doesn't seem to like them, can't 'visualise' them at all. much prefer SAS

Lotus 123 and dataease for me.

I like to keep up with the times.

Share this post


Link to post
Share on other sites
21 minutes ago, Panther said:

I remember Supercalc and mBasic

Super alcoholic and mbasic??? I was still using viewsheet until well into the 90s (school didn't have much of an IT budget:) )

Share this post


Link to post
Share on other sites
41 minutes ago, Panther said:

I remember Supercalc and mBasic

I recall connecting my BBC model B to a golf ball typewriter. Which was a hell of a lot easier than creating a network using Amstrad hardware.

Share this post


Link to post
Share on other sites
5 hours ago, Cunning Plan said:

I recall connecting my BBC model B to a golf ball typewriter. Which was a hell of a lot easier than creating a network using Amstrad hardware.

Should have checked your landline phone they were probably trying to email the instructions to it.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.