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

This answer was rated:

★★★★★

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 ?

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.

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

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 3 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

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.

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

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?

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:

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.

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.

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.

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

The purpose of this is to make the digits standard when you switch between columns.

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.

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 ?

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.

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.

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.

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?

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?

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!

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. and 2 other Computer Specialists are ready to help you