Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cross reference in excel

Posted on 2014-01-09
3
Medium Priority
?
380 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 35

Accepted Solution

by:
Norie earned 2000 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 13

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

963 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