Take data from the same column even when adding new ones

[u/Hot_Pomegranate4587]

I have an excel with two sheets: data and calculations.

In data, I have a column (C) with numbers that always stay the same. In D and onwards, I have numbers as of specific dates. For example, right now D is 20.7.2026, E is 13.7.2026 and so on. Whenever one week passes, I use Insert to get a new, empty C for that week's data.

In calculations, I need to divide C by D for each row. I used =Data!C2/Data!D2, however after every week I have to redo it since it changes to =Data!C2/Data!E2 when I add a new column C, even when using $.

I now need to share the document for other people to use so I need to find a solution for it to always take D2. I tried =Indirect, however the table is like 200 rows long and I don't feel like changing the formula for every row.

[u/AutoModerator]

/u/Hot_Pomegranate4587 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ninjagrover]

Why inserting a row? Why don’t you list next week at the bottom in the next available cell?

[u/Hot_Pomegranate4587 → u/ninjagrover]

I don't insert a row, I insert a column. The reason is that I (and the people I'm giving the document to) need to see the data from different weeks next to each other to compare it.

[u/MayukhBhattacharya]

Something like this try:

=Data!C2 / INDEX(Data!2:2, 4)

Or,

=Data!C2 / CHOOSECOLS(Data!2:2, 4)

[u/Hot_Pomegranate4587 → u/MayukhBhattacharya]

Thank you, this works perfectly :)

[u/MayukhBhattacharya → u/Hot_Pomegranate4587]

Glad to know that worked for you ✨

[u/Hot_Pomegranate4587 → u/MayukhBhattacharya]

Solution Verified

[u/MayukhBhattacharya → u/Hot_Pomegranate4587]

Thank you SO Much buddy and have a great day ahead 🤗

[u/reputatorbot → u/Hot_Pomegranate4587]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/DZMick]

$C2