We help IT Professionals succeed at work.

When I select a value in a cell I want it to update other information in side the spreadsheet

cybersi
cybersi asked
on
HI All

Stage 1 RED
I have a spread sheet that calculates gas usage and then creates a invoice.  When I select the meter number which is controlled by data validation I want it to update the flat number B11 from "a4 Sub meters",  Name B10 from b4 Sub Meters , Tenant Code e7 from c4 sub meters.

Stage 2 BLUE

When I enter the  value for "period to" on the invoice I would like it to update "Meter Reading d17 from N4 Sub Meters",  "Units Used from N22 Sub Meters" , "Kilowatts Used f17 from N40 Sub Meters" and "Cost G17 from R59 sub meters".

Hope that makes sense

Regards

Simon
Gas-Accnts.xlsx
Comment
Watch Question

Finance Analyst
Commented:
Stage 1:
Formulas as follows:

Name in B10 =INDEX('Sub Meters'!$A$3:$N$17,MATCH($A$17,'Sub Meters'!$E$3:$E$17,0),2)
Flat number in B11 ="Flat "&INDEX('Sub Meters'!$A$3:$N$17,MATCH($A$17,'Sub Meters'!$E$3:$E$17,0),1)
Tenant/Customer ID in E7 =INDEX('Sub Meters'!$A$3:$N$17,MATCH($A$17,'Sub Meters'!$E$3:$E$17,0),3)

You will notice that these are pretty much the same apart from the last digit.

These use the INDEX function and area A3:N17 on the Sub Meters sheet and return the value from the column specified in that last digit; name column 2, Flat column 1, ID column 3.

Thanks
Rob H
Rob HensonFinance Analyst
Commented:
Carrying on win same vain with Stage 2:

Meter Reading D17 =INDEX('Sub Meters'!$A$3:$V$17,MATCH($A17,'Sub Meters'!$E$3:$E$17,0),MATCH($C$17,'Sub Meters'!$A$3:$V$3,0))

This uses a second MATCH function with the date to get the correct column from the meter readings.

Units used =INDEX('Sub Meters'!A21:V36,MATCH(A17,'Sub Meters'!E21:E36,0),MATCH(C17,'Sub Meters'!A21:V21,0))

Same function as above but from a different table/range.

Kilowatts Used - can't see where you are putting this but will no doubt be able to adjust one of the other formulas.

Cost in F17 =INDEX('Sub Meters'!A58:U73,MATCH(A17,'Sub Meters'!E58:E73,0),18)

Thanks
Rob H

Author

Commented:
Hi Rob

That's crazy quick but thank you.  I will put them in the cells now and see how I get on.

Regards

Simon

Author

Commented:
Absolutely Brilliant!