Help with getting a specific row and column cell value to another spreadsheet

[hucklemyberry]

I'm making an employee daily progress report that shows what they accomplished for the day. I get the data I need from a software we use and then create a report and copy and paste that report into excel.(There is no export to excel option) I copy and paste the data in spreadsheet2 and would like the data to automatically go into spreadsheet1(which is already formatted to a report style spreadsheet). Here is the tricky part the info I past into Spreadsheet2 won't have the same format as spreadsheet1, so what i need is a formula that can recognize spreadsheet2 Row and the Column names and be able to transfer those cell values into the correct Row and Column names in spreadsheet1.

For Example:

Spreadsheet1 will have the employees name in column A2-A13 and the Columns would be across cells B1-F1 each having its own specific title

Spreadsheet2 employees name will also be in column A2-A13 but wont be in the same order as spreadsheet1 and the order will change everyday, this is also the same case for columns.

I'm not even sure if this is possible to do in excel, I thought i could create a "IF()" formula that would be able to send spreadsheet2 cell values to the correct spreadsheet1 cell based on which column and row it was located under but I have been unsuccessful so far. Any help would be awesome.

Thanks,

[micope21]

Hi

Yes it can be done.

Can you upload sample file. It would help us having a file to put formula in.

[hucklemyberry]

Sample Report.xlsxAbsolutely thanks for getting back to me so quickly I attached a sample file below

[FDibbins]

in B9 add this formula...

=IFERROR(VLOOKUP($A9,Data!$A$5:$M$10,MATCH(B$8,Data!$4:$4,0),FALSE),"")

It is based on the format of the data you supplied. adjust the references as required...the formula is constructed so that you can just copy it across and down with no changes needed

[hucklemyberry]

Ok this works on the sample sheet but when i transfer it to my actual sheet i cant get it working. I'm changing the references numbers ($A9,B$8 and $A$5:$M$10) to the correct cells, however I'm not sure what "Data!$4:$4,0)" is grabbing from, could you break down the formula for me and tell me which function is doing what, i"m a little confused

[FDibbins]

"Data!$4:$4,0)" is checking the headings in your data table, and returning the column number for the search. so you need to adjust the $4:$4 reference to the row (4) that your actual headings are in. also, if your data does not start in column A, you will need to subtract the difference from the result of the match()

=IFERROR(VLOOKUP($A9,Data!$A$5:$M$10,MATCH(B$8,Data!$4:$4,0),FALSE),"")

iferror returns "nothing" if the search is unsuccesful
vlookup searches for your criteria (A9), in data range Data!$A$5:$M$10
match looks at your "results" heading, then searches your data table for that heading...the ,0 is for an exact match
the "false" part is included to enable a vlookup in unsorted data

Let me know if you need any more help, or explanaitions

[hucklemyberry]

Ok that really helps thanks FDibbins, I have one last question, after i put the formula in the cell, when i try to get out of that cell by hitting "Enter" a "Update Values:Data" popup appears and when i select cancel the text "[Data]" appears in my formula which messes up the whole formula. Do you know how i can stop this from happening

[micope21]

Hi

Try this

B9 =IF(ISERROR(INDEX(Data!$A$5:$M$10,MATCH($A9,Data!$A$5:$A$10,0),MATCH(B$8,Data!$A$4:$M$4,0))),0,INDEX(Data!$A$5:$M$10,MATCH($A9,Data!$A$5:$A$10,0),MATCH(B$8,Data!$A$4:$M$4,0))) then copy down and cross

[hucklemyberry]

No worries just figured it out, thanks for all of yalls help

[FDibbins]

glad to help, please mark this thread as solved