The Mechanical Hand posted:

Originally, mistakenly, posted in the general programming thread, but anyway...

Need some help with an excel problem I've come across.

I have a drop down list with names of cities in it.

I want to set this up so when you pick a city from that list another field will populate with an address associated with that city. I know it has to do with formulas and all that but I am totally lost. Any help?

G-Dub posted:

You probably want to look at the VLOOKUP function within Excel. I presume there is one address per city? I would have a table of City|Address and then your VLOOKUP would look like:

VLOOKUP(<Cell ref of dropdown>,<range of table>,2)

I presume everything recalculates when one cell is changed, therefore the address will update when the drop down changes.

ZerodotJander posted:

Looks like you should be able to get something going using the INDIRECT function and concatenating pieces of your reference.

Meme Emulator posted:

G-Dub posted:

Is it the same cell in each workbook you are wanting to get? If you can post some sample source data and the desired sample format I will take a stab at it.

Fuoco posted:

There should be a way of doing this without the need for VBA. It's a bit inelegant though. Try the following...

In cell B2 put the following formula:

code:

="='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\" & B$1+1 & "_budgets\[" & LOWER($A2) & "]GR R'!$H$104"

This will return text to the cell of the full reference that we need. Fill the formula across and down to cover all the space required.

Save your spreadsheet at this point (but don't close it). Open up Notepad.

In your spreadsheet, select B2 down to the end, and copy the cells. Paste these into Notepad.

Then, copy the full text back from Notepad and paste into the same place in Excel. You'll notice at this point, rather than pasting back the reference as text, it's now actually reading from the reference instead.

Do the same with the remaining columns to get the remaining data.

ZerodotJander posted:

Make sure you have relative references turned on.

ZerodotJander posted:

How are you selecting your range to sort during your macro record? Make sure whatever way you are doing that selects the full table and not just the range specified at the moment you record the macro.

You could also try just adding a sort column and numbering all your guests in order, and sorting based on that instead of a multi-field sort.

Sub Par posted:

Can you post the code? Excel isn't very good at recording a macro with the idea of a dynamic range, and oftentimes you will need to make a couple edits to what was recorded to make it work as you intended.

Excel posted:

Sub Mac2()

End Sub
Sub Sort()
'
' Sort Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Cells.Select
Range("K12").Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A60") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C60") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D60") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B60") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:J60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Ivan Drago posted:

Very simple question (I think):

What I want to do is find a value in an array and return a different value a certain number of columns over AND a certain number of rows down at the same time, like a combination of VLOOKUP and HLOOKUP. Is this possible?

As a quick means of reference, here's a simple table. How can I write a formula that finds "Jim" and returns his salary from this set of data? If there some sort of nested lookup function that would make this possible?

code:

     A       B       C
1    Jim     
2            Age     27
3            ID      2
4            Salary  $38,000
5    
6    Tom
7            Age     32
8            ID      8
9            Salary  $37,500

E: If I were to use =OFFSET(A1,3,2,1,1) this would work, but for the purposes of this file it would be easier if I could reference the lookup value by the text that inside ("Jim") rather than the cell itself (A1). I'm sure there's a way to nest an offset inside a lookup or a match or something along those lines but my brain isn't working today.

esquilax posted:

Try something like "=index(C:C, match("Jim", A:A, 0)+3)"

Index() can also change columns if you need it.