how to reference a variable value (not the best of descriptions)

[Princy557]

Hi
I have a couple of worksheets one sheet creates a set of values and a graph. Contains, date, calculated values, reading from meters etc.
A second worksheet has a date column and a tariff column. The value of the tariff can vary on a daily basis and is entered as required by the user. The entry is added each time so there is a historic value available.
How do I do a calculation on worksheet 1 using the 'latest' values on worksheet 2?

[SuperTech]

It actually depends on what calculations you want to do. Sharing complete details with sample files will be more helpful in this case.

[Princy557]

Hi,
Well basically the only calculation detail would be a tariff, then the other cells would work of the results of that.
I have solar panels and get a feed-in tariff from the gas company. I just want to be able to estimate to a closer level how much will be paid to me after the readings are taken. Reading are done weekly but payment every 90 days.
It's not essential that I have this working, but it's been an annoyance for some time that I don't know how I would do it.
I'm struggling to keep my eyes open, will get more detail in the morning.

[Princy557]

Hi
Currently, I actually add the information to the tariff page and note it. Then make amendments to the formulae's every time I do a calculation or FIT entry. (FIT- Feed-In Tariff)
It would all become a much neater page if I could just reference the tariff page, either the entry by date or by the last entry.

Just thoughts,
Any input would help my knowledge

[SuperTech]

At least please share the formula before and after modification.

[Princy557]

Sorry,

The amount due is calculate with E47 =(D47*(17.87/100))+((D47/2)*(5.38/100))

Values are taken from tariff worksheet and hand-entered
26/07/2019 £0.1787 £0.0538 £0.2325
15/01/2020 £0.1787 £0.0538 £0.2325 <- this in example above
01/07/2020 £0.1826 £0.0550 £0.2376
04/10/2021 £0.1848 £0.0557 £0.2405

Hope that makes sense and there is now enough information given

[SuperTech]

If I understand your situation correctly, you are getting one data on one file with "£" sign which you have to type in another file in number format for calculation manually.

Here is the stepwise solution for you:
1. Copy the source data cell from the source file.
2. In the target file use paste special > Paste reference.
3. You will get the reference of the source cell, on the bases of which you can type the final formula like this one which I wrote for you with my sample files.
4. After writing the formula for one cell, you can simply drag the formula for the other cells of the column as per your requirements.

For cell E1

Code: Select all

=(D1*SUBSTITUTE('[Source file.pmdx]Sheet1'!B1,"£",""))+((D1/2)*(SUBSTITUTE('[Source file.pmdx]Sheet1'!B1,"£","")))

Please let me know if you have any confusion.

Sample files are attached here:

Source file.pmdx

(5.98 KiB) Downloaded 513 times

Final file.pmdx

(6.44 KiB) Downloaded 538 times

[Princy557]

Hi SuperTech,
Thanks for taking the time.
I've just ditched about fifty worksheets which were all messing around by me NOT getting anywhere. So we are left with

'Graph' where the formulae are and 'Tariff' where the incremental data is updated. Maybe this is a clearer way of showing what I'm trying to do.

Tariff is where I add various changes as prices happen. What I think I need to do is to (in formula ways) point at the last entry to get the combined Generation + collection values Coumnsl B and C, or use the single value in column D.
'Graph' needs to use B & C or D as the value in the formulae.

Is that a better explanation?

Attached are the worksheets

P.S. I'm working through your formula, struggling a bit, but on it. I have a Best Before Date of about 2015. It's been downhill since then. ha ha

[SuperTech]

Thanks for sharing the file and details. I have added the formula for you and for better performance, I have made some changes in your file.

Tariff is limited to 500 rows, which you can increase as per your requirements later.

FIT TEST calculations.pmd

(36.5 KiB) Downloaded 534 times

[Princy557]

Hi SuperTech
Thank you so much, I'll spend some time this afternoon and see if I can learn how you've done it.
But it certainly looks good.

[Princy557]

Hi SuperTech,
I think I've noticed a problem.
When the formulae are used the next time, the tariff has changed, all the values above change to the newest tariff

Tariff
01/07/2020 £0.1826 £0.0550 £0.2376
04/10/2021 £0.1848 £0.0557 £0.2405
added tariff
18/01/2022 £0.1878 £0.0559

Graph should be:
16/01/2022 16105 18 £3.83 **
18/01/2022 16108 3 £0.64

but comes out changing the previous value
16/01/2022 16105 18 £3.88 **
18/01/2022 16108 3 £0.65

Is there a way of fixing in stone, all previous values?

My first thought is that the Tariff value will have to be copied to the Graph sheet or the Tariff sheet is superfluous and the tariff would become part of the Graph sheet.

Any thoughts?
(other than flipping bloke!)

[SuperTech]

I have just added the formula so that you can get the latest tariff values in graph sheet as per your requirements. No other change.

Here are the values I am getting after adding your latest tariff values.

[Princy557]

HI,
Was there supposed to be an attachment?

[SuperTech]

Do you want attachment also? I used the same file which I shared last time and just entered one new entry in tariff. No other change.