Named Range Lookup Query

[lapta301]

Dear All

Can I please trouble you for some assistance with a lookup problem.

I have 15 rows of monthly data for each of 12 locations that follow on from each other in the format shown below. I want to replicate these 17 rows on another sheet and using a lookup for the office name pulling in the appropriate data. I had thought of using range names for each office but then got lost.

I hope that this makes sense.


As ever any help would be most appreciated.

A3 Office
A4 Months Apr May Jun etc
A5 Data 1
A6 Data 2
A19 Data 15
A20 Office
A21 Months Apr May Jun etc
A22 Data 1
A23 Data 2
A36 Data 15

 

[Haldoug]

You can rearrange your information into a table with each office being a row and column A being the office, B being the Month, and C to Q being the data. The copy and paste-special/transpose function will do that.
The macro below Does that one office at a time, and leaves your curser on the next office. Continue to enter your macro's keyboard shortcut until you have a range instead of a column of date.

The macro looks like this

ActiveCell.Range("A1:A17").Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(1, 0).Rows("1:17").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub

Make sure "Use relative references" is marked if you record the macro yourself.
To record the macro, put the curser on A3, copy A3:a19, move the curser to A2, pastespecial-transpose, delete rows a4:a19, insert row a4, and stop recording. Put the curser on A3 and run the macro below. Make sure "Use relative references" is marked if you record the macro yourself.

 

[lapta301]

Haldoug

Apologies for the delay in getting back to you.

An interesting thought but I really need to keep the layout as it comes from our Head office.

I was hopeful that with each named group of rows for each location some clever lookup query could do the job.

My thanks for taking the time to try and sort out this messy problem.

Regards