offset and find function w/ reference to another sheet

[OldManDemosthenes]

Is it possible to create a formula say on sheet1 A2 which takes the value in cell A1 and finds that same value in sheet2? It then uses an offset formula to find a value listed below the original A1 value and place it in A2.

I know that this can ordinarily be done with hlookup but for my purposes that won't work. The reason for this is that "name" value that is searched for is not in a consistent row or column in sheet2 (data is downloaded from the internet and I don't want to alter the layout). Secondly, I assume I need to use the offset formula because the data on sheet2 is listed in 2 columns below each of the name values. So if the name value is on sheet2 B5, I will eventually need to pull the values from sheet2 b7 and c7.

I'm willing to use basic excel formulas or VBA code - whatever works. Any suggestions are much appreciated!

 

[T. Valko]

Is it possible to create a formula say on sheet1 A2 which takes the value in cell A1 and finds that same value in sheet2? It then uses an offset formula to find a value listed below the original A1 value and place it in A2.

I know that this can ordinarily be done with hlookup but for my purposes that won't work. The reason for this is that "name" value that is searched for is not in a consistent row or column in sheet2 (data is downloaded from the internet and I don't want to alter the layout). Secondly, I assume I need to use the offset formula because the data on sheet2 is listed in 2 columns below each of the name values. So if the name value is on sheet2 B5, I will eventually need to pull the values from sheet2 b7 and c7.

I'm willing to use basic excel formulas or VBA code - whatever works. Any suggestions are much appreciated!

Can you be more specific as to where to look for this value? Can you narrow it down to a certain range?

Sheet2 is a pretty big area to look for something!

 

[OldManDemosthenes]

The data sheet is formatted in a way that the variable names are listed 4 in a row with the corresponding data for each one listed below the name. There are a ton of rows. The range for the data will be sheet2 B:P. The amount of rows will vary.

I know I didn't describe that well but, if you want to look at the actual sheet that I am downloading go to http://www.boe.ca.gov/news/tsalescont09.htm then click on the "Taxable Sales in California Report By Type of Business for Cities and Counties" Its near the top. Take a look at the "2009 p15 T5 Agoura Hills" tab. My data sheet is roughly the same except with more rows.

Ideally, I do not want to have to alter the format of the data page because I don't trust future users of the program to properly make the alterations

 

[T. Valko]

The data sheet is formatted in a way that the variable names are listed 4 in a row with the corresponding data for each one listed below the name. There are a ton of rows. The range for the data will be sheet2 B:P. The amount of rows will vary.

I know I didn't describe that well but, if you want to look at the actual sheet that I am downloading go to http://www.boe.ca.gov/news/tsalescont09.htm then click on the "Taxable Sales in California Report By Type of Business for Cities and Counties" Its near the top. Take a look at the "2009 p15 T5 Agoura Hills" tab. My data sheet is roughly the same except with more rows.

Ideally, I do not want to have to alter the format of the data page because I don't trust future users of the program to properly make the alterations

OK, I got the file.

So, what exactly do you want to look for? Be VERY specific!

 

[OldManDemosthenes]

My model includes sheet(RetailData09) which is composed of the data from the link that I sent. For simplicity you can pretend that this sheet looks exactly the same as sheet(2009 p15 T5 Agoura Hills). In reality the RetailData09 sheet copies all the data from sheets "2009 p15..." through "2009 p32..." and lists this data directly below the original data from the 2009 p15 page.

When the user runs the userform located on the sheet(AutoRun), they can select as many cities or towns from the list as they like. Let's just say for simplicity they select only "Agoura Hills" which is the first city.

What I need to do is have the userform find the cell which contains "Agoura Hills" on RetailData09 which is located in B4. (If Alameda was selected its F4 or if Apple Valley is selected its J23). What I need to do is take all of the retail data listed below the selected city and place it in row 1 of sheet(AutoRun)

So the result is on sheet(AutoRun) A1, it would display "Agoura Hills", B1 "13" (for Motor Vehicle Permits), C1 "2773" (for Motor Vehicle Transactions) and so on for all "Retail and Food Services" categories. If there is a second city selected, its data would be placed in row 2.

Hopefully this is enough information (or too much information)

Thank you for taking a look at this!

 

[T. Valko]

My model includes sheet(RetailData09) which is composed of the data from the link that I sent. For simplicity you can pretend that this sheet looks exactly the same as sheet(2009 p15 T5 Agoura Hills). In reality the RetailData09 sheet copies all the data from sheets "2009 p15..." through "2009 p32..." and lists this data directly below the original data from the 2009 p15 page.

When the user runs the userform located on the sheet(AutoRun), they can select as many cities or towns from the list as they like. Let's just say for simplicity they select only "Agoura Hills" which is the first city.

What I need to do is have the userform find the cell which contains "Agoura Hills" on RetailData09 which is located in B4. (If Alameda was selected its F4 or if Apple Valley is selected its J23). What I need to do is take all of the retail data listed below the selected city and place it in row 1 of sheet(AutoRun)

So the result is on sheet(AutoRun) A1, it would display "Agoura Hills", B1 "13" (for Motor Vehicle Permits), C1 "2773" (for Motor Vehicle Transactions) and so on for all "Retail and Food Services" categories. If there is a second city selected, its data would be placed in row 2.

Hopefully this is enough information (or too much information)

Thank you for taking a look at this!

Ok, sounds like you need a VBA solution.

I'm not much of a programmer so you'll get a better result from someone else.

 

[OldManDemosthenes]

Fair enough - I know that's where this is eventually heading. I really appreciate you taking the time to help