Forum Discussion
Use a formula to designate a cell in another worksheet
I'm working with Excel for Mac.
I have a worksheet named "Subjects" and in "Subjects" I want to use a formula to designate a cell in another worksheet ("Items").
The cell I want to reference in "Items" is always in Column A. In "Subjects" I have a cell (C5) where I can enter a row number, for example "2". Let's call that row value "#" for convenience.
I want to use the value "#" (from C5) to designate the row of a cell in column A of "Items" so I can so I can copy the value in "Items" cell A# into cell A10 of "Subjects."
So in cell A10 of Subjects, I have entered the formula "=Items!"A"&C5. I get an error message "There is a problem with this formula." I have tried the formula "=Items!("A"&C5) and I get the same error. I have tried formatting cell C5 as either text or number and the same error message either way.
What is the correct formula or do I need to use a different approach?
Thank you.
It appears that you are trying to use a combination of text and cell references in your formula to reference a cell in another worksheet. You are on the right track, but the formula needs a slight adjustment. In Excel, you can use the INDIRECT function to create a cell reference based on text.
The INDIRECT function is available in Excel for both Windows and Mac, and it works similarly in both versions.
Here is the correct formula to reference a cell in the "Items" worksheet using the value in cell C5 of the "Subjects" worksheet:
=INDIRECT("Items!A" & C5)
Here is how it works:
- "Items!A" specifies the sheet name ("Items") and the column letter ("A").
- & is used to concatenate (join) the sheet name and column letter with the value in cell C5.
- C5 contains the row number you want to reference.
So, if cell C5 in the "Subjects" worksheet contains the value "2," this formula will create a reference to cell A2 in the "Items" worksheet.
Make sure to enter this formula in cell A10 of the "Subjects" worksheet. It should display the value from the corresponding cell in column A of the "Items" worksheet. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
5 Replies
=INDEX(Items!A:A,C5)- Thanks. This works!
It appears that you are trying to use a combination of text and cell references in your formula to reference a cell in another worksheet. You are on the right track, but the formula needs a slight adjustment. In Excel, you can use the INDIRECT function to create a cell reference based on text.
The INDIRECT function is available in Excel for both Windows and Mac, and it works similarly in both versions.
Here is the correct formula to reference a cell in the "Items" worksheet using the value in cell C5 of the "Subjects" worksheet:
=INDIRECT("Items!A" & C5)
Here is how it works:
- "Items!A" specifies the sheet name ("Items") and the column letter ("A").
- & is used to concatenate (join) the sheet name and column letter with the value in cell C5.
- C5 contains the row number you want to reference.
So, if cell C5 in the "Subjects" worksheet contains the value "2," this formula will create a reference to cell A2 in the "Items" worksheet.
Make sure to enter this formula in cell A10 of the "Subjects" worksheet. It should display the value from the corresponding cell in column A of the "Items" worksheet. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
- Super fast response and spot on. Thank you. I appreciate the thorough explanation.
