Offsetting cell("address")

ShoYnn

Board Regular
Joined
Mar 20, 2019
Messages
60
Office Version
  1. 2016
Platform
  1. Windows
I have a table. The first column has job names. The second column uses cell("address") to give me the sheet and cell address that the job is found. That all works great. What I would like to do though is add a few rows to the results of the cell("address"), for example instead of the result being sheet2!$D$1 I would like it to return with sheet2!$D$4. Anyone got anything for this?

Here is the original formula I am working with

=Cell("address",Index(Indirect("'"&$O$1&"'!$D$1:$FM$1"),Match(AM3,Indirect("'"&$O$1&"'!$D$1:$FM$1"),0)))

O1 is a cell that contains the sheet name to look in, D1:FM1 are the cells that will contain the value being matched, and AM3 contains the value to match. The returned response is

' FY22 Teams'!$D$1

Which is accurate, but I would like it to return the cell a few rows down


EDIT:
When I try to do a +3 on the index part it adds 3 to the column, but the row stays at 1. Example formula is

=Cell("address",Index(Indirect("'"&$O$1&"'!$D$1:$FM$1"),Match(AM3,Indirect("'"&$O$1&"'!$D$1:$FM$1"),0+3)))

Response is now

'FY22 Teams'!$G$1

Where I want to add the 3 to the rows and keep the column the same
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Scratch what I just had your match is not looking at rows. Let me get back to you when I am at my computer
 
Upvote 0
Scratch what I just had your match is not looking at rows. Let me get back to you when I am at my computer
I will try this tomorrow when I am back at work and let you know! Thanks friend!

Lol, I replied to your response before your edit!

Also, I noticed I typed the formula wrong, the +3 should have one ) infront and two after, not be right after the 0
 
Upvote 0
Sorry I am not back at my computer yet but your Range part of your index only contains one row. So your RowNo position is being used to find the column. I think you need to have something more like this.
Excel Formula:
=Cell("address",Index(Indirect("'"&$O$1&"'!$D$1:$FM$20"), 3,Match(AM3,Indirect("'"&$O$1&"'!$D$1:$FM$1"),0)))
 
Upvote 0
OK I can confirm that the above is substantially correct.
You need to specify a range of rows in your nominated range (in your first Indirect function), I have used 20 but see what you think will cover how many rows you are likely to want to offset by or whether you also want to do a Match in the row position.

I used 3 in the above but you want the 1st row + 3 so you will actually need to put 4.
If you used a match for the row position as well it would be + 3.

Excel Formula:
=CELL("address",INDEX(INDIRECT("'"&$O$1&"'!$D$1:$FM$20"), 4,MATCH(AM3,INDIRECT("'"&$O$1&"'!$D$1:$FM$1"),0)))
 
Upvote 0
Solution
OK I can confirm that the above is substantially correct.
You need to specify a range of rows in your nominated range (in your first Indirect function), I have used 20 but see what you think will cover how many rows you are likely to want to offset by or whether you also want to do a Match in the row position.

I used 3 in the above but you want the 1st row + 3 so you will actually need to put 4.
If you used a match for the row position as well it would be + 3.

Excel Formula:
=CELL("address",INDEX(INDIRECT("'"&$O$1&"'!$D$1:$FM$20"), 4,MATCH(AM3,INDIRECT("'"&$O$1&"'!$D$1:$FM$1"),0)))
Thanks a bunch! I will try this out tomorrow and let you know how it goes!
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Offset Cell("address") - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
OK I can confirm that the above is substantially correct.
You need to specify a range of rows in your nominated range (in your first Indirect function), I have used 20 but see what you think will cover how many rows you are likely to want to offset by or whether you also want to do a Match in the row position.

I used 3 in the above but you want the 1st row + 3 so you will actually need to put 4.
If you used a match for the row position as well it would be + 3.

Excel Formula:
=CELL("address",INDEX(INDIRECT("'"&$O$1&"'!$D$1:$FM$20"), 4,MATCH(AM3,INDIRECT("'"&$O$1&"'!$D$1:$FM$1"),0)))
Alex, I just tried this and it works as desired! Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top