Solved

Cross reference in excel

Posted on 2014-01-09
3
356 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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