-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Help on this please
- Thread starter Jtvining
- Start date
Not to sure what you are after but I will guess.
If you select cells A1 to S10 by highlighting those cells the reference A1 will show in the name box which is directly above the A column and to the left of the formula bar, in that box type any name for the range selected and press enter. Try typing Data
Now you have a range reference (Data) you can go to any time or refer to in a formula. Just click in the name box and select the name you typed and it will take you to A1:S10.
If you wanted to sum all the cells in that named range you would enter ~
=SUM(Data) which is the same as typing =SUM(A1:S10)
The formula must be outside the range named Data or you will get a circular reference warning.
If that is not what you require, can you give a better explanation of your requirements.
If you select cells A1 to S10 by highlighting those cells the reference A1 will show in the name box which is directly above the A column and to the left of the formula bar, in that box type any name for the range selected and press enter. Try typing Data
Now you have a range reference (Data) you can go to any time or refer to in a formula. Just click in the name box and select the name you typed and it will take you to A1:S10.
If you wanted to sum all the cells in that named range you would enter ~
=SUM(Data) which is the same as typing =SUM(A1:S10)
The formula must be outside the range named Data or you will get a circular reference warning.
If that is not what you require, can you give a better explanation of your requirements.
Upvote
0
no im looking to use it as a range.
i want to reference lets say A1:S1, but the 1 would change based on a reference that is retreived by the row function
The reference would then be used in a match function say match(a3,Arow():Srow(),0)
i want to reference lets say A1:S1, but the 1 would change based on a reference that is retreived by the row function
The reference would then be used in a match function say match(a3,Arow():Srow(),0)
Upvote
0
Sorry it has taken so long to get back to you.
Try ~
In cell A1 is the formula with the returned ROW
'The data row you wish to look up
In Cell A3 is the Value or Text you wish to MATCH.
=MATCH($A$3,INDIRECT(""&$A$1&":"&$A$1&A:A&S:S),FALSE)
The first part after INDIRECT looks in cell A1 to find the row of data from which you need to match the value in Cell A3, the next part limits the row of data to look up, only from column A to Column S, and False, produces the exact match position in the row of data you are looking up.
Hope that is what you require.
Sorry I did not twig to what you wanted first up.
Cheers.
Try ~
In cell A1 is the formula with the returned ROW
'The data row you wish to look upIn Cell A3 is the Value or Text you wish to MATCH.
=MATCH($A$3,INDIRECT(""&$A$1&":"&$A$1&A:A&S:S),FALSE)
The first part after INDIRECT looks in cell A1 to find the row of data from which you need to match the value in Cell A3, the next part limits the row of data to look up, only from column A to Column S, and False, produces the exact match position in the row of data you are looking up.
Hope that is what you require.
Sorry I did not twig to what you wanted first up.
Cheers.
Last edited:
Upvote
0
