• 100% Satisfaction Guarantee

The-PC-Guy, Computer Manager
Category: Computer
Satisfied Customers: 1873
Experience:  20 years experience providing remote computer support
62934938
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: 1 year ago.
Category: Computer
Expert:  The-PC-Guy replied 1 year ago.

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

Customer: replied 1 year 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

Expert:  The-PC-Guy replied 1 year 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: 1873
Experience: 20 years experience providing remote computer support
Customer: replied 1 year 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 1 year ago.
tell me which cells you have the problem with and what the value should be and I will take a look
Customer: replied 1 year 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 1 year 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 1 year 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

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 1 year 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 1 year 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 1 year 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 1 year ago.
do I need to resend the file?
Customer: replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago.

Its the same one you sent back to me - attached

Expert:  The-PC-Guy replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago.
ok, first I will need you to open a new question for me as this is additional work.

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 1 year 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 1 year 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 1 year ago.

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

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

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

Should be there now?

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

How do I get the table?

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

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

Expert:  The-PC-Guy replied 1 year ago.
ok try this url

Customer: replied 1 year 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 1 year ago.
ok, please can you respond at this other url so we are on the same page?

Expert:  The-PC-Guy replied 1 year ago.
did you seee the response I posted to this other url

Customer: replied 1 year 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 1 year 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 1 year ago.
sure I will take a look, can you tell me which cell figure is wrong?
Expert:  The-PC-Guy replied 1 year ago.
also can we please work from this other url

just goto that url, and click reply to expert

there. And we will continue the conversation from there.

thanks
Expert:  The-PC-Guy replied 1 year ago.

### 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.

• ### Kamil Anwar

#### Satisfied Customers:

151
8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Previous | Next >

### Kamil Anwar

#### Satisfied Customers:

151
8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.

### James K.

#### Satisfied Customers:

93
Technical Director of IT Company

### Jon-Tech

#### Satisfied Customers:

61
20 yrs: Computers, Networking

### MikeW

#### Satisfied Customers:

53
32+ years Computer and Networking Experience

### jamieratliff

#### Satisfied Customers:

21
Jamie has worked in the Information Technology field for over a decade.

### TheDoctor

#### Satisfied Customers:

16
Experienced Software Engineer

### Bill Fisher

#### Satisfied Customers:

1
Computer system design, networks, integration, general support.