Hi

I want to check a program which shows data form our systems and I used a
worksheet "stateview" for placing data from this program. In a database the
exact data is stored and this data is put in a second worksheet. This data
is a lot more, because all the data is in it. But in both worksheets the
same systems should be occur.
In a third worksheet I want the other two worksheets and I made a formula:

=IF(ISERROR(VLOOKUP(VALUE(C2);'database'!B:C;1;FAL SE));"fail database";
IF(ISERROR(VLOOKUP(VALUE(C2);stateview!F$3:F$290;1 ;FALSE)); "fail
stateview";"OK"))
(translated from dutch to english is hopefully correct)

What happens: data in stateview is added everyday in a new colomn, so the
formula must be changed and copy/paste over 300 times, because in the
formula "stateview!F$3:F$290" will be changed in eg. "stateview!H$3:H$290"
to check the new data.

Is there an easy way to change this formula, without copy/paste?
Perhaps I can use in the third worksheet a cell value (eg: I3
contains:"H3",I4 contains:"H290") and put this in this formula. How can I do
this?

Hopefully you can help me.

Thanks in advance,

Andre Gruben

See response in public.excel

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gruben" wrote in message
...
Hi

I want to check a program which shows data form our systems and I used a
worksheet "stateview" for placing data from this program. In a database
the
exact data is stored and this data is put in a second worksheet. This data
is a lot more, because all the data is in it. But in both worksheets the
same systems should be occur.
In a third worksheet I want the other two worksheets and I made a formula:

=IF(ISERROR(VLOOKUP(VALUE(C2);'database'!B:C;1;FAL SE));"fail database";
IF(ISERROR(VLOOKUP(VALUE(C2);stateview!F$3:F$290;1 ;FALSE)); "fail
stateview";"OK"))
(translated from dutch to english is hopefully correct)

What happens: data in stateview is added everyday in a new colomn, so the
formula must be changed and copy/paste over 300 times, because in the
formula "stateview!F$3:F$290" will be changed in eg. "stateview!H$3:H$290"
to check the new data.

Is there an easy way to change this formula, without copy/paste?
Perhaps I can use in the third worksheet a cell value (eg: I3
contains:"H3",I4 contains:"H290") and put this in this formula. How can I
do
this?

Hopefully you can help me.

Thanks in advance,

Andre Gruben

With I3 containing the alpha for the column, e.g. in I3: F

Try :

=IF(ISNA(MATCH(C2+0,database!B:B,0)),"fail database",
IF(ISNA(MATCH(C2+0,
INDIRECT("stateview!"&I3&"3:"&I3&"290"),0)),
"fail stateview","OK"))

or, with commas replaced by semicolons to suit your version:

=IF(ISNA(MATCH(C2+0;database!B:B;0));"fail database";
IF(ISNA(MATCH(C2+0;
INDIRECT("stateview!"&I3&"3:"&I3&"290");0));
"fail stateview";"OK"))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Gruben" wrote in message
...
Hi

I want to check a program which shows data form our systems and I used a
worksheet "stateview" for placing data from this program. In a database
the
exact data is stored and this data is put in a second worksheet. This data
is a lot more, because all the data is in it. But in both worksheets the
same systems should be occur.
In a third worksheet I want the other two worksheets and I made a formula:

=IF(ISERROR(VLOOKUP(VALUE(C2);'database'!B:C;1;FAL SE));"fail database";
IF(ISERROR(VLOOKUP(VALUE(C2);stateview!F$3:F$290;1 ;FALSE)); "fail
stateview";"OK"))
(translated from dutch to english is hopefully correct)

What happens: data in stateview is added everyday in a new colomn, so the
formula must be changed and copy/paste over 300 times, because in the
formula "stateview!F$3:F$290" will be changed in eg. "stateview!H$3:H$290"
to check the new data.

Is there an easy way to change this formula, without copy/paste?
Perhaps I can use in the third worksheet a cell value (eg: I3
contains:"H3",I4 contains:"H290") and put this in this formula. How can I
do
this?

Hopefully you can help me.

Thanks in advance,

Andre Gruben

Hi Bob,

Thanks for your help, the INDIRECT function works.
Indeed the data is added to the worksheet, but with the formula I only need
to change the colomn value in I3 and I4.

Regards,

André


"Bob Phillips" schreef in bericht
...
Andre,

When you insert a column for the new data in stateview, if it is inserted
before column F, the formula should automatically update to refelect where
column F now is.

If somehow the data is added within the table in stateview, you could try
using INDIRECT (same in Dutch I believe). Something like

=ALS(ISFOUT(VERT.ZOEKEN(WAARDE(C2);'database'!B:C; 1;ONWAAR));"fail
database";ALS(ISFOUT(VERT.ZOEKEN(WAARDE(C2);INDIRE CT("'stateview'!"&I3&":"&I
4);1;ONWAAR)); "fail stateview";"OK"))



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gruben" wrote in message
...
Hi

I want to check a program which shows data form our systems and I used a
worksheet "stateview" for placing data from this program. In a database
the
exact data is stored and this data is put in a second worksheet. This
data
is a lot more, because all the data is in it. But in both worksheets the
same systems should be occur.
In a third worksheet I want the other two worksheets and I made a
formula:

=IF(ISERROR(VLOOKUP(VALUE(C2);'database'!B:C;1;FAL SE));"fail database";
IF(ISERROR(VLOOKUP(VALUE(C2);stateview!F$3:F$290;1 ;FALSE)); "fail
stateview";"OK"))
(translated from dutch to english is hopefully correct)

What happens: data in stateview is added everyday in a new colomn, so the
formula must be changed and copy/paste over 300 times, because in the
formula "stateview!F$3:F$290" will be changed in eg.
"stateview!H$3:H$290"
to check the new data.

Is there an easy way to change this formula, without copy/paste?
Perhaps I can use in the third worksheet a cell value (eg: I3
contains:"H3",I4 contains:"H290") and put this in this formula. How can I
do
this?

Hopefully you can help me.

Thanks in advance,

Andre Gruben

"Gruben" wrote:
.. but with the formula I only need
to change the colomn value in I3 and I4.

Think the alternative formula suggested using MATCH (instead of VLOOKUP) &
INDIRECT would have worked just as well, I figure, and you would just need
to change the column value (the column letter) in one cell, I3. But
unfortunately, unlike Bob, I don't speak any Dutch <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--