Solved

Cross reference in excel

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now