Jake the coffee guy
New Member
Hi All.
My name is Jake and this is my first question but i have used Chandoo.org for almost all of my Excel training for a number of years now, so thankyou for all your help thus far. This problem has been difficult for me to research though.
I use excel for many tasks but specifically for data analysis on coffee extraction and Vlookup and simple macro's are my best friends. The philosophy behind all my workbooks is that all my primary data is transferred to a primary "Archive" sheet and my calculations use vlookup referencing that primary archive. The first column of the Archive sheet contains the unique identifier and as such i should never load a row that starts with the same name.
My current problem surrounds the naming transferred to each row of the archive.
In order to save time i will use a dropdown list from name manager of the unique name in column 1 of the archive. A macro will subsequently populate cells of data using vlookup. i may change 1 small piece of data and then want to save all that data back to a new row in the archive and assign a new unique identifier.
I am trying to use the If function in conditional formatting to change the background on the entry box ensuring i don't overwrite a data row if my unique identifier is already used in the archive.
In my sample workbook you can see a simple data archive in the "Machines" Tab. In the "Once off" tab at B6 a drop down lists the unique identifiers from column 1 of that archive. Starting at E6 and extending to E17 are a string of yes/no in answer to an if question about B6.
Very strangely as B6 is changed via the dropdown list the if function in column e only work when corresponding to the line number in the archive not the actual search question. All the IF formula's are the same and should report the same answer but it is only works if the line number matches.
Any is assistance is very much appreciated.
Regards Jake.
My name is Jake and this is my first question but i have used Chandoo.org for almost all of my Excel training for a number of years now, so thankyou for all your help thus far. This problem has been difficult for me to research though.
I use excel for many tasks but specifically for data analysis on coffee extraction and Vlookup and simple macro's are my best friends. The philosophy behind all my workbooks is that all my primary data is transferred to a primary "Archive" sheet and my calculations use vlookup referencing that primary archive. The first column of the Archive sheet contains the unique identifier and as such i should never load a row that starts with the same name.
My current problem surrounds the naming transferred to each row of the archive.
In order to save time i will use a dropdown list from name manager of the unique name in column 1 of the archive. A macro will subsequently populate cells of data using vlookup. i may change 1 small piece of data and then want to save all that data back to a new row in the archive and assign a new unique identifier.
I am trying to use the If function in conditional formatting to change the background on the entry box ensuring i don't overwrite a data row if my unique identifier is already used in the archive.
In my sample workbook you can see a simple data archive in the "Machines" Tab. In the "Once off" tab at B6 a drop down lists the unique identifiers from column 1 of that archive. Starting at E6 and extending to E17 are a string of yes/no in answer to an if question about B6.
Very strangely as B6 is changed via the dropdown list the if function in column e only work when corresponding to the line number in the archive not the actual search question. All the IF formula's are the same and should report the same answer but it is only works if the line number matches.
Any is assistance is very much appreciated.
Regards Jake.
