• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

return just "checked" cells

I

igorsusa

Member
Hello Chandoo,

I have one problem and I think it will be an easy one for you guys.

Basically all I need to do is to return the values that are in column N "DOSE" (sheet main) into the sheet "cass" into the right cell (so regarding the time of the day and type of the medicine), but only if user clicks on check mark in the next column.

I have tried something but I doesnt work, also I am not sure if I am using the right procedure...

Fell free to use your own strategy what will be the best solution even for further cases if we will be adding additional pills, so it would be less work then.

Thanks so much!
 

Attachments

  • chandu_pills.xlsx
    125.1 KB · Views: 12
EDIT, PLEASE FOLLOW THIS DIRECTIONS:
_______________________________________________________________
Main sheet contains everything the patient is having, but cass contains some of the content of the main and sometimes most but almost always not all. but the ones in the cass will definitely be on the main as well.

Example: I want to be able to add omeprazole 20mg capsules into a slot on the main sheet (dose) and by doing this in slot 1 of main 1, this information is automatically replicated into weeks 2, 3 and 4 of main and into weeks 1-4 of cass sheet. it is only replicated into cass sheet if i chose option of "in cass sheet" and if not selected then it is not inputted into the cassette sheets¸. also there is no need to individually link each dose to the cass sheet , as it is in the file. I only need one tick for each medicine

i.e if omeprazole is linked to cass, then all doses (i.e. morning, lunchtime, tea time and bed) should automatically be linked to the cass sheet. but I don't want to the box where the tick is present to be visible on the printed version.

Basically I only need to create one tick button for each medicine, from which I can select if the data goes to the cass sheet or no. So one check mark button (it doesnt need to be check mark buton, can be anything, since it must not be visible in printed version) for each medicine and if selected then all the data for all period of the day should be imported into cass sheet for that medicine. The only data that is returning to cass sheet is from column "N" (doses).

Then I would need one good logic of how to do this to have as little problems possible, if we would be adding some other pills to the main sheet...

Hope it is clear enough, thank you for your help!




_______________________________________________________________
 
Last edited:
Hi Igor ,

I am not clear on your requirement. Can you clarify the following ?

1. Do you want a formula to populate any cells on the main tab ? If yes , which are the concerned cells ?

2. Which are the cells on the cass tab which need to have a formula in them ?

3. The dosages on the main tab are as follows :

Take ONE daily

Take ONE twice daily

Take TWO twice daily

Take ONE twice daily (NOT IN CASSETTE)

Are there any more or are these 4 the only possible dosages ?

Are the formulae to be developed in any way concerned with these dosages ? If yes , how ?

Narayan
 
Hey @NARAYANK991 !

1. Formula just need to copy the values that are in column N, "doses". From main to cass sheet, according the specific pill. If you have any idea how to make it simple you can change the way I did it now in the file...

2. Just the yellow colored cells, in week 1.

3. For now no, this is not important.

So all I need is to create one "button" for each medicine that will say "copy this doses (for all period of the day) to cass sheet" or "dont copy them". It is up to user to decide if this values will be linked to cass sheet (so user decides, he cheeks or unchecks the slot). But this "button" must not be visible when sheets are printed on paper... Any idea how to accomplish that?

Only values from column N needs to be linked to the cass sheet.

Hope it is clearer now and thank you for your help!
 
Hi Igor ,

Just to clear one issue :

When you right-click on the checkbox , select Format Control ; click on the Properties tab. You will see a checkbox labelled Print Object.

If this is checked , the object is printed , else it will not be printed.

All you have to do is select all the checkboxes , and uncheck this box.

Narayan
 
OK, thanks for this, cool trick.

But this is not all I need. There must not be one checkbox for one row like it is now, but I have to do it somehow just one for each medicine. So not for each row but just for each medicine. If the box is checked then I need the formula to return the values into cass sheet for all the period of the day for that medicine. If its not checked the values dont go to another sheet.

Thanks!
 
Hi Igor ,

See your file now.

There are 2 issues :

1. I have included a section within the formula which checks to see whether the medicines on the two tabs match ; you need to decide whether this is necessary. The 5th and 6th medicines do not match at present.

2. On the main tab there are 6 medicines whereas on the cass tab there are 7 , which is why the last row is showing error values.

The formula is using an OR function to see whether even one of the checkboxes within each section is checked ; thus it is OK for even one checkbox in the set of 4 to be checked , and the medicine will be applied to all the 4 times.

Narayan
 

Attachments

  • Igorsusa_chandu_pills.xlsx
    127.5 KB · Views: 7
Hey,

this is great, we just need expanding it a little!


Actually I have one better idea: Is it possible to write a formula that would return the name of the medicine, values of the doses for each period of the day into the cass sheet, if the check mark is selected? Formula must also look into the page 2/2 (see attached file).

If it is not possible then I would like to have just extended formula to also search in the page 2/2.

Thanks!
 

Attachments

  • Igorsusa_chandu_pills_2.xlsx
    119 KB · Views: 3
Last edited:
Hi Igor ,

Sorry , but it is taking longer than I anticipated.

I have put in formulae in columns A and B of the cass tab ; see if this is what you want.

Narayan
 

Attachments

  • Igor_Susa_pills_2_revised.xlsx
    122.6 KB · Views: 6
hey @NARAYANK991

thanks, but its not working properly... If I deselect, the first medicine I got just 0 in cass sheet, even if all other medicines are selected. Also I would like the function to look for the medicines that are in column AD (so we have two pages).

Sorry to bother you and thanks so much for your help!
 
Hi Igor ,

I think you have not read my last post fully.

I have put in the formulae in columns A and B only. Only the week's quantity and the name of the medicine will appear in the cass tab through formulae ; if you confirm that this is OK , then I will put in the formulae in the other required columns for the doses.

Narayan
 
Whoops, sorry, yep, I think this should be it! I can change the discription in cass sheet with vlookup later, that is no probem!

So formula is OK, I just need it to work properly ant to look also in column AD.

Thanks Narayank.
 
Hi Igor ,

If you see the medicines which are displayed in column B , you can see that the formula is looking in column AD once it has finished looking in column B.

Narayan
 
That is true! ;)
But formula is not working ok... Try to deselect first checkmark. Then there are just zeros in cass eventhough other medicines are selected...

Also If I just select first (Omeprazole 10mg Capsules) and third (Pregabalin 0.5mg Tablets) medicine, I have just 0 for Pregabalin 0.5mg Tablets... something is not right...

EDIT: OK, I have seen that you will change the formula for the dozes, thanks man! I am waiting patiently! ;)
 
Last edited:
Hi Igor ,

Sorry for the delay. Can you see the file and comment ?

Narayan
 

Attachments

  • Igor_Susa_pills_3_revised.xlsx
    122.3 KB · Views: 4
Hey, @NARAYANK991,

its working perfectly. Could you please check out the comment sheet in the attached file?

Thanks.
 

Attachments

  • Igor_Susa_pills_3_comments.xlsx
    124.2 KB · Views: 8
Hi Igor ,

Do you have any sample data for the new template sheet ?

If you have , can you upload it , so that we can see whether the data can be directly transferred to the other sheets ?

Secondly , what is the template of the marsheet ? Is marsheet the same as main ?

If you wish to have the disable data entry on marsheet feature , we might have to go in for VBA ; is this acceptable ?

Narayan
 
Hey,

marsheet=main sheet, yes.

Any VBA is acceptable, of course.

I do not have any template for this new sheet, since this is the new idea that came up. It would just be the same format that is in comment sheet, soemthing like that. I think the best way to do it is to have one button which imports the data into two other sheets. When you click on the button, the data transfered to other two sheets and all fields became empty, so user can continue with filling the other medicines.

Thanks!
 
Hi Igor ,

Can you go through the uploaded file ?

What I have done is enter the cell addresses to which the data entered on the comment tab is supposed to be transferred ; please check whether what I have put in is correct or not. Also , please enter the unfilled cells with the transfer cell addresses.

Secondly , since the shape and colour of the medicines is related to the medicines themselves , is it possible to have a table of medicines with their associated data , so that on the comment tab we can have data validation dropdowns for entering the names of the medicines ?

Narayan
 

Attachments

  • Igor_Susa_pills_3_comments.xlsx
    125.5 KB · Views: 4
Ok, I have managed to open it somehow. Yes it is correct, I also added the text into empty cells. Please see attached file. In strength cell I forgot to put more options, we can add that later.

Thanks!
 

Attachments

  • Igor_Susa_pill_novo_narayan.xlsx
    127.6 KB · Views: 4
You must log in or register to reply here.
Back
Top