LinkBack URL
About LinkBacks
If I have the text of a column in a cell. For example, A5 (in cell A1). How can I use the offset formula to change than value. I tried referencing Offset(A1,,1) but it returns a #value error.
If I have the text of a column in a cell. For example, A5 (in cell A1). How can I use the offset formula to change than value. I tried referencing Offset(A1,,1) but it returns a #value error.
the 1st ref in OFFSET tells excel where to start from (where the offset starts from)
then how many rows to go down
then how many columns to go down.
If you have A5 as the reference you want to get info from, then you need to specify how rows to go down and how many columns to go across...
=OFFSET(Star-Ref,rows, columns)
You generally use values for the rows and column arguments, but a cell-ref can be used as well...
b5 abc cba abc cba
B1=OFFSET(A1,RIGHT(A1,1)-1,0) This would be used when the column is the same (I changed it to B for the next example)
B2=OFFSET(A1,RIGHT(A1,1)-1,CODE(LEFT(A1))-97)
However, this seems a pretty long way around getting contents of a cell in a known location? Perhaps consider INDIRECT()?
=INDIRECT(A1) = cba
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I'm going to work through your examples but I'm not sure I explained myself correctly. Spreadsheet attached.
You haven't provided very much information here. The formula is doing what you told it to do..
=OFFSET (C$... start in cell C4 - which contains AM)
,, (stay in the same row)
C2-1 (subtract 1 from the cvalue in C2 - 2 - which gives 1 and move that number of columns to the right of your startng point. That gives you E: the contents of D4, one row to the right of your starting point.
What did you want as the answer... and why?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
The answers are in row 3. Based on the value in C2, change the column names in C3:E3 by that number minus 1. I have a spreadsheet that manually references months in another sheet, so when the month changes, I want to find and replace the column values. All the values in row 3 are for January. Maybe offset isn't the right formula.
How about using INDIRECT()?
aa bb AL D G AM E H E aa bb
C6=INDIRECT(C3&$C$2)
copied across
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks