Is there a formula I can use in Excel Sheet B, to get the value of a cell in Sheet A, even when I add a row to Sheet A (that is, the cell in Sheet A changes row)?

Question

I have two Excel sheets.

 

The first increases in a row each working day and it holds prices for shares that change from day to day.

 

The second makes calculation based on those prices.

 

I have tried putting in one of the second sheet price, the formula “= <the cell in the first sheet that holds the corresponding price>”, but it does not do the job, because when I add a row in the first sheet, it does not change the row to take the price from.

 

So, I have been doing it manually each time I need to do the calculations.

 

Is there any formula that can allow me to get the right price even when I add new rows in the first sheet?

Answer 1

Hello

Not sure what kind of caculation you do, but did you try pivot table instead? That might work beautifully too.

Otherwise, convert Sheet A to an Excel Table and use structured references..

To do so , click anywhere in Sheet A data. Go to Insert > Table (check “My table has headers”). Give the table a name like, such as "MyTable".

Then formulas won’t break when you insert rows, and references expand automatically.

For examples, if you want the price where Date = B2 and Ticker = A2, you can use formula and references like this:

=XLOOKUP(A2&B2, MyTable[Ticker]&MyTable[Date], MyTable[Price])

Answer 2

To achieve the functionality you want in Excel, you can use the INDEX and MATCH functions together. This combination allows you to dynamically reference a cell in Sheet A, even if rows are added or removed. Here’s how you can set it up:

1. Assuming:
   • Your prices are in column B of Sheet A.
   • You want to reference a price based on a corresponding identifier (like a stock name) in column A of Sheet A.
   • You have the identifier in Sheet B that you want to match with Sheet A.
2. Formula: In Sheet B, you can use the following formula:

      =INDEX('Sheet A'!B:B, MATCH(A1, 'Sheet A'!A:A, 0))
   

   • Replace A1 with the cell in Sheet B that contains the identifier you want to match.
   • This formula will find the row number of the identifier in column A of Sheet A and return the corresponding price from column B.

This way, even if you add or remove rows in Sheet A, the formula will still correctly reference the price associated with the identifier you are looking for in Sheet B.

---

References:

• How to use defined names to automatically update a chart range in Excel
• Excel performance: Tips for optimizing performance obstructions