I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson

That would be fine if the data were always in the same location, but I am
dealing with a national spreadsheet. Names come and go, and categories
change. I need to find specific info on my reps only. Thank you, though. I
did learn something from Debra's notes. Using her table as a reference, I
need to know how many medium sweaters there are, just imagine a worksheet a
thousand times larger with more products and sizes/colors, etc. and the
creator can't keep them in the same spot.

"Dave Peterson" wrote:

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson

I don't understand why =index(match()) won't work.

If your header labels are always in row 1 and always spelled the same way and
the names are always in column A, then if you match by header to get the column
and match by name to get the row, I don't see why it wouldn't work.

Cellzman wrote:

That would be fine if the data were always in the same location, but I am
dealing with a national spreadsheet. Names come and go, and categories
change. I need to find specific info on my reps only. Thank you, though. I
did learn something from Debra's notes. Using her table as a reference, I
need to know how many medium sweaters there are, just imagine a worksheet a
thousand times larger with more products and sizes/colors, etc. and the
creator can't keep them in the same spot.

"Dave Peterson" wrote:

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson


--

Dave Peterson

Dave, you're right. I wasn't reading the formulas correctly. It will work
just fine. Thank you very much. This will save me about an hour a day once
I get it set up.

"Dave Peterson" wrote:

I don't understand why =index(match()) won't work.

If your header labels are always in row 1 and always spelled the same way and
the names are always in column A, then if you match by header to get the column
and match by name to get the row, I don't see why it wouldn't work.

Cellzman wrote:

That would be fine if the data were always in the same location, but I am
dealing with a national spreadsheet. Names come and go, and categories
change. I need to find specific info on my reps only. Thank you, though. I
did learn something from Debra's notes. Using her table as a reference, I
need to know how many medium sweaters there are, just imagine a worksheet a
thousand times larger with more products and sizes/colors, etc. and the
creator can't keep them in the same spot.

"Dave Peterson" wrote:

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson


--

Dave Peterson

Dave,

I tried it at work today and it works for one of the things I need to look
up, but not the others. I am as sure as I can be that I have put the formula
in correctly. I think the values I am trying to match may be causing an
issue. The value that works is "Gross Adds" one that doesn't (and is
representative of the others) is "% data +data existing". I copied and
pasted values so I wouldn't need to worry about getting spelling or spacing
wrong. I have read through the article you recommended and don't see the
answer. It could be that I am not educated enough to understand what she is
saying, though, since she does devote some space to troubleshooting.

I have one additional question that came up today while I was working on it.
The filename of the indexed report changes at least monthly. The names of
the matched information stay consistent. Any tips on how to efficiently
update the worksheet without having to go into every cell every month?

Where is the next best place to look if you don't have the info?

"Dave Peterson" wrote:

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson

#1. I'd bet that it was a typing difference. You may have leading/trailing
spaces that are included in one, but not the other (or a trailing
alt-enter???). If you make that name something easy, but unique, does it work:

QWERASDF
(in both the formula and the header)

#2. Since it's the filename that's changing, maybe just Edit|Links|change
source (or even an Edit|Replace, but don't make any typos--you'll be dismissing
lots and lots of dialogs!).

Cellzman wrote:

Dave,

I tried it at work today and it works for one of the things I need to look
up, but not the others. I am as sure as I can be that I have put the formula
in correctly. I think the values I am trying to match may be causing an
issue. The value that works is "Gross Adds" one that doesn't (and is
representative of the others) is "% data +data existing". I copied and
pasted values so I wouldn't need to worry about getting spelling or spacing
wrong. I have read through the article you recommended and don't see the
answer. It could be that I am not educated enough to understand what she is
saying, though, since she does devote some space to troubleshooting.

I have one additional question that came up today while I was working on it.
The filename of the indexed report changes at least monthly. The names of
the matched information stay consistent. Any tips on how to efficiently
update the worksheet without having to go into every cell every month?

Where is the next best place to look if you don't have the info?

"Dave Peterson" wrote:

It sounds like =index(match()) would work nicely:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html



Cellzman wrote:

I have a report I use that has more info than I need to deal with. I would
like to be able to create a worksheet that would allow me to pull information
from that report by referncing the labels that have the information. For
example, I might want to know how many sales a rep has. I want Excel to go
into the report and look for New Sales along the top and Sarah along the side
and give me the number. The position of this data changes frequently, so I
cannot reference cell locations or columns and rows. Can this be done in
Excel?

--

Dave Peterson


--

Dave Peterson