HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Try the following array formulas that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

or

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=H2)*(She et1!B2:B8=I2),0),MATCH(
J2,Sheet1!C1:F1,0))

....where H2 contains the region of interest, such as 101, I2 contains
the year of interest, such as 2001, and J2 contains the month of
interest, such as 3.

Hope this helps!

In article ,
stefan7219
wrote:

HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan

Note that I've assumed that Sheet1 contains your table. Change the
sheet name accordingly.

In article ,
Domenic wrote:

Try the following array formulas that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

or

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=H2)*(She et1!B2:B8=I2),0),MATCH(
J2,Sheet1!C1:F1,0))

...where H2 contains the region of interest, such as 101, I2 contains
the year of interest, such as 2001, and J2 contains the month of
interest, such as 3.

Hope this helps!

In article ,
stefan7219
wrote:

HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan

Thanks Domenic.

I must be doing something wrong, because all I get from the formula is
"N/A"

Did you actually try the formula and it worked?

Thanks
Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Did you use ctrl-shift-enter to enter the formula?

Did you double check the values to verify that there is a match in the required
columns/rows?

stefan7219 wrote:

Thanks Domenic.

I must be doing something wrong, because all I get from the formula is
"N/A"

Did you actually try the formula and it worked?

Thanks
Stefan

--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

--

Dave Peterson

I made sure I used ctrl+shift and Enter. It did not make a difference.

I've trying to disect the formula and see where I might be going
wrong.

At this time I can't replicate the middle part :
"MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0)"

So I'm thinking, something is wrong in here.

Anyway, if somebody have any ideas, please let me know.

// Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Your original data had some funny asterisks throughout.

I think Domenic thought they were typos in the post--but if that's the way your
data actually looked, then this formula still worked for me:

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8="*101")* (Sheet1!B2:B8="2001*"),0),
MATCH(3,Sheet1!C1:F1,0))

Still array entered.

I still think it's a difference in data--not the formula.


stefan7219 wrote:

I made sure I used ctrl+shift and Enter. It did not make a difference.

I've trying to disect the formula and see where I might be going
wrong.

At this time I can't replicate the middle part :
"MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0)"

So I'm thinking, something is wrong in here.

Anyway, if somebody have any ideas, please let me know.

// Stefan

--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

--

Dave Peterson

In article ,
Dave Peterson wrote:

Your original data had some funny asterisks throughout.
I think Domenic thought they were typos in the post...

Yep, that's right. I thought they were either typos or some sort of
demarcation. As it turns out, looking at the post from within the
originating forum, it denotes bolding.

I still think it's a difference in data--not the formula.

Yeah, I think so... :)

Stefan,

If you'd like, I can email you a sample file. If so, you can send me
your email address at

In article ,
stefan7219
wrote:

I made sure I used ctrl+shift and Enter. It did not make a difference.

I've trying to disect the formula and see where I might be going
wrong.

At this time I can't replicate the middle part :
"MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0)"

So I'm thinking, something is wrong in here.

Anyway, if somebody have any ideas, please let me know.

// Stefan

Very industrious!

I have a difficult enough time just finding these here newsgroups!



Domenic wrote:

In article ,
Dave Peterson wrote:

Your original data had some funny asterisks throughout.
I think Domenic thought they were typos in the post...

Yep, that's right. I thought they were either typos or some sort of
demarcation. As it turns out, looking at the post from within the
originating forum, it denotes bolding.

I still think it's a difference in data--not the formula.

Yeah, I think so... :)

--

Dave Peterson

Thanks Dave,
obviously I'm doing something wrong, so at this point I'm thinking it
might be the entry of the formula.
I write the formula, actually copy&paste, and then hit Ctrl+Shift
Enter.

Is that the correct way of doing it?

// Stefan

PS: What is your output from the formula as written with the data
provided?


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Stefan,
In reviewing the formula that Domenic gave on the first response,
I noticed that his third expression of "Sheet1!" is shown as "S heet1!"
with a blank space after "S". Your message indicates that you copied
exactly. Is that the potential typo that you may be looking for?
GeorgeF


--
GeorgeF
------------------------------------------------------------------------
GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124
View this thread: http://www.excelforum.com/showthread...hreadid=466862

I saw that also and I corrected for it.

I still can't get to work.

George, is the formula working for you?

// Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Stefan,

To make sure that we're in the same wave length, here is how I
understood that your data is laid out...

A2:A8 contains the 'Region', such as 101

B2:B8 contains the 'Year', such as 2001

C1: 1

D1: 2

E1: 3

F1: 4

C2:F8 contains your data

Then, the following formula...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=102)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

....confirmed with CONTROL+SHIFT+ENTER, will return 0.81. Does this help?

In article ,
stefan7219
wrote:

I saw that also and I corrected for it.

I still can't get to work.

George, is the formula working for you?

// Stefan

An unfortunate line break:

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=102)*(Sh eet1!B2:B8=2001),0),
MATCH(3,Sheet1!C1:F1,0))

....confirmed with CONTROL+SHIFT+ENTER, will return 0.81. Does this help?

(copy and paste directly into the formula bar--then hit ctrl-shift-enter.)

Domenic wrote:

Stefan,

To make sure that we're in the same wave length, here is how I
understood that your data is laid out...

A2:A8 contains the 'Region', such as 101

B2:B8 contains the 'Year', such as 2001

C1: 1

D1: 2

E1: 3

F1: 4

C2:F8 contains your data

Then, the following formula...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=102)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

...confirmed with CONTROL+SHIFT+ENTER, will return 0.81. Does this help?

In article ,
stefan7219
wrote:

I saw that also and I corrected for it.

I still can't get to work.

George, is the formula working for you?

// Stefan

--

Dave Peterson

Stefan,
For the amount of time I've spent on this, I don't see the
solution. I initially reproduced the "5.03", then I change the
equation and have gone from N/A to 0. Where did you place the equation
on Sheet 2? In C2? Sorry, I've run out of time for the weekend.
GeoregeF


--
GeorgeF
------------------------------------------------------------------------
GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124
View this thread: http://www.excelforum.com/showthread...hreadid=466862

First Thanks for trying to help me.

I redid every thing from scratch. Ensured that your data was the same
as mine, and it was.

I copied and pasted, in sheet 2 and sheet 1, in the formula in the
formula bar.

With the formula as written, I got a little box come up asking me to
update the values: eet1.

I'm asuming that comes from the third "Sh eet1" and the space between
the h & e, in the formula.
I took out the space and tried again. That did not work either.

I keep getting the"#N/A" error in cell.
If I click on the exclamation point next to the cell to evaluate the
error it looks like something is wrong in the middle of the formula.
the second "Sheet1" statement.
It is underlined and indicates something is wrong.

Could I be missing some kind of Add-In?

I'm not giving up, if it works for you the formula must work for me. I
just need to figure out how I have srewed it up so far.

Any ideas?

// Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Maybe all the problems are caused by seeing the suggestions via the excelforum.

It seems to be adding extra characters where there are none. (Google can do
this, too.)

Maybe connecting to the newsgroups directly would make it easier to copy|paste.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tut...e_ng/index.htm

And if you're looking for old posts:

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_gr...Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

stefan7219 wrote:

First Thanks for trying to help me.

I redid every thing from scratch. Ensured that your data was the same
as mine, and it was.

I copied and pasted, in sheet 2 and sheet 1, in the formula in the
formula bar.

With the formula as written, I got a little box come up asking me to
update the values: eet1.

I'm asuming that comes from the third "Sh eet1" and the space between
the h & e, in the formula.
I took out the space and tried again. That did not work either.

I keep getting the"#N/A" error in cell.
If I click on the exclamation point next to the cell to evaluate the
error it looks like something is wrong in the middle of the formula.
the second "Sheet1" statement.
It is underlined and indicates something is wrong.

Could I be missing some kind of Add-In?

I'm not giving up, if it works for you the formula must work for me. I
just need to figure out how I have srewed it up so far.

Any ideas?

// Stefan

--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

--

Dave Peterson