Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cross reference in excel

Posted on 2014-01-09
3
Medium Priority
?
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

730 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