Excel formula issue

Hi All,

I need to the following formula placed on c11 and all subsequent rows in c.

=IF(OR(c10="",'tab1'!A35="Responses"),"",'tab1'!A35&"")

I need this formula to persist after the file closes.

Please see attached file.

Thanks for your help!
Sheet-A.XLSM
J_DrakeAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
Because you are working with merged cells, it makes it more complicated.  It is always advisable to avoid using merged cells for areas having formulas or being referenced by formulas:

However, try selecting C11 and pasting this formula in the formula bar at the top (not in the cell):

=IF(OR(C9="",MOD(ROWS($A$11:$A11),2)=0,INDEX('Tab1'!$A$35:$A$1000,ROUND(ROWS($A$11:$A11)/2,0))="Responses"),"",INDEX('Tab1'!$A$35:$A$1000,ROUND(ROWS($A$11:$A11)/2,0))&"")

Open in new window


then copy down

this should work if you reference a separate workbook that contains Tab1....
0
 
J_DrakeAuthor Commented:
Tab1 is now inside this workbook, I modified the mockup to reflect the change.

I tried pasting the formula in the bar and it shows as text.

Thanks again!
0
 
NBVCCommented:
Yes, I noticed you have the cells formatted as text.

Select column C and format the cells as General first, then re-paste or re-process the formula in C11 before copying down.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
J_DrakeAuthor Commented:
That pulled over the task but not the performed by.. any ideas?

Thanks,
0
 
NBVCCommented:
Try:

=INDEX('tab1'!$B$35:$B$1000,ROUND(ROWS($A$11:$A11)/2,0))&""
0
 
J_DrakeAuthor Commented:
Is there anyway to hide or lock  the formula to keep it from being tampered with?

Thanks,
0
 
NBVCCommented:
0
 
J_DrakeAuthor Commented:
I checked out the article, thanks. I tried locking the sheet but that won't work because it breaks the vb functions powering some of the form objects.

Is there anyway, perhaps using code to lock the c & E columns?

Thanks again!
0
 
Naresh PatelTraderCommented:
I don't know it will work or not but try this. Step 1 Step 3Step 4 Step 6
Thanks
0
 
NBVCCommented:
You can add unprotect/protect code to your vba...

e.g


Sheets("Sheet1").UnProtect Password:="MyPassword"
...
[i]Your code[/i]
...
Sheets("Sheet1").Protect Password:="MyPassword"

Open in new window


so save the sheet in protected mode using your password as the article I referenced describes (you can remove password requirement if not wanted).

The running your code will temporarily unprotect the sheet to do its thing, then finally re-protect it so that people can't mess with it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.