I am trying to create a worksheet to keep track of sample count. I have columns for Site Name, Quarter, and several other informative stats. I want to create a column that will update each time a new sample is added(i.e. 1, 2, 3... ) I have 12 sites, each of which I want the sample counter to be exclusively linked to. Furthermore I want the counter to be futher categorized by the financial quarter (1-4). The way I hope this to work is that say that one day I get samples at 2 sites. I input a new row. In the Site Name column I input "Site A", and that the sample was collected in Quarter "2". I create another new row for the other site that I sampled "Site B" also collected in quarter "2". In this example Site A has already had 5 samples this quarter(2), and site B has had 2 samples in this quarter(2). I want my counter to automatically populate a "6" for sample count for Site A, and "3" for Site B. I then want to use this counter to populate a bar graph indicating the progression toward the 15 samples per Quarter goal for each site. Help!
This can be done using a formula column. Could you post some data so we could see it more clearly?
here is a sample of what i'm working on, I changed some of the quarter designations for the purpose of this example
Create a formula column called Counter. Add this formula to it:
current_row = row();
this_site = :Site[current_row];
this_quarter = column("Quarter (1-4)")[current_row];
this_counter = 1;
for (i = 1, i < current_row, i++,
one_site = :Site[i];
one_quarter = column("Quarter (1-4)")[i];
if (this_site == one_site & this_quarter == one_quarter,
:Counter[current_row] = this_counter;