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 The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Computer
Satisfied Customers: 1920
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

Customer Question

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
Submitted: 2 years ago.
Category: Computer
Expert:  The-PC-Guy replied 2 years ago.
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

Attachment: 2015-02-09_220727_master_copy_-_ccgs_-_v_2.0.xlsx

Expert:  The-PC-Guy replied 2 years ago.
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, Computer Manager
Category: Computer
Satisfied Customers: 1920
Experience: 20 years experience providing remote computer support
The-PC-Guy and 9 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?

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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.
Expert:  The-PC-Guy replied 2 years ago.
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.
Expert:  The-PC-Guy replied 2 years ago.
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 ???

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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?

Expert:  The-PC-Guy replied 2 years ago.
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.

Attachment: 2015-02-16_233923_2015-02-09_220727_master_copy_-_ccgs_-_v_2.0-ja-mod.xlsx

Its the same one you sent back to me - attached

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
it didn't show up on my list yet?

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

Should be there now?

Expert:  The-PC-Guy replied 2 years ago.
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?

Expert:  The-PC-Guy replied 2 years ago.
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?

Expert:  The-PC-Guy replied 2 years ago.
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

Expert:  The-PC-Guy replied 2 years ago.
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
Expert:  The-PC-Guy replied 2 years ago.
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 ?

Expert:  The-PC-Guy replied 2 years ago.
sure I will take a look, can you tell me which cell figure is wrong?
Expert:  The-PC-Guy replied 2 years ago.
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

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.