How JustAnswer Works:
• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
Ask The-PC-Guy Your Own Question
The-PC-Guy, Computer Manager
Category: Computer
Satisfied Customers: 2079
Experience:  20 years experience providing remote computer support
62934938
Type Your Computer Question Here...
The-PC-Guy is online now

I need some more help with the table you helped me with before

This answer was rated:
I need some more help with the table you helped me with before - some of the sums have become corrupt and I can't see how - can you help please asap
Thanks for using JustAnswer. My name is***** will do whatever I can to answer your question

is there any chance you can send the sheet you are having problems with so I can help further?

http://ww2.justanswer.com/how-do-i-send-photo-or-file-expert
Customer: replied 2 years ago.

Hi Andrew - thank you for responding

I have attached the file. This was originally sorted by one of your colleagues but seems to have some issues.

You will see that some of the choices are giving N/A or VALUE! responses. But also, if you look at the top section, under CCG - Cellulitis - the choice is Airedale, Wharefedale etc - and if you look at the value given for cost is it £964.20, but on the actual sheet the value should be £248,764. This means the cost per episode value is also very wrong. Hope this makes sense and you can sort this for me

Graeme

sorry for the delay for some reason I was never notified of your response.

I see you are doing a multiple vlookup. That may be part of the issue.

Where is the cost supposed to populate from? Lets start with that.

It looks like you are trying to populate a cost from the E column.

so try this formula in your L8

and let me know if it gives you the correct value.

=VLOOKUP(\$C8,'CCG List Cellulitis'!\$A\$6:\$V\$217,5,FALSE)
The-PC-Guy and 5 other Computer Specialists are ready to help you
Customer: replied 2 years ago.

That one seems to work OK - and I think I can make this apply to all other cells - have you had chance to look at the other issues with the error messages?

tell me which cells you have the problem with and what the value should be and I will take a look
Customer: replied 2 years ago.

If you look at the sheet provided labelled Analysis Tool the problem is when you select Hosiery Spend per 1000 patients or Total Hosiery Spend you get error messages in cells P8, Q8 and R8 and down from there

Customer: replied 2 years ago.

There is also a similar issue when you select CCG population - see cell G19, 20 et al for this issue

i'm going to give you a different formula that you may be able to apply more universily

it seams like you are trying to get the intersection of 2 vlookups, or the intersection of a column Heading and the row selection to get the correct price

=INDEX('CCG List Cellulitis'!\$B\$6:\$V\$217,MATCH(\$C8,'CCG List Cellulitis'!\$A\$6:\$A\$217,0),MATCH(L\$6,'CCG List Cellulitis'!\$B\$5:\$V\$5,0))

that is for cell L8, you should then be able to copy and paste special formulas to the other cells. Now the only issue your going to have is the absolute reference to the top of the column the formula is copied to in a box, L\$6 in the formula, you will have to change that for each box you copy to when I say box, I mean cells L8 to L13 refer to L6 in that formula, Cells L19 to L24 would refer to L17, and so on, that column would then be good to be coppied to the entire row.

You know what its just faster for me to do it

http://ge.tt/9jf3gBA2/v/0?c

there is the modified file, let me know if you have any problems understanding

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE SMILEY FACES BENEATH THIS ANSWER

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.
I think we may be in different time zones or something.

Did you get a chance to try the new formula. I also sent a modified version of your sheet.
I think we may be in different time zones or something.

Did you get a chance to try the new formula. I also sent a modified version of your sheet.
Customer: replied 2 years ago.

The file has become corrupt in that the middle table has the wrong values - only noticed it when I tried to show it to a colleague ???

do I need to resend the file?
Customer: replied 2 years ago.

Not yet - the file is not compiled correctly. If you look at the middle table it should run from the worksheet entitles CCG List Lower Limb Ulcer - but it runs from the worksheet CCG List Cellulitis and therefore gives the same results as the top table. The INDEX sum needs changing to the correct worksheet

if you see the formula

=INDEX('CCG List Cellulitis'!\$B\$6:\$V\$217,MATCH(\$C8,'CCG List Cellulitis'!\$A\$6:\$A\$217,0),MATCH(L\$6,'CCG List Cellulitis'!\$B\$5:\$V\$5,0))

assuming that everything on another sheet is in the same cells

the 'CCG List Cellulitis' is the name of the sheet, that is the part of the formula between the single quotes, you would have to change all instenses of that value I think there are 4 in the formula. Change those to the name of whatever other sheet you want to refer to.
Customer: replied 2 years ago.

I tried that but on things like CCGpopulation etc I then get an N?A response - is it possible you could change the sheet to point to the correct worksheet?

ok, why don't you send me your latest sheet, and I will take a look at it as soon as possible
Customer: replied 2 years ago.

Its the same one you sent back to me - attached

so you are saying that the formula for one of the tables needs to come from a different sheet and set of values entirely?
Customer: replied 2 years ago.

Yes - the one marked CCG List Lower Limb Ulcer for the middle table and the bottom table should be a combination of both - need this quite urgently please

ok so the middle one comes from a different sheet, and when you say the bottom one should be a combination of both, do you mean to SUM the 2 values?
Customer: replied 2 years ago.

Yes please - and the average in the lower table needs to work out the average of those 2 values

ok, first I will need you to open a new question for me as this is additional work.

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.
Customer: replied 2 years ago.

But this is the work that should have been done the first time? I am desperate so if needed I will pay again but if this had been done the first time around it wouldn't need doing now

i thought that what was originally requested was done in the first place. I beleive you asked to modify the formula in G8. Which I gave you.

Yes please open another question and I will get to it right away as soon as I see your question on the list.
Customer: replied 2 years ago.

I have opened another question - please let me know when you get it

it didn't show up on my list yet?

Did you request me for it?
yes, I am not seeing any new open questions on your profile
Customer: replied 2 years ago.

Should be there now?

yes, posted reply to your new question, with 2nd table values, let me know if those are corerect, before I do the third one
Customer: replied 2 years ago.

How do I get the table?

please refer to your new question that you just opened, i belive you should have a my questions link somewhere on your screen
Customer: replied 2 years ago.

The only thing I am getting are these responses from you?

ok try this url

http://www.justanswer.com/computer-programming/8xr84-update-spreadsheet-per-previous-convers.html
Customer: replied 2 years ago.

Yes that looks OK - the bottom section now needs to be a combination of the 2 other sections, and the weighted average needs to work out the average of the 2 as well please

ok, please can you respond at this other url so we are on the same page?

http://www.justanswer.com/computer-programming/8xr84-update-spreadsheet-per-previous-convers.html
did you seee the response I posted to this other url

http://www.justanswer.com/computer-programming/8xr84-update-spreadsheet-per-previous-convers.html
Customer: replied 2 years ago.

I responded to say the middle box seems ok now the bottom book needs to be a combination of the two top boxes and the bottom weighted national average should be a combination as well

Customer: replied 2 years ago.

Actually can you look at the weighted national average in the top section - when I choose cost the figure just can't be right - the figures are up in the hundreds of thousands and the average in below 1000 ?

sure I will take a look, can you tell me which cell figure is wrong?
also can we please work from this other url

http://www.justanswer.com/computer-programming/8xr84-update-spreadsheet-per-previous-convers.html

just goto that url, and click reply to expert

there. And we will continue the conversation from there.

thanks