[Solved] Copy right, but increment row references down

[LeroyTennison]

Environment: OpenOffice 4.11.3 on Linux. I'm trying to construct a cost comparison spreadsheet without a lot of keying. It's for electrical providers (in Texas, where i live, it's an open market where you can shop for providers). My spreadsheet has the following features

Column A has 12 rows (starting in row 3) for 12 months of electrical usage, column B has the actual kwh usage and column C through J are intended to calculate the cost per month using the provider's formula.

Row 15 has the totals for the 12 months and row 16 has the average.

I left a little space so row 21 starts the providers and has the following defined columns: Company, per month base cost (fixed such as $10/month), energy charge (cents/kwh), Low use fee (fixed amount), Low use criteria (less than so many kilowatt hours), Medium use rebate (fixed amount), Medium use criteria (so many kilowatt hours). Hi use rebate (fixed amount), Hi use criteria, delivery cost (fixed per month) and per kilowatt delivery cost (cents/kwh)

The formula is complex, for the first provider it's =B3*($C$21+$K$21)/100+$B$21+$J$21+IF(B3<$E$21;$D$21;0)+IF(AND(B3>$G$21;B3<$I$21);-$F$21;0)+IF(B3>$I$21;-$H$21;0)

Replicating this down the rows for a given provider is easy - it just works. The issue is that, for the next provider, $21 references need to change to $22, for the third provider its $23 and so on through $28.

The spreadsheet is attached.

I need a formula which will not only change B3 to B4 and so on down the sheet but also will change $21 references to $22 through #28 across the sheet.

I've "played" with absolute and relative references and can't seem to get the functionality i am looking for. The reason i need it is that some of the provider parameters have changed and i need to make modifications to the formulas.

Any help or ideas would be greatly appreciated, thanks.

Edit: Changed subject, was Replicating cell references  Make your post understandable by others  -- MrProgrammer, forum moderator

[Alex1]

If $21 must reference to row 22 when copied to the next row, just omit the dollar sign in front of it.

[MrProgrammer]

If $21 must reference to row 22 when copied to the next row, just omit the dollar sign in front of it.

LeroyTennison wants row 21 to change to row 22 when the formula is copied a column to the right.

C3: =B3*($C$21+$K$21)/100+$B$21+$J$21+IF(B3<$E$21;$D$21;0)+IF(AND(B3>$G$21;B3<$I$21);-$F$21;0)+IF(B3>$I$21;-$H$21;0)
D3: =B3*($C$22+$K$22)/100+$B$22+$J$22+IF(B3<$E$22;$D$22;0)+IF(AND(B3>$G$22;B3<$I$22);-$F$22;0)+IF(B3>$I$22;-$H$22;0)

First, put unique provider names in A21:A29 or at least use placeholders A through I. Then, in your formulas instead of:
• $C$n use VLOOKUP($C$2;$A$21:$K$29;COLUMN($C$2);0)    $C$2 in VLOOKUP not $C$n !
• $K$n use VLOOKUP($K$2;$A$21:$K$29;COLUMN($K$2);0)    $K$2 in VLOOKUP not $K$n !
• etc.

VLOOKUP uses the providers in row 2 to get the data from the correct row as you go across the columns. If you need any additional assistance attach a spreadsheet demonstrating the difficulty you're having with VLOOKUP (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach your VLOOKUP sheet.

[Tutorial] VLOOKUP questions and answers

The resulting formula will be long and difficult to read so consider using Insert → Names → Define with names defined for E_Base, E_CentsKWH, E_LowFee, … which contain appropriate VLOOKUP formulas. Read about defined names in Help → Index or in User Guides (PDF) or searching for topics about that in the Calc Forum. You'll wind up with something like:
=B3*(E_CentsKWH+D_CentsKWH)/100+E_Base+D_Base+…

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Alex1]

LeroyTennison wants row 21 to change to row 22 when the formula is copied a column to the right.

You're right. It was late when I replied. Formulas will be much simpler with one of the tables transposed.

[LeroyTennison]

Thank you, VLOOKUP looks very promising, not getting the expected results using your replacement but I'm going to investigate further before replying fully.

[LeroyTennison]

After a lot of VLOOKUPs, it works, thanks again.