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

Conditional formatting

G

Guitman

New Member
Hi, is it possible using conditional formatting that if an asterisk is entered in cell C11 on sheet 1 that the information in adjacent cell D11 is copied and entered in cell B115 in sheet 2.
Then if I put an asterisk say in cell C16 on sheet 1 that the adjacent information in D16 would be entered in the next blank cell on sheet 2 (B116).
In other words any cell I choose to place an asterisk in sheet 1 column C then the adjacent information would be entered into the next blank cell in column B sheet 2.
I have attached a file of what I am trying to do. If this is not possible with conditional formatting what other option would I have to achieve my goal.
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Copy.xlsm
    111.9 KB · Views: 5
Put this is sheet2!B115 with CSE and drag down:

=IFERROR(INDEX('Sheet 1'!$D$11:$D$33,SMALL(IF('Sheet 1'!$C$11:$C$33="*",ROW($C$11:$C$33)-10),ROW(A1))),"…")
 
Last edited:
Faseeh said:
Put this is sheet2!B115 with CSE and drag down:

=IFERROR(INDEX('Sheet 1'!$D$11:$D$33,SMALL(IF('Sheet 1'!$C$11:$C$33="*",ROW($C$11:$C$33)-10),ROW(A1))),"…")
Click to expand...
Hi Faseeh, thanks for your prompt reply but I am having trouble getting this to work. Is there any chance that you could do this on the file I sent you and then email it back to me so that I can compare and try and work out where I am going wrong. Thanks
 
Hi Guitman,
See your file with solution of Sir Faseeh.
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Copy.xlsm
    111.3 KB · Views: 2
Khalid NGO said:
Hi Guitman,
See your file with solution of Sir Faseeh.
Click to expand...
Hi Sir Faseeh, I have been trying to implement this formula in my actual workbook but I am having trouble with the result showing on my actual sheet.
When you analyse the formula it is showing the correct result but on the actual sheet it is not showing the result. I have attached the file for the actual workbook I am using so could you have a look for me please and let me know where I am going wrong. Thanks very much
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Trial.xlsm
    176.2 KB · Views: 1
Dear Guitman,

Execute formula pressing Ctrl Shift Enter at the same time, your sheet is working.
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Trial.xlsm
    176.5 KB · Views: 3
Faseeh said:
Dear Guitman,

Execute formula pressing Ctrl Shift Enter at the same time, your sheet is working.
Click to expand...
I think this is the bit I am not understanding. If you have a look at cell B65 I have entered the formula by copying and pasting then changed a few bits. Where do I do the Ctrl Shift Enter? This is something so basic which I seem to be missing and driving me mad. Please excuse my ignorance
 
Ok no problem! :)

Select Cell B65
Press F2 so that you can see formula
Now press Ctrl Key along with Shift Key and then press enter
This will make a Currly braket around your formula in the formula bar
These are called array formulas :)
 
Faseeh said:
Ok no problem! :)

Select Cell B65
Press F2 so that you can see formula
Now press Ctrl Key along with Shift Key and then press enter
This will make a Currly braket around your formula in the formula bar
These are called array formulas :)
Click to expand...
So easy (when you know how). Thank you very much for your help. Much appreciated
 
Faseeh said:
You are welcome.
Click to expand...
Hi Faseeh, need your help again. Again it must be something very simple that I am missing. I am taking yesterday's workbook a little further and I have used a conditional formatting in Cell C11 to make D11 change colour once I insert an asterisk in Cell C11. It obviously does not work on this workbook but I have used the same formatting in other workbooks and it works ok. What am I doing wrong? I attach the modified file. Thanks once again
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Copy.xlsm
    177 KB · Views: 5
Khalid NGO said:
@Faseeh Sir
I think Guitman is referring about removing of *.

CF is not returning back after removing *
Click to expand...
Hi, for some reason mine will not turn green when I put the asterisk in. By trying different things I press F2 then it will turn green but when I remove the asterisk it still remains green until I press F2 again. The F2 seems to be acting like a re-fresh key. Certainly in my other workbooks I don't get this problem so there must be something wrong within this workbook. Like yesterday could you email me the file back please to see if anything will change.
Thanks
 
@Guitman Hi,

Not only F2, even if you scroll your screen than also it will refresh. Go through the below link:

http://answers.microsoft.com/en-us/...freshing/0e7d82bf-4db8-4e8f-b509-9606afdc5e6d

Adding these line will resolve the issue.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

Basically this update the Screen Updating property of the application to true so CF will update.

Try the code on Sheet Module.

Regards,
 
So here is the final solution, which don't require any VBA code.

Follow these simple steps.
1. Select the Material & Labour Sheet. Any cell.
2. From the DEVELOPER tab select Properties.
This will open the sheet propert.
3. There you can see the option Enable Format Condition Calculation. Turn it TRUE.Capture.JPG

See the image.
 
Somendra Misra said:
So here is the final solution, which don't require any VBA code.

Follow these simple steps.
1. Select the Material & Labour Sheet. Any cell.
2. From the DEVELOPER tab select Properties.
This will open the sheet propert.
3. There you can see the option Enable Format Condition Calculation. Turn it TRUE.View attachment 11314

See the image.
Click to expand...
Fantastic. All works well. Thanks very much
 
You must log in or register to reply here.
Back
Top