How JustAnswer Works:

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

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 6406
Experience:  Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
49766785
Type Your Computer Question Here...
Jess M. is online now

Can excel find a value for an entry in a column - which is

Resolved Question:

Can excel find a value for an entry in a column - which is a drop down box so changes - and cross reference it with an entry from a row - which is also a drop down box ***** *****es ?
Submitted: 2 years ago.
Category: Computer
Expert:  Jess M. replied 2 years ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Yes it is possible. But can you please send me a copy or dummy or sample of the Excel file that you are working on so that I can check your actual requirements?

You can attach the sample Excel file here using the paper clip icon. It is intended for pictures but will also work with Excel files.

Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 2 years ago.

Attachment: 2014-11-13_062319_example_1.xlsx

Hope this has attached. You will see what I need to do is to be able to select a value in the row on the left and have it find the value of the columns going across. These will change as they are drop down so that you can compare any value in the row to any value in the column

Thank you

Expert:  Jess M. replied 2 years ago.
Graeme,

Thank you for writing back with the attachment. I got the sample file.

Are you working on the sheet called Analysis Tool? If yes, please let us try a sample task.

Do you want Excel to populate G8 to P8 corresponding to the selection in C8? And where will these data be pulled from? Which sheets?

Jess
Customer: replied 2 years ago.

Yes the Analysis Tool Sheet. The top box where it says Cellulitis in B7 (I think) - this will pull information from the sheet marked CCG List Cellulitis - so once you have selected the name in the row I have marked 1 (currently NHS Barnet CCG) it will find the value corresponding to the G6 value also on the CCG List Cellulitis (so currently it will find the value for NHS Barnet CCG Episodes per patient and put in that value. If you change the name in the row or the value in the Column it will find the new answer. Hope that makes some sense

Customer: replied 2 years ago.

The bottom table marked lower limb ulcer will do the same looking at the information in the sheet marked CCG List lower limb ulcer

Expert:  Jess M. replied 2 years ago.
Graeme,

I am starting to understand your requirements fully. It is better to have at least one sample data then,

In your #1, when NHS Barnet CCG is selected,the next columns should be:
Episodes = 426
Episodes per Patient = 1.43
Cost per Bed Day = 301

And so on?

And when the selection is changed, these values will change.

Please confirm so that I can build the formula for you.

Best regards,
Jess
Customer: replied 2 years ago.

Yes that sounds right - but important that when both row and column are changed the values change with them - thank you so much

Expert:  Jess M. replied 2 years ago.
Graeme,

I see that the drop downs are only the numbered 1 - 5 for Cellulitis and 1 - 5 for Lower Limb Ulcer.

So you are saying, that the data that will be pulled out if it is Cellulitisis the sheet called CCG List Cellulitis. If it is Lower Limb Ulcer, then the data to be pulled out is the sheet CCG List Lower Limb Ulcer.

Is this correct?

Jess
Customer: replied 2 years ago.

Yes - this just allows comparison for up to 5 accounts (the ones listed as CCGs) at a time across a changing variety of measures in two different disease areas

Expert:  Jess M. replied 2 years ago.
Graeme,

Thanks for the confirmation. Will the labels Cellulitis (top group) and Lower Limb Ulcer (bottom group) ever change? Or they will stay in their places and only the five items are changeable through the drop downs?

Jess
Expert:  Jess M. replied 2 years ago.
Graeme,

Thank you for your patience. I have completed the requirements and you can download the finished file in the link below.

However, here are my findings:
  1. This Analysis Tool sheet is just pulling data from the 2 source sheets.Therefore, the figures are just pulled out using the VLOOKUP function and this does not affect the actual value of the data in a cell.
  2. For instance, the actual data stored in cell G6 in sheet CCG List Cellulitis is 1.45762711864407 since it is a result of the DIVISION formula =SUM(F6)/C6. It is only showing there as 1.46 because it is formatted with a CUSTOM format #,###.00 which means figures will use commas as thousands separator, with 2 decimal places.
  3. Now, during the LOOKUP, the pulled out value in this sample case is 1.45762711864407 and NOT the 1.46 because Excel only oulls out the actual figure. Therefore, in this case, you still need to format the columns to use CUSTOM format #,###.00. This is tidious job though.
  4. So in the bottom group (using the sheet CCG List Lower Limb Ulcer), I tried to use the ROUND function with the syntax Round(number, decimal places). Then I followed your displayed decimal places. For instance, I use ROUND(xxx,2) for Episodes per Patient column since you originall limit the decimal there to 2 places. The resulting formula is =ROUND(SUM(F6)/C6,2), the original formula was =SUM(F6)/C6
  5. The purpose of this is to make the digits standard when you switch between columns.
  6. Also, to meet your functionality requirements, I needed to create a small table at the bottom of the 2 sheets, in cell range $A$300:$B$320. This table is required in the VLOOKUP function so that the values will switch when you change the column headings using the drop downs.
  7. NOTE: I only did this in the bottom group.

Here is the download link:

http://wikisend.com/download/339420/example_1_rev1.xlsx

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess

Customer: replied 2 years ago.

Hi - this is awesome - could I push my luck a little more - happy to pay a little more if needed.

Could a row be added with the value of the National Average (available on each sheet at the bottom) - and then the cells conditionally formatted so that if above average they turn light red and is below they go green and if equal they go amber ?

G

Expert:  Jess M. replied 2 years ago.
Graeme,

Thank you for writing back. Do you want this National Average in #6 item in each group (Cellulitis and Lower Limb Ulcer)?
Expert:  Jess M. replied 2 years ago.
Graeme,

I am sorry for the delay. Here is the second revision of your file. I added a row in each group called National Average and then compared the results to the actual AVERAGES an performed conditional formatting. It requires a lot of steps so it really took me some time to complete it.

The clue is, I needed to include the "Averages" row in the LOOKUP and just HIDE it after. That is why in the Analysis Tool sheet, you will see Row 14 and Row 25 hidden. They are the actual "Average" where the comparison in the Conditional Formatting is being based.

Here is the final file:
http://wikisend.com/download/975692/example_1_rev2.xlsx

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. If you want to pay more for this additional requirements, you can leave me a tip or bonus.Cool

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess
Customer: replied 2 years ago.

Hi

Sorry but that wasn't what I was looking for. I wanted to be able to rate the cost in the box on the analysis tool against the average on the Cellulitis / Lower Limb sheets - not the average of the 5 entries on the analysis tool - and if possible to shade the cell itself red or green - not sure if this is possible?

Expert:  Jess M. replied 2 years ago.
Hi,
So you want to apply conditional formatting to the actual entries in the 5 rows against the average?
Customer: replied 2 years ago.

Hi

I think I have managed to do that now thanks. The report is almost ready - just looks a little untidy as cannot make the cells give details in £s or just numbers and make the number of decimal points consistent as this changes with each choice made - but it looks pretty good so far. I may need to develop this further - is there an easier way to contact you if I need your help or just through this system?

Expert:  Jess M. replied 2 years ago.
Graeme,

Thank you for writing back with that information. I am glad the report is almost ready now and I am glad that I was able to help.

Yes, that is also my concern in your report since the column headings are VARIABLE (they can change using drop-downs), you need to FORMAT the column to meet the actual format of the data being pulled.

And yes, you can request me any time here in JustAnswer by posting a new question starting with "For Jess M" so that I can help you immediately.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 6406
Experience: Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
Jess M. and other Computer Specialists are ready to help you

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 Customer
< Previous | Next >
  • 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 Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • 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!!!! Alex
  • 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. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • 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. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • Kamil Anwar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    156
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg Kamil Anwar's Avatar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    156
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    93
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/JO/JonTech/2011-10-25_111835_A2c.64x64.jpg Jon-Tech's Avatar

    Jon-Tech

    IS Manager

    Satisfied Customers:

    61
    20 yrs: Computers, Networking
  • http://ww2.justanswer.com/uploads/JA/jamieratliff/2012-2-8_194551_jamie.64x64.jpg jamieratliff's Avatar

    jamieratliff

    Information Systems Manager

    Satisfied Customers:

    21
    Jamie has worked in the Information Technology field for over a decade.
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERleg4q7o8/2012-3-6_203036_meja.64x64.png TheDoctor's Avatar

    TheDoctor

    Software Engineer

    Satisfied Customers:

    16
    Experienced Software Engineer
  • http://ww2.justanswer.com/uploads/FI/FisherEngineering/2012-1-18_194422_Fisher375Headshotcompressed239K.64x64.jpg Bill Fisher's Avatar

    Bill Fisher

    Computer Engineer

    Satisfied Customers:

    1
    Computer system design, networks, integration, general support.
  • http://ww2.justanswer.com/uploads/ZE/zeyank/2014-12-9_221254_zeyank.64x64.png Ryan H.'s Avatar

    Ryan H.

    Computer Support Specialist

    Satisfied Customers:

    1739
    A+ Certified Technician - 10 Years experience working with all types of computer systems.