Solved

How would I fill an xlsx column according to the body of this post?

Posted on 2013-11-26
10
187 Views
Last Modified: 2013-11-26
I have a large excel spreadsheet with a column I that contains  three different entrees -
S-1_labs, PRN and Li_labs.   In column J  would like  57 when column I = S-1_Labs, 86 when column I = PRN  and 60 when column I = Li_labs. What equation can I use to avoid entering these numbers into column I manually?
Thanks
0
Comment
Question by:PDSWSS
  • 6
  • 4
10 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Couple of options, as its only 3 values the simplest would be a nested IF statement.

In column J, assume starting row 2, the following formula:

=IF(I2="S-1_labs",57,IF(I2="PRN",86,60))

For a larger set of variables I would create a lookup table.

Thanks
Rob H
0
 

Author Comment

by:PDSWSS
Comment Utility
I should have mentioned that there are also blank cells where neither of the three values
are entered.  Your equation will enter "FALSE" in these fields.
Would you be able to send me an equation that will address this 4th possibility?
Thanks
0
 

Author Comment

by:PDSWSS
Comment Utility
Second equation to delete all False cells?
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
=IF(I2="S-1_labs",57,IF(I2="PRN",86,IF(I2="Li_Labs",60))
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
Apply autofilter to data and select "FALSE" in column J.

Deleting rows will only delete those visible.

Thanks
Rob H
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:PDSWSS
Comment Utility
Filtering in this situation not optimal.
Would be better to use an equation,  where  cell = false in column J then delete "False"
0
 

Author Comment

by:PDSWSS
Comment Utility
Thank you very much for the solution.  FYI should be   )))   at the end not  ))
0
 

Author Closing Comment

by:PDSWSS
Comment Utility
Thanks again.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Formula can't be used to generate delete but could change to blank rather than false. Put ,"" before closing brackets.
0
 

Author Comment

by:PDSWSS
Comment Utility
Thanks again.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now