How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask R. Michael Your Own Question

R. Michael
R. Michael, MIS Analyst
Category: Microsoft Office
Satisfied Customers: 59
Experience:  Masters of Info. Systems; 10+ years using Microsoft Office.
Type Your Microsoft Office Question Here...
R. Michael is online now
A new question is answered every 9 seconds

Good morning. I have a table that automatically adds numbers

Customer Question

Good morning. I have a table that automatically adds numbers to each column as i populate other worksheets. I use this as a training record system for my flying school.

I need to work out a formula that will allow 1 cell to search along a row and identify the earliest date that a certain number was added to the table.

Is there any way of doing this?

Submitted: 3 years ago.
Category: Microsoft Office
Expert:  R. Michael replied 3 years ago.

R. Michael :

Hello I am R. Michael and I would like to answer this question. However I do have a question about your table. Does it contain record entry dates in cells or are you looking for excel to find out which cell in a column or cell was updated without any identifiable time/date stamps in cells of the table?

R. Michael :

For example if the dates are stored in a row 1 This Function would find the earliest date =MIN($1:$1)

JACUSTOMER-qmt7g4wv- : G'day mate. I probably should have clarified the question a bit better. The table has each of the lessons from my flying syllabus across the top, and each of the required competences down the left hand side. As a student progresses through Their training, I complete a separate worksheet and their scores are automatically added to the table. I have also added an extra row at the bottom of the table where the date for each lesson is added as the worksheets are completed.
JACUSTOMER-qmt7g4wv- : What I need to achieve is for another part of the workbook to be able to establish the date when a student scores a "1" for example
JACUSTOMER-qmt7g4wv- : The table is also not set in stone. I have about 20 worksheets and the table brings all the scores into one place. Hope this helps
R. Michael :

So are all the worksheets in the workbook related to one student? Or is the table to combne multiple student scores>

JACUSTOMER-qmt7g4wv- : They all relate to one student. Each student has a separate spreadsheet
JACUSTOMER-qmt7g4wv- : Hello mate. Did my answer come through?
R. Michael :

Got it I am going to post a sheet in 1 minute

R. Michael :

Here is a link to a spreadsheet. Let me know if this is what you are looking for:

R. Michael :

R. Michael :

I built a example table based on your description. The basically duplicated that in another sheet "Date Lookup" This sheet checks the date of the number in the "table" sheet, Then references the date row if it matches a cell with the number to lookup(yellow cell).

JACUSTOMER-qmt7g4wv- : Tha is made
R. Michael :

The other yellow cell returns the lowest(earliest) date in the "Date Lookup" table. You can Change the value in 'Date Lookup'!A2 to find the earliest date for another number. Let me know if that works.

JACUSTOMER-qmt7g4wv- : Thanks mate. That looks like something I can work with. I am on my iPhone at the moment and will message you back tomorrow to let you know how I go. Tha is for your help.
R. Michael :

No Prob, let me know tomorrow, and I can update modify if needed,

JACUSTOMER-qmt7g4wv- : G'day mate. I've had a goo look at the spreadsheet you created and it's kind of along the right lines. What you've created on the "table" tab I similar to what I have. What I need the spreadsheet to do is in column L list the date that the student first for
JACUSTOMER-qmt7g4wv- : Got a 1 for that competence as they may achieve it on more than one lesson.
JACUSTOMER-qmt7g4wv- : Thanks
R. Michael :

I understand I can build that in and re-post in a little while.

JACUSTOMER-qmt7g4wv- : Ok thanks.
R. Michael :

OK Here it is:

R. Michael :

I removed that other table to simplify and condense

R. Michael :

and added your request to the L column to find the earliest date and used conditional formatting to white out the text of cells where no 1's appear or the formula in column L results in 0 value. One note if you click on the formula in the formula bar make sure to press Ctrl+Shift+Enter to array the formula or add those { } brakets it is the only way to do a MIN IF. typing them in won't work