Programming

Programming questions? Ask a programmer for answers ASAP

Ask a Computer Expert,
Get an Answer ASAP!

This answer was rated:

Can you help me create a formula that can look at a vertical…

Can you help me create...
Can you help me create a formula that can look at a vertical range of cells and find only cells that have a value >0 in them. For any cell with a value >0 the formula should then read the name associated in a different cell on the same row and place it in the cell where the formula is. The vertical range of cells may be 50 to 120 cells high.
Show More
Show Less
Ask Your Own Programming Question
Answered in 4 minutes by:
6/25/2010
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified

Hi,

 

Would you like me to send you a sample file with the formulas, or do you prefer to send your file so I can set up the formulas directly on it.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
I can send you a copy of my spreadsheet with the location of where I would like the formulas written for. It will also hopefully help you understand exactly what I am trying to do. How do I attach the spreadsheet to send to you?

Ok to send the file go to www.wikisend.com and upload it there. You will then get a page that has the download link (the link looks something like this http://wikisend.com/download/527586/Book1.xls). Copy the download link and come back here and paste it in your reply.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

John,

 

Not sure I copied the link correctly as it doesn't look like an active link. Not sure if you can just type it into your browser to access. Anyway here is what I am trying to do. The spreadsheet I am sending you is one that I use for an auction draft for a fantasy football league. There are 10 teams in the league as demonstrated by the 10 column headings you will see in the sheet called "Draft Sheet 2". As players are drafted via the auction, the value paid for each player is typed in to the cell of that player under the column heading of the team that drafts them. Each team must spend at least $1 on each player they draft and each team must draft 5 Running Backs, 5 Receivers, 3 QB's, 2 Defenses, and 2 Kickers. What I am asking you to do, is help me create formulas that will list which players have been drafted by each team by position and list them on the sheet titled "Sheet 1". For instance if Team 1 - T&A pays $24 for Tom Brady, once the $24 is typed into the "Draft Sheet 2" row for Brady, Tom, the program would then place Brady, Tom in the quarterback range under T & A's column. Hope this makes sense.

 

http://wikisend.com/download/598964/2010 Draft Tracker 2.xls

I got the file, thanks.

 

I am sorry I am not able to quickly grasp your file or your latest description. I will be happy to assist you with your original question which is more in line with the assigned value to this question. So please try to elaborate a little on the initial question.

 

Thanks

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
In column G between G258:G313 of "Draft Sheet 2" there will be 3 cells that will receive values greater than 1 (the rest will be left blank). As those values are placed, I would like the program to read that the value is greater than 1 (as per my original help request) and then take the adjacent text in column B (i.e. - name) for those 3 rows and place that text in cells B12:B14 in "Sheet 1".

Much clearer now. Thanks!

 

Ok, here you go

 

FILE

 

The formulas are in th yellow cells on Sheet 1. They retrieve the names corresponding to the largest 3 values in the range G258:G314.

 

Hope this is ok. Let me know if they need any tweaking or if you have any questions, and thank you for clicking Accept if you are satisfied with the work.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

John,

 

I've been tied up in meetings this morning and haven't had a lot of time to review, but on first pass it seems to do exactly what I asked to have it do. After I have a bit of time later this morning to review, I will respond with the "Accept Answer" response. Thank you very much.

 

Bryan

Bryan,

 

Glad to know that the formulas are ok. Take your time, and thanks for updating me.

 

 

John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified
John D and 87 other Programming Specialists are ready to help you
Ask your own question now
Customer reply replied 8 years ago

John,

 

I did find one "glitch" and that is if the same value is placed in more than one vertical cell, it lists the first answer multiple times. Can you edit the formula to list it once and then list the next "text" that has the same $ value.

 

Bryan

Bryan,

 

That is the way the Large function works. If I understand you correctly, if you have these values in the data sheet

Sam 11

Joe 16

Chris 11

 

you want B12 to return "Joe", B13 to return "Sam", and B14 to return "text"

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

John,

 

Sorry if I wasn't clear, in the scenario you outlined above, I would want B14 to answer Chris, but right now it is answering "Sam" in B13 and B14.

Bryan, I am afraid that condition makes it progressively more complicated - a totally different approach. It would require either building a set of transition formulas placed in 3 or 4 helper columns, or a macro to be run on demand. In either case it would be difficult to modify later in case you want to apply it on different source data or in different destination cells.

 

 

 

 

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

John,

 

Thanks for the consideration. For any easy workaround I can just enter 8.9 in the place of 9 or .9 in the place of one. It rounds up for addition purposes, but still sorts properly. Thanks for all your help.

 

Bryan

Yes, that should work. Try it and let me know if it doesn't or if you need further assistance.

 

Ask Your Own Programming Question

Ok, I came up with a solution covering all possibilities using formulas.

 

FILE

 

Just so you know there are transition formulas in columns AA, AB, AC and AD on sheet 1, which of course you can hide.

 

Hope this is ok. Let me know if you have any questions.

 

 

 

Ask Your Own Programming Question
Was this answer helpful?

How JustAnswer works

step-image
Describe your issueThe assistant will guide you
step-image
Chat 1:1 with a programmerLicensed Experts are available 24/7
step-image
100% satisfaction guaranteeGet all the answers you need
Ask John D Your Own Question
John D
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
9,666 Satisfied Customers
Experience: Vast experience in Excel programming and business solutions

John D is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

ATLPROG

ATLPROG

Computer Software Engineer

7,677 satisfied customers

MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML

LogicPro

LogicPro

Computer Software Engineer

7,175 satisfied customers

Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.

unvadim

unvadim

Computer Software Engineer

1,168 satisfied customers

Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.

lifesaver

lifesaver

Computer Software Engineer

950 satisfied customers

Several years of intensive programming and application development experience in various platforms.

ehabtutor

ehabtutor

Computer Software Engineer

936 satisfied customers

Bachelor of computer science, 5+ years experience in software development, software company owner

TheDoctor

TheDoctor

Software Engineer

893 satisfied customers

M.S. in Internet Information Systems

The-PC-Guy

The-PC-Guy

Software Engineer

848 satisfied customers

Extensive Knowledge in PHP, MYSQL, CSS & Javascript

< Previous | Next >

Related Programming Questions
=VLOOKUP(C6,'NCS-3'!$A$1:$D$5510,2,FALSE) For this VLook,
For this VLook, sometimes the "C6" has multiple "C6", how do we VLook for these? … read more
The-PC-Guy
The-PC-Guy
Software Engineer
Bachelor's Degree
848 satisfied customers
I have daily stock results, one row per day. I would like to
I have daily stock results, one row per day. I would like to create a formula that indicates the cell value (a stock price) of the row before a row that I have named "Today". How can I do this? The re… read more
R. Michael
R. Michael
MIS Analyst
Master\u0027s Degree
22 satisfied customers
Hi, I need to create a ranking formula for some raw data. Can
Hi, I need to create a ranking formula for some raw data. Can anyone help create? (I have example of one I did manually on hand). Thanks… read more
Daniel
Daniel
Bachelor\u0027s Degree
1,568 satisfied customers
Hi John-Youve been such a great help and I appreciate
Hi John- You've been such a great help and I appreciate it. I'm having another issue with Excel. I have these spreadsheets with a lot of empty cells. I found that if I filled all the empty cells with … read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
Im not a very knowledgeable excel spreadsheet user and could
I'm not a very knowledgeable excel spreadsheet user and could use some help: I have a large number of rows, consisting of results for a series of physical fitness tests. The last cell in each row is a… read more
The-PC-Guy
The-PC-Guy
Software Engineer
Bachelor's Degree
848 satisfied customers
Hi there. I need to create a formula in Excel (and I am not a programmer) to do this If
Hi there. I need to create a formula in Excel (and I am not a programmer) to do this: If it is equal to the character '&' then insert If it is blank, then leave blank and takeaway the space. Also, whe… read more
Zabo04
Zabo04
Master\u0027s Student
Master\u0027s Degree
7 satisfied customers
Hello. I need help creating a formula in Excel. The formula
Hello. I need help creating a formula in Excel. The formula would reside in one worksheet and search and retrieve values from another worksheet. I am trying to create a formula for a cost estimating e… read more
Simit
Simit
Bachelor\u0027s Degree
2,001 satisfied customers
I have two separate lists with ranges determined by a top and a base depth (each on a
I have two separate lists with ranges determined by a top and a base depth (each on a separate column). I need to retrieve a value if the range on my second list falls within the range of the first li… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
hi I need to know how to lookup the URGENT HELP NEEDED
hi I need to know how to lookup the occurence of a value in a table in a specific column and return the resulting values in that row so I can use them I have used vlookup but it stops looking at the f… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
Excel table - I want to count the number of times an event
Excel table I want to count the number of times an event is paired with another on individual occasions. I will break it down so it may seem long winded but I am trying to be thorough because if I giv… read more
Bill Sprouse
Bill Sprouse
Bachelor\u0027s Degree
37 satisfied customers
I have a product database that i need to assign category numbers
I have a product database that i need to assign category numbers to depending on certain values. For example one column is the product type - ie shoes, hats, belts etc.. each of these has it's own cat… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
I need an Excel macro that will total the number of true values
I need an Excel macro that will total the number of true values and then present the total as a percentage of trues per the number of entered values. I am trying to calculate how often my employees ar… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
I have a workbook with multiple sheets that I need to select
I have a workbook with multiple sheets that I need to select a 10 cell verticle number array from one sheet and transpose this array to horizontal then paste it into another sheet in a different locat… read more
Arty
Arty
Senior Administrator
Bachelor\u0027s Degree
469 satisfied customers
I am a newby to VBA and need to put data in a cell that is
I am a newby to VBA and need to put data in a cell that is next to a cell that I obtain data from via a vlookup function. This is the vlookup formula I am using: BCN = Application.VLookup(CID, Sheets(… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
What is the formula for matching values in two lists with defferent
What is the formula for matching values in two lists with defferent ranges in rows and returning a value in an adjacent third column. In the example below I would expect the formula to compare list1 a… read more
jeff
jeff
Software Developer
Master\u0027s Degree
153 satisfied customers
Excel 2007 conditional foarmatting based on formau
Excel 2007 conditional foarmatting based on formaul. I have a column of azimuth values between 0 and 360 degrees in column =$P$4:$P$2572 and my vlookup puts the selected date for comparison in P1. I n… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
I have a data dump in 20 unique columns, and I am using the
I have a data dump in 20 unique columns, and I am using the Advanced function under the Data menu in 2007. I am trying to extract data with criteria using "OR" logic - so far it is only working as "AN… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
I am using Excel 2007 and Im trying to have values from column
I am using Excel 2007 and I'm trying to have values from column C to equal the values from column A. The values from column A were calculated from formulas. If I simply put in the formula "=A1", "=A2"… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x