If statement 08/20/09

[Angie G.]

Can someone help?

I have a workbook with 2 spreadsheets. 1 sheet is data and the other is the
report.

Both sheets have Rig no., Income and Expense Columns. I want a formula in
the report spreadsheet, to go to data spreadsheet and get corresponding
Income and/or Expense amount to corresponding Rig no; because same rig no.
may be listed several times on the report spreadsheet.

Thanks,

 

[Jacob Skaria]

Take a look at the worksheet function VLOOKUP() which searches for a value in
the first column of a table array and returns a value in the same row from
another column in the table array.

Syntax is
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

OR
=VLOOKUP(A1,Shee2!A1100,4,0)
(This formula if applied in Sheet1 cell B1 will search the value of Sheet1
A1 in Sheet2 A1:A100 and returns the matching value from Column D (4) of
sheet2)


If this post helps click Yes

 

[Eduardo]

Hi,
if I didn't misunderstood you have in column A the rig number and you want
to summarize from the sheet called data if information start in row 2 and you
have your incomes in column B and expenses in column C in the report column B
enter

=sumproduct(--(A2=data!$A$1:$A$1000),data!$B$1:$B$1000)

in column C you will enter

=sumproduct(--(A2=data!$A$1:$A$1000),data!$c$1:$c$1000)

change range to fit your needs but remember both sides of the formula need
the same range

 

[Angie G.]

Eduardo,

I don't want to summarize. On the data spreadsheet, rig no is only listed
once, on the report spreadsheet it can be listed more than once. *(see
example below)I just want to be able to put a formula in the report
spreadsheet income cell - to go to the data spreadsheet - to look in col A
being the rig no. column, if in the income column (col b)has an amount to
copy to report cell.

Example below:

Data sheet - entered manually.
Col A Col B Col C
Rig # Income Expense
4 1000 500

Report Sheet

Col A Col B Col C Col D Col E
Rig # Income Expense xxxx xxxx
4 *
4
5
5
6
6

Hope this makes better sense. Thanks for all your help.