Using cell information in formula?

  • Thread starter Thread starter Gruben
  • Start date Start date
G

Gruben

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;FALSE));"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
 
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);INDIRECT("'stateview'!"&I3&":"&I
4);1;ONWAAR)); "fail stateview";"OK"))



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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é
 
Back
Top