If cell 1 and 2 contain x and y then reference z

[kujo21]

Working on a travel spreadsheet in Office 2010

1st tab that I will insert State and City, that will need to auto fill another cell on that row with daily allowance * number of days cell (also on in that row)

2nd tab has reference information in each ROW.

Oh yeah and in the city and state I'd like to make those drop down menus, or start auto filling in when typing.

Suggestions? Thanks!!

 

[LloydFinancials]

Welcome Kujo,

OK for your first question: On the first tab, you want a city and state column with dropdowns showing a list of the city and state values from the 2nd tab. Is that correct? Is that what you want to know how to create?

Luke

 

[kujo21]

Thanks Luke for helping. On the first tab I have to fill out for each person where they are going. When I do that, I want it to search the 2nd tab for the daily allowance of that location and input it to another cell on the first tab, multiply the number of days in a different cell on the first tab.

For example I'm trying to find for XYZ

Tab 1 [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name [/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]# Days[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD]Syracuse[/TD]
[TD]New York[/TD]
[TD]8[/TD]
[TD]XYZ[/TD]
[/TR]
</tbody>[/TABLE]


Tab 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City[/TD]
[TD]State[/TD]
[TD]Allowance/Day[/TD]
[/TR]
[TR]
[TD]Syracuse[/TD]
[TD]New York[/TD]
[TD]$87[/TD]
[/TR]
</tbody>[/TABLE]


2nd question was more about when I start typing the City and State in to tab 1, I'm curious if I can get it to auto-fill. Its got 37000 locations and I figure it would be a nice to save time.

 

[LloydFinancials]

For Cost you can vlookup(B2,'Tab 2'!$A$1:$C$999,3,FALSE)*D2

To autofill:

1. Click Advanced, and then under Editing options, clear or select the Enable AutoComplete for cell values check box to turn automatic completion of cell values on or off