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

convert excel formulas from 2010 to 2003 with explanation

Sorry OOoopss !! Some more questions
1) How to do disable the properties for a excel file - ( right click the file & properties – general custom summery)
2) Please see the attached file – in Issue Receipt tab : I have a excel 2003 file with data - list ( blue line & * star appear) - i want to protect my formula - how to do. ( when i an doing it is protecting all the lists - NO blue line & * star appears & not allowing me to save it.
When protecting the sheet - this Blue * not appearing & not allowing to do data entry. I want to protect the cells with formula , but at the same time want to allow user to do data entry / select data form the list & the cells will formula will do calculations automatically
Please help
 

Attachments

Unfortunately, I don't see Blue * as I don't have 2003.

1) Not sure what you mean.

2) To allow data entry on protected sheet.
Select range where you want to allow data entry. Right-Click ->Format Cells.

Then go to Protection tab and uncheck Locked.
 
this I know...but when i do this...blue * not coming & not allowing to do entry. Since you do not have 2003 , it is not possible for u to check. any way..I will try else try to find different way around.
 
Typically, I'd recommend User Form and VBA for adding new data to protected sheet... are you comfortable working with VBA and maintaining?
 
no sir....absolutely no idea about User Form and VBA and maintaining it.
But want to learn. Can u recommend any easy link / forum on this?
Also you may recommended book for advance excel or any site with examples? Also want to know in details about Index formula and array ... Small or ROW(1:1)-1
 
Here's the explanation.
So the INDEX formula is used to find value in array that matches condition.
=INDEX(Array,row_num,[column_num])

ROW, simply returns row# of the reference.
=ROW(Reference/Calc)

SMALL looks for k-th smallest value in range or calculation.
=SMALL(array,k)

In the formula used in the sheet, k is expressed as $M$6-ROW($H$4), $M$6 is where Spin control starting value of 5 is stored, and ROW($H$4) returns 4 always. Therefore, when spin control modifies value stored in $M$6 and updates it by either adding or subtracting from it, k will adjust accordingly. This is to make calculation dynamic.

So this portion of the formula:
SMALL(IF(INDEX(Stockposition!$B$14:$P$108,,15)<=0,ROW(INDEX(Stockposition!$B$14:$P$108,,15))-ROW($M$13),""),$M$6-ROW($H$4))

Checks if any value in column#15 of Array (Stockposition!$B$14:$P$108) is less than or equal to zero. If there is matching value in array, it looks for k-th smallest value in the array and returns it's row# in the array. -ROW($M$13) is used as the array starts just after ROW($M$13) which is header of the table.

For an example, if Spin control value is at starting value of 5. This calculation returns 2 as answer, the smallest ROW # of cell in Helper Column which has value less than or equal to zero.

Now plug this into outer INDEX, you can simplify as.
INDEX(Array,2,1)
Therefore it returns value stored in 3rd row of 1st column in the array, which is Amoxicillin.

Hope this explains it.
 
A NEW problem :
Hope I did not break any forum rules in writing a new problem in continuation of my old questions:
Question: for every 10 counts I want to change a code -Refer the colour changes. In Excel 2003
Example:
1 AA
2 AA
3 AA
4 AA
5 AA
6 AA
7 AA
8 AA
9 AA
0 AB
1 AB
2 AB
3 AB
4 AB
5 AB
6 AB
7 AB
8 AB
9 AB
0 AC
1 AC
2 AC
3 AC....continues
Please Help!!!
 
Last edited:
You must log in or register to reply here.
Back
Top