CODEWITHSUNDEEP
excelexcel-formula
activexplor3
activexplor3

Reputation: 71

Using CONCAT formula after reference to another sheet in same workbook

I'm trying to get the cell value from another sheet in the same workbook, but instead of being able to do =US!A3, I need to be able to make the A3 dynamic. Ideally, I'd like to do =US!CONCATENATE(colname," ",rownum)) where rownum would increase by 1 when I pull the formula down. colname is a vlookup formula. Can anyone help? Thanks in advance!

Upvotes: 0

Views: 1608

Answers (2)

Scott Craner
Scott Craner

Reputation: 152605

Use INDIRECT:

=INDIRECT("US!" & colname & rownum)

To get the row number as dynamin we can replace the rownum with ROW() which will return the row number in which the formula is placed.

Upvotes: 2

Mark S.
Mark S.

Reputation: 2596

It's not clear what you're trying to do here, but you need to adjust how you write your formula. First you need to start with the formula, not the sheet reference.

If you look below you will see I start the formula, and select the cell A1, since this is not $A$1, it will drag down as I drag it down. Additionally it will drag over as I drag it over. I then put the space you had and this is where your question becomes unclear. I'm unsure what you're trying to combine so I defaulted to cell B1.

=CONCAT(Sheet1!A1," ",Sheet1!B1)

Upvotes: 0

Related Questions