Index and match formula resulting in #REF

[Guest]

I have three worksheets. One is where I enter all my property data, the
second is a template which is automatically populated when I input the
property name in the third sheet. Also, the third sheet contains a list of
property names and a macro that will automatically populate and print the
template when I put an "X" in the column next to the property name. So if I
wanted to print twenty specific employee templates, I can check off the
twenty that I need to print.

In my second worksheet is a template that is automatically populated using a
series of vlookup formulas. There is one section that I would like to
customize since not all the lines there are applicable to each property and
not all properties have the same data. For example, some properties have a
number in a tax column, some have a number in insurance column and some have
a number in capital expenditure column. I only want to show the word "Tax",
"Insurance" or "Capital expenditure" if there is a number for each those of
the corresponding names. So if property XYZ has a number say 300 in the tax
column, 0 in the insurance column and 400 in the capital expenditure column,
my template should only show the word "Tax" in one line and "capital
expenditure" in another. I don't want to show the word "insurance" in
another line since it has a "0" value.

This is the formula I used
"=index(database,match(B1,prop_name,0),match(tax,database,0),match(insurance,database,0),match(capital_expenditure,database,0)"

I used defined name ranges.
Database = all my data in sheet 1
Prop_name = list of properties in sheet 1
tax = tax column in sheet 1
insurance = insurance column in sheet 1
capital expenditure = capital expenditure in sheet 1

Cell B1 is in Sheet 2 which is my template. In B1, I have a vlookup formula
that refers to Sheet 3 where I can input the name of the property in one
cell, and B1 reads that input and populates the template in sheet 2.

Am I getting a result of #REF because the index,match formula cannot
evaluate the vlookup formula in cell B1?

Also, is my formula correct? if not, please help !!!

Hope I am clear. Thanks for any help.

 

[Roger Govier]

Hi

Did this problem not start life as an employee problem with Weekly,
Bi-weekly and Monthly payment cycles??

I don't think you can achieve what you want, missing out rows where
value is 0, without using VBA code.

"=index(database,match(B1,prop_name,0),match(tax,database,0),match(insurance,database,0),match(capital_expenditure,database,0)"

This formula will not work.
What would work would be
=IF(INDEX(database,MATCH(B1,prop_name,0),MATCH("Tax",Row_1_of_Database,0))>0,"Tax")
where
Row_1_of_Database is replaced either by the relevant cell references, or
a named range.

This will return the value Tax in the cell. It could be extended with
additional IF statements identically constructed, but for "Insurance"
and "Capital Value" respectively, but of course the first one found
would be returned.
Repeating the formula on the next line, would return the same result as
the line above.

My advice remains the same as when you used Employee as the example.
Expenditure type should all be in one column of your database, with a
separate line entry for the same property to cover each type of
expenditure incurred. The task of populating a cell with the name of the
expenditure, but omitting it if the value is zero, becomes a very simple
IF statement.

 

[Guest]

I assume (dangerous!) that the formula is attempting to return TAX, INSURANCE
or CAPITAL EXPENDITURE
and as these can appear in any combination on one to three consecutive
lines; it is slightly more complicated than appears. Plus the formula won't
do this.

If possible, can you send a sample workbook as it is much easier to work
with the real data/situation.

E-mail to toppers at REMOVETHISjohntopley.fsnet.co.uk

 

[Guest]

Toppers,

I will send the sample workbook to you. I really really appreciate this.

 

[Guest]

Toppers,

Is your email address (e-mail address removed)? or is it
REMOVETHISjohntopley.fsnet.co.uk ? An error occurred when I tried to email
me as your email below is missing an "@" sign.

 

[Guest]

Toppersatjohntopley.fsnet.co.uk

replace at with @