Absolute worksheet reference in excel cell formulas

0 votes

I have a list in Sheet3 that is filled out with data from a Sheet1 column. To do this, it just uses standard cell referencing. When the work in a row is finished, I sometimes move full rows from Sheet1 to Sheet2. As a result, after moving something to Sheet 2, I no longer want that item to be listed in Sheet 3's list. However, Excel automatically switches the reference from Sheet1 to Sheet2 in the calculation in Sheet3 when I move the cells from Sheet1 to Sheet2, defeating my goal.

How can I utilize an absolute reference in my spreadsheet so that formulas don't change when cells are moved? I'm aware that Libreoffice has a feature where the sheet name can just be prefixed with the $ sign. Excel, however, doesn't support that.

My formula in row 1 column 1 of Sheet3 is:

=Sheet1!C6
Oct 10, 2022 in Others by Kithuzzz
• 38,020 points
528 views

1 answer to this question.

0 votes

Try this:

=INDIRECT("Sheet1!C"&ROW())
answered Oct 10, 2022 by narikkadan
• 63,680 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,573 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
533 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,680 points
3,652 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,680 points
1,976 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,806 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,935 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
950 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,571 views
0 votes
1 answer

How to express absolute cell references in Excel R1C1-style formulas?

If the formulas are in Column K ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,680 points
987 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP