Solved

Cross reference in excel

Posted on 2014-01-09
3
364 Views
Last Modified: 2014-01-09
Hello!

column A has numbers that are arranged in certain way and can not be changed.                                                      
Cells F3 - XQ3 have data that needs to be linked with the data from column A.                                                      
I manually populated cells B8 - M8 to show what needs to be done.                                                      
It is very time consuming to do it cell by cell. I am wondering if there is a formula that can help me expedite these calculations.                                                      
thanks!
Cross-reference-Formula.xlsx
0
Comment
Question by:Ladkisson
3 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39769377
Try this in A8 an copy across/down.

=INDEX(OFFSET($A$2,1,(CODE(B$6)-CODE("A"))*53+6,1,52),,MATCH($A8,OFFSET($A$2,,(CODE(B$6)-CODE("A"))*53+6,1,52),0))
0
 

Author Comment

by:Ladkisson
ID: 39769399
AWESOME!!! Thank you!!
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 39769435
Here is it using a VBA function.


Public Function CrossRef01(rngVal As Range) As Long

Dim arrXrefRange(12) As String

arrXrefRange(0) = "G2:BF2"  'A
arrXrefRange(1) = "BH2:DG2" 'B
arrXrefRange(2) = "DI2:FH2" 'C
arrXrefRange(3) = "FJ2:HI2" 'D
arrXrefRange(4) = "HK2:JJ2" 'E
arrXrefRange(5) = "JL2:LK2" 'F
arrXrefRange(6) = "LM2:NL2" 'G
arrXrefRange(7) = "NN2:PM2" 'H
arrXrefRange(8) = "PO2:RN2" 'I
arrXrefRange(9) = "RP2:TO2" 'J
arrXrefRange(10) = "TQ2:VP2" 'K
arrXrefRange(11) = "VR2:XQ2" 'L

'Search the range
For Each cell In Range(arrXrefRange(rngVal.Column - 2))
  If Range("A" & rngVal.Row).Value = cell.Value Then
    CrossRef01 = Sheet1.Cells(cell.Row + 1, cell.Column).Value
    Exit For
  End If
Next


End Function

Open in new window

Cross-reference-Formula.xlsm
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question