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