Adding a date to the bottom of a list and finding the correct cell

It all sounds possible, but with sample workbook it would be easy to give precise suggestions.
Without, I expect that MATCH and OFFSET functions can do miracles for you ;-)

Hi,
If I understand well, you want your data from sheet2 to be added to existing data in sheet1.
That would not be possible with formulas. Formula gar=thers information from "surrounding world" and presents it (somehow processed) in the cell where is written in.
but it can be easily done with a macro.
I had some spare time, so prepared a but more fancy one (although still rather on easy side):

Please Login or Register  to view this content.

see how it works in the attachment.

some comments
- I think that after succesfull writing into main sheet data shall be deleted from suplementary one. If not, change DeleteAfterUse to False
- Lines with errors (either wrong headings or not-excel-dates will remain not deleted for correction.
- Much larger sheets (but with the same general layout) shall work well. Key points are:
- headers in main sheet starting at A1
- names and dates in supplementary sheet in columns A and B starting from row 1
- Dates are real excel dates: "January 1st, 2014" won't do
- Headers in main sheet are "neat" - now Skill G is written as "Skill G " (with space at the end) - and this will cause errors (in supplementary sheet it is OK)

OK, so we have a list of headers (skil names for columns) in Sheet1 (no dates yet) and list of Skills with dates in Sheet2 -> we want to transfer it to Sheet1 with formulas.
Let's start with approach suggested by you, so in A2 in Sheet1:

=IF(COUNTIF(Sheet2!$A:$A,A$1)=0,"",INDEX(Sheet2!$B:$B,MATCH(A$1,Sheet2!$A:$A,0)))

first we check if there is any data for skill in the header (with COUNTIF) then with MATCH (you asked about it) we find the first occurence of our skill in column A and return respective date from column B using INDEX. With reasonable use of mixed and absolute addressing our formula is ready to be copied right.

If we do it we notice some strange dates like Jan 0 1900 in cells where we expected empty string ("").
To deal with it we can format the cells (all where we want our formulas, so A2:I10 or something like as non-standard format - for instance mm/dd/yyyy,,,@ or dd-mm-yyyy,,,@ (as I did). Note that excel can later show only dd-mm-yyyy,@ but at the stage of appying you shall write the long form:
positivevaluesformat,negativevaluesformat,zerovaluesformat,textformat
or at least a part of it

OK, that was good for first date, but MATCH will always find first occurence, so it is not good way for next.
We can try array formula (committed with Ctrl+Shift+Enter) for A3:
=IF(COUNTIF(Sheet2!$A:$A,A$1)<ROW(A2),"",INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A:$A=A$1,ROW($A:$A),""),ROW(A2))))

COUNTIF part is similar - as we plan to copy it down we do not check if countif<2 (= 0 or 1) but <ROW(A2) as we will copy it down will be rownumber of A3, A4 and so on.
INDEX is again used to retieve date from column B, but it's location is determined with second (row(a2) smallest row number, where our skill was in column A

This way is also good for row above, so we could copy it here too (in attachment I didn't).

if we copy it to few cells down and right we will notice that excel slows down. Especially Excell2007+ this is because formula checks whole columns (and there is a million+ rows!)

so let's edit A4 to look into only reasonable (I used 100 but adopt it to your needs) rows:

=IF(COUNTIF(Sheet2!$A$1:$A$100,A$1)<ROW(A3),"",INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A$100=A$1,ROW($A$1:$A$100),""),ROW(A3))))

and remember - array formula ! (Ctrl+Shift+Enter)

This one can be copied to a number of cells not slowing excel down noticeably.

In Attachment you have first formula left in A2 and second in A3 (just to examine them) and all others (A4:A10 and B2:I10) is the third formula.

Wow, that was long ...