• 100% Satisfaction Guarantee
Dr. Dan, Computer Software Engineer
Category: Microsoft Office
Satisfied Customers: 1081
Experience:  B.S. Electrical & Computer Engineering, 20+ years technical experience
7043301
Type Your Microsoft Office Question Here...
Dr. Dan is online now

# Hi Dr Dan I need you to perform another series of calculations

Hi Dr Dan
I need you to perform another series of calculations for me

If cell L26 = Y
then Cell B19 = the amount in cell L29
Cell B20 = the amount in cell L30
Cell B21 = the amount in cell L31
Cell B22 = the amount in cell L32
Cell B23 = the amount in cell L33
Cell B24 = the amount in cell L34
Cell B25 = the amount in cell L35
Cell F17 = the amount in cell M29
Cell F18 = the amount in cell M30
Cell J18 = the amount in cell N29

If cell O26 = Y
then Cell B19 = the amount in cell O29
Cell B20 = the amount in cell O30
Cell B21 = the amount in cell O31
Cell B22 = the amount in cell O32
Cell B23 = the amount in cell O33
Cell B24 = the amount in cell O34
Cell B25 = the amount in cell O35
Cell F17 = the amount in cell P29
Cell F18 = the amount in cell P30
Cell J18 = the amount in cell Q29

If cell R26 = Y
then Cell B19 = the amount in cell R29
Cell B20 = the amount in cell R30
Cell B21 = the amount in cell R31
Cell B22 = the amount in cell R32
Cell B23 = the amount in cell R33
Cell B24 = the amount in cell R34
Cell B25 = the amount in cell R35
Cell F17 = the amount in cell S29
Cell F18 = the amount in cell S30
Cell J18 = the amount in cell T29

Also If Cell L26 = Y then Cell A15 = the text that is in Cell L27
If Cell O26 = Y then Cell A15 = the text that is in Cell O27
If Cell R26 = Y then Cell A15 = the text that is in Cell R27

This seerms a lot to me so am happy to give you a good bonus at the end
Thanks
John

Dr. Dan :

Hi John, I got your request, let me start taking a look into this

Dr. Dan :

Working on it right now

Customer:

No worries thanks

Customer:

Dan, I am about to go into a meeting which should be about 1 hour. Will check back with you when out of meeting. Ok

Dr. Dan :

Perfect

Dr. Dan :

I think I'll have it done by then

Customer:

Thanks

Dr. Dan :

All done!

Dr. Dan :

Attached is the .xlsx of the file I have created for you: http://goo.gl/Q6Lm7

Dr. Dan :

Click download in the bottom right of the screen to save to your computer and open it up

Dr. Dan :

Note I built in some error checking into the cells such that only one "Y" can be on of L26, O26, R26 which are data validated (restricted) to a drop down Y,N list (click on either of the cells, Data tab > Data validation and you can see they are type list, with source "Y,N")

Dr. Dan :

If there is more than one Y then all of the fields will show ERROR

Dr. Dan :

if all of them are N all the fields will show 0

Dr. Dan :

Then when you toggle the Y respectively, the fields as requested should show what you have instructed

Dr. Dan :

I have the place holder data in italics and the results in bold

Dr. Dan :

Play around with that file and let me know if that meets your needs!

Dr. Dan :

Thanks for personally requesting me to do this task, I appreciate the repeat consultation

Dr. Dan :

When you get back from your meeting, try this out and let me know if it needs any tweaking. I will leave the chat open so feel free to type in when you are back

Customer:

Hi Dan

Customer:

I am waiting for the pass code to get on to the site in order to download your work

Dr. Dan :

Whoops, sorry I didn't change the permission

Dr. Dan :

try again

Dr. Dan :

Dr. Dan :

(Control S) to save it once you have opened it

Customer:

Thanks....I will see how it goes and get back to you soon

Customer:

John

Customer:

Hi Dan

Customer:

The drop down list Y,N is not working. I get an error message 'The value you entered is not valid'. A user has restricted values that can be entered into this cell'

Dr. Dan :

Hmm

Dr. Dan :

Let me change that

Dr. Dan :

Here's the revised one without data validation:

Dr. Dan :

Just be sure to use Y

Dr. Dan :

as mentioned

Dr. Dan :

Will only work if one and only one of the three fields has Y in it

Dr. Dan :

otherwise will throw error

Dr. Dan :

does that work out for you?

Customer:

That is better but not all of the data comes across into all of the cells

Dr. Dan :

What cells don't get populated?

Dr. Dan :

I think this is working to the outline that you provided

Dr. Dan :

Let me know what data is missing

Customer:

Sorry, but L27, O27 and R27 should be L26, O26 and R26 (my mistake)

Customer:

Hang on that is not right...let me have another look

Customer:

F17 and F18 should be F18 and F19 (my mistake)

Customer:

Also the text in L27, O27 and R27 do work but are slow to transfer

Dr. Dan :

ok let me make some edits

Dr. Dan :

What version of excel do you use?

Dr. Dan :
Dr. Dan :

Customer:

Office pro 2010 also the cells are merged cells. would that make a difference

Dr. Dan :

the A15 text changes are near instantaneous for me in Excel in 2010, 2013

Dr. Dan :

Maybe?

Dr. Dan :

I don't know for sure

Dr. Dan :

Is it working in the template I am giving you?

Customer:

I unmerged tham and the problem is still there

Dr. Dan :

What is the problem exactly?

Customer:

Everything is working except the text transfer from L27, O27 and R27. For some reason when I select Y in the cell above (either L26, O26 or R26) the text transfer does not go immediately. It doesn't matter if the cells are merged or not either

Dr. Dan :

You can take out the error checking from cell A15

Dr. Dan :

And use =IF(L26="Y",L27,IF(O26="Y",O27,IF(R26="Y",R27,0)))

Dr. Dan :

I have instant changes in the example document

Dr. Dan :

Do you get instant changes in my 20130605.xlsx file?

Dr. Dan :

(Can you see the text change from "This is L27" "This is O27" "This is R27")?

Customer:

No. the changes are still not instant. I can see on your example but when I copy across it does not work

Customer:

I use Provider 'A', Provider 'B' and Provider 'C'

Dr. Dan :

How big is your text in L27, O27, R27?

Customer:

see above

Dr. Dan :

So the titles are just Provider A, Provider B, Provider C

Customer:

Yes

Dr. Dan :

Do you have to click on the cell to refresh it?

Customer:

No

Dr. Dan :

I would delete out A15 and re-enter the formula again

Dr. Dan :

=IF(OR(AND(L26="Y",O26="Y"),AND(L26="Y",R26="Y"),AND(R26="Y",O26="Y"),AND(L26="Y",O26="Y",R26="Y")),"ERROR", IF(L26="Y",L27,IF(O26="Y",O27,IF(R26="Y",R27,0))))

Dr. Dan :

Test the same function in some blank cells elsewhere

Dr. Dan :

It shouldn't matter what text is in L27, O27, R27

Dr. Dan :

A15 should just get whatever is in there

Customer:

I selected Y in cell L26 and it took a full 2 mins to change from my previous selection

Dr. Dan :

Just cell A15?

Customer:

I deleted A15 and tried it in another cell and it works

Customer:

Cell a5 vis a meged cell

Customer:

sorry A15

Dr. Dan :

So only A15 causes problems because it was merged

Customer:

yes

Dr. Dan :

Does it work if you unmerge the cell?

Dr. Dan :

Only the data in the upper-left cell will remain in the merged cell

Customer:

Yes it does

Dr. Dan :

Try unmerging

Customer:

Can I merge the cell with the formula in it

Dr. Dan :

Plop the function in the left most cell

Dr. Dan :

and then merge

Dr. Dan :

upper left of the merge selection

Dr. Dan :

with the formula in it

Dr. Dan :

let me know if that works

Customer:

I tried but it stopped working after I merged

Customer:

It should be ok...I can work around this for the final product.

Customer:

I have another problem with the same data but am happy to pay for it as another question

Dr. Dan :

how many cells are you merging?

Dr. Dan :

It could be that if you have (hundreds)

Customer:

10 cells are being merged

Dr. Dan :

it may be populating all of those

Dr. Dan :

hmm

Dr. Dan :

10 shouldn't be too many

Dr. Dan :

Hmm. Easiest work around would be to not have A15 as a merged cell if possible

Dr. Dan :

if it is working otherwise

Dr. Dan :

or use another field that is not merged as the Text field

Customer:

Yes I can live with that.

Dr. Dan :

(without looking at your dataset I can't troubleshoot this one well)

Dr. Dan :

(as I cannot recreate it)

Customer:

Thats ok I am happy to leave the cell unmerged

Dr. Dan :

great, hopefully we got this data set optimized

Dr. Dan :

I would be happy to work on another set, although I will need a break tonight to do it, depending on the complexity, could get it done overnight (10 PM our time here), probably day for you

Customer:

I have another problem. The table in question needs to go up one level as I now need to put another bit of data in at the bottom but I have limited the sheet to print only to a certain cell range and do not know how to expand on the print range so easier for me to move the table up

Dr. Dan :

If this question is done, appreciate if you kindly accept this solution and request myself for another question

Customer:

yes it is 13.20 here (Australia)

Dr. Dan :

When you print you should be able to select your print range

Dr. Dan :

Yes,

Customer:

ok will complete this question and ask for you again

Dr. Dan :

In print settings choose Print Selection

Dr. Dan :

(You'll need to select the area you want to print, print to going to the Print prompt)

Customer:

I have a pdsf button that printes certain cells

Customer:

thats pdf

Dr. Dan :

PDF button in the worksheet?

Customer:

yes

Dr. Dan :

or in the Print prompt

Customer:

in the sheet

Dr. Dan :

phooey, is that a VBA script?

Customer:

yes

Customer:

let me pay you and then lets continue with a new question

Dr. Dan :

Do you have the macro's code?

Customer:

yes

Dr. Dan :

ok sounds good