Hi John, I got your request, let me start taking a look into this
Working on it right now
No worries thanks
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
I think I'll have it done by then
Attached is the .xlsx of the file I have created for you: http://goo.gl/Q6Lm7
Click download in the bottom right of the screen to save to your computer and open it up
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")
If there is more than one Y then all of the fields will show ERROR
if all of them are N all the fields will show 0
Then when you toggle the Y respectively, the fields as requested should show what you have instructed
I have the place holder data in italics and the results in bold
Play around with that file and let me know if that meets your needs!
Thanks for personally requesting me to do this task, I appreciate the repeat consultation
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
I am waiting for the pass code to get on to the site in order to download your work
Whoops, sorry I didn't change the permission
you should have access to the file now
(Control S) to save it once you have opened it
Thanks....I will see how it goes and get back to you soon
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'
Let me change that
Here's the revised one without data validation:
Just be sure to use Y
Will only work if one and only one of the three fields has Y in it
otherwise will throw error
does that work out for you?
That is better but not all of the data comes across into all of the cells
What cells don't get populated?
I think this is working to the outline that you provided
Let me know what data is missing
Sorry, but L27, O27 and R27 should be L26, O26 and R26 (my mistake)
Hang on that is not right...let me have another look
F17 and F18 should be F18 and F19 (my mistake)
Also the text in L27, O27 and R27 do work but are slow to transfer
ok let me make some edits
What version of excel do you use?
There is the latest version with the F18, F19 edits
Office pro 2010 also the cells are merged cells. would that make a difference
the A15 text changes are near instantaneous for me in Excel in 2010, 2013
I don't know for sure
Is it working in the template I am giving you?
I unmerged tham and the problem is still there
What is the problem exactly?
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
You can take out the error checking from cell A15
And use =IF(L26="Y",L27,IF(O26="Y",O27,IF(R26="Y",R27,0)))
I have instant changes in the example document
Do you get instant changes in my 20130605.xlsx file?
(Can you see the text change from "This is L27" "This is O27" "This is R27")?
No. the changes are still not instant. I can see on your example but when I copy across it does not work
I use Provider 'A', Provider 'B' and Provider 'C'
How big is your text in L27, O27, R27?
So the titles are just Provider A, Provider B, Provider C
Do you have to click on the cell to refresh it?
I would delete out A15 and re-enter the formula again
Test the same function in some blank cells elsewhere
It shouldn't matter what text is in L27, O27, R27
A15 should just get whatever is in there
I selected Y in cell L26 and it took a full 2 mins to change from my previous selection
Just cell A15?
I deleted A15 and tried it in another cell and it works
Cell a5 vis a meged cell
So only A15 causes problems because it was merged
Does it work if you unmerge the cell?
Only the data in the upper-left cell will remain in the merged cell
Yes it does
Can I merge the cell with the formula in it
Plop the function in the left most cell
and then merge
upper left of the merge selection
with the formula in it
let me know if that works
I tried but it stopped working after I merged
It should be ok...I can work around this for the final product.
I have another problem with the same data but am happy to pay for it as another question
how many cells are you merging?
It could be that if you have (hundreds)
10 cells are being merged
it may be populating all of those
10 shouldn't be too many
Hmm. Easiest work around would be to not have A15 as a merged cell if possible
if it is working otherwise
or use another field that is not merged as the Text field
Yes I can live with that.
(without looking at your dataset I can't troubleshoot this one well)
(as I cannot recreate it)
Thats ok I am happy to leave the cell unmerged
great, hopefully we got this data set optimized
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
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
If this question is done, appreciate if you kindly accept this solution and request myself for another question
yes it is 13.20 here (Australia)
When you print you should be able to select your print range
ok will complete this question and ask for you again
In print settings choose Print Selection
(You'll need to select the area you want to print, print to going to the Print prompt)
I have a pdsf button that printes certain cells
PDF button in the worksheet?
or in the Print prompt
in the sheet
phooey, is that a VBA script?
let me pay you and then lets continue with a new question
Do you have the macro's code?
ok sounds good