Using cell value in formula?

[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

 

[Bob Phillips]

See response in public.excel

--

HTH

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

 

[Max]

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"))

 

[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é

 

[Max]

.. 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>