I work with large databases with ever changing column and row headings. I
need to create reports that use both static column and row headings and does
not reference row or column numbers. For instance I need company X sales for
customer Z. This month that may be column C and row 4 but next month it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month defeats
the purpose and efficiency of creating the formulas and certainly decreases
efficiency on my part. Does anyone know how to create a function that will
look up values based on two static criteria rather than column and row number
references?

If you made use of named ranges (which can be created from the column
headings with Insert | Name | Create), then the formulae would be the
same even if the columns were moved,

eg = VLOOKUP(source,table,col,0)

Hope this helps.

Pete

On May 4, 5:23 pm, Dawn - KY <Dawn -
wrote:
I work with large databases with ever changing column and row headings. I
need to create reports that use both static column and row headings and does
not reference row or column numbers. For instance I need company X sales for
customer Z. This month that may be column C and row 4 but next month it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month defeats
the purpose and efficiency of creating the formulas and certainly decreases
efficiency on my part. Does anyone know how to create a function that will
look up values based on two static criteria rather than column and row number
references?

Hi Dawn

Assuming Company is in Column A and Customer's data is in columns B to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer Z",$A$1:$Z$1,0),0)
Change ranges to suit

--
Regards

Roger Govier


"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row headings.
I
need to create reports that use both static column and row headings
and does
not reference row or column numbers. For instance I need company X
sales for
customer Z. This month that may be column C and row 4 but next month
it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month
defeats
the purpose and efficiency of creating the formulas and certainly
decreases
efficiency on my part. Does anyone know how to create a function that
will
look up values based on two static criteria rather than column and row
number
references?

Thanks, Roger. At least I'm getting closer to solving my problem. I'm getting
an answer of 0 now rather than N/A or Value. This is the string I am now:

=VLOOKUP("Equity Income",'External P&L - Grp Curr'!$C$29:$BJ$66,MATCH("Corp
Adm",$E$29:$BJ$29,0),0)

Equity income is the account
The next part is the range on a different tab
Corp Adm is the business unit for which I need equity income.
The final part is the range where all the buisness units are located.

"Roger Govier" wrote:

Hi Dawn

Assuming Company is in Column A and Customer's data is in columns B to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer Z",$A$1:$Z$1,0),0)
Change ranges to suit

--
Regards

Roger Govier


"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row headings.
I
need to create reports that use both static column and row headings
and does
not reference row or column numbers. For instance I need company X
sales for
customer Z. This month that may be column C and row 4 but next month
it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month
defeats
the purpose and efficiency of creating the formulas and certainly
decreases
efficiency on my part. Does anyone know how to create a function that
will
look up values based on two static criteria rather than column and row
number
references?

Hi

I think that should be
=VLOOKUP("Equity Income",'External P&L - Grp Curr'!$C$29:$BJ$66,
MATCH("Corp Adm",'External P&L - Grp Curr'!$E$29:$BJ$29,0),0)

I would also put Equity Income and Corp Adm in separate cells, then use
those cell locations in the formula.
The formula then remains unchanged if you want to use a different
Company and/or Customer

--
Regards

Roger Govier


"Dawn - KY" wrote in message
...
Thanks, Roger. At least I'm getting closer to solving my problem. I'm
getting
an answer of 0 now rather than N/A or Value. This is the string I am
now:

=VLOOKUP("Equity Income",'External P&L - Grp
Curr'!$C$29:$BJ$66,MATCH("Corp
Adm",$E$29:$BJ$29,0),0)

Equity income is the account
The next part is the range on a different tab
Corp Adm is the business unit for which I need equity income.
The final part is the range where all the buisness units are located.

"Roger Govier" wrote:

Hi Dawn

Assuming Company is in Column A and Customer's data is in columns B
to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer Z",$A$1:$Z$1,0),0)
Change ranges to suit

--
Regards

Roger Govier


"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row
headings.
I
need to create reports that use both static column and row headings
and does
not reference row or column numbers. For instance I need company X
sales for
customer Z. This month that may be column C and row 4 but next
month
it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month
defeats
the purpose and efficiency of creating the formulas and certainly
decreases
efficiency on my part. Does anyone know how to create a function
that
will
look up values based on two static criteria rather than column and
row
number
references?

Got it! After sleeping on it all weekend I came back in fresh this morning
and worked out my problem rather quidkly. The match formula should start with
the same column as the Vlookup formula. When I changed the match formula to
MATCH("Corp Adm",'External P&L - Grp Curr'!$C$29:$BJ$29,0) I got exactly what
I was looking for. Thank you for helping me. This will save me a lot of time
in the future.

"Roger Govier" wrote:

Hi

I think that should be
=VLOOKUP("Equity Income",'External P&L - Grp Curr'!$C$29:$BJ$66,
MATCH("Corp Adm",'External P&L - Grp Curr'!$E$29:$BJ$29,0),0)

I would also put Equity Income and Corp Adm in separate cells, then use
those cell locations in the formula.
The formula then remains unchanged if you want to use a different
Company and/or Customer

--
Regards

Roger Govier


"Dawn - KY" wrote in message
...
Thanks, Roger. At least I'm getting closer to solving my problem. I'm
getting
an answer of 0 now rather than N/A or Value. This is the string I am
now:

=VLOOKUP("Equity Income",'External P&L - Grp
Curr'!$C$29:$BJ$66,MATCH("Corp
Adm",$E$29:$BJ$29,0),0)

Equity income is the account
The next part is the range on a different tab
Corp Adm is the business unit for which I need equity income.
The final part is the range where all the buisness units are located.

"Roger Govier" wrote:

Hi Dawn

Assuming Company is in Column A and Customer's data is in columns B
to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer Z",$A$1:$Z$1,0),0)
Change ranges to suit

--
Regards

Roger Govier


"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row
headings.
I
need to create reports that use both static column and row headings
and does
not reference row or column numbers. For instance I need company X
sales for
customer Z. This month that may be column C and row 4 but next
month
it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each month
defeats
the purpose and efficiency of creating the formulas and certainly
decreases
efficiency on my part. Does anyone know how to create a function
that
will
look up values based on two static criteria rather than column and
row
number
references?

Thanks for the feed back letting us know you resolved the problem.


--
Regards

Roger Govier


"Dawn - KY" wrote in message
...
Got it! After sleeping on it all weekend I came back in fresh this
morning
and worked out my problem rather quidkly. The match formula should
start with
the same column as the Vlookup formula. When I changed the match
formula to
MATCH("Corp Adm",'External P&L - Grp Curr'!$C$29:$BJ$29,0) I got
exactly what
I was looking for. Thank you for helping me. This will save me a lot
of time
in the future.

"Roger Govier" wrote:

Hi

I think that should be
=VLOOKUP("Equity Income",'External P&L - Grp Curr'!$C$29:$BJ$66,
MATCH("Corp Adm",'External P&L - Grp Curr'!$E$29:$BJ$29,0),0)

I would also put Equity Income and Corp Adm in separate cells, then
use
those cell locations in the formula.
The formula then remains unchanged if you want to use a different
Company and/or Customer

--
Regards

Roger Govier


"Dawn - KY" wrote in message
...
Thanks, Roger. At least I'm getting closer to solving my problem.
I'm
getting
an answer of 0 now rather than N/A or Value. This is the string I
am
now:

=VLOOKUP("Equity Income",'External P&L - Grp
Curr'!$C$29:$BJ$66,MATCH("Corp
Adm",$E$29:$BJ$29,0),0)

Equity income is the account
The next part is the range on a different tab
Corp Adm is the business unit for which I need equity income.
The final part is the range where all the buisness units are
located.

"Roger Govier" wrote:

Hi Dawn

Assuming Company is in Column A and Customer's data is in columns
B
to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer
Z",$A$1:$Z$1,0),0)
Change ranges to suit

--
Regards

Roger Govier


"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row
headings.
I
need to create reports that use both static column and row
headings
and does
not reference row or column numbers. For instance I need company
X
sales for
customer Z. This month that may be column C and row 4 but next
month
it could
be colum S and row 15.

Taking the time to re-write Vlookup or Hlookup functions each
month
defeats
the purpose and efficiency of creating the formulas and
certainly
decreases
efficiency on my part. Does anyone know how to create a function
that
will
look up values based on two static criteria rather than column
and
row
number
references?