Using cell information in formula?
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
|