offset from current cell

M

ManiacMark

New Member
Joined
Apr 12, 2008
Messages
4
Hello,

First time poster. I've been searching for a way to do this for a couple of days now.

I am basically writing a perl script to scrape data off of a report text file to a new text file that is formatted for excel. This data is then imported into excel and i want to insert functions into the text file so the user does not have to put the functions manually into the thousands of rows.

The problem is that the the data can be imported anywhere into an existing spreadsheet so my formula can not use standard cell address's. For example i need a formula that says =hour(cell to my right) or =text(cell to my right, "ddd").

I have tried things like =text(offset(cell("address"),,1),"ddd") but it looks like the offset function can not use a function to return the address. Putting just cell("address") into any cell returns the current cell address so i don't know why it can not be used.

All ideas appreciated.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Hello Mark, welcome to MrExcel,

Address function will give a text string not a cell reference, try

=TEXT(OFFSET($A$1,ROW()-1,COLUMN()),"ddd")

...or perhaps better because it doesn't need any cell references

=TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()+1)),"ddd")
 
Upvote 0
Instead of
Code: 
offset(cell("address"),,1)
use
Code: 
OFFSET(INDIRECT(CELL("address")),,1)

However, that may still not get you the results you want, because the CELL("address") function without a reference returns the address of the last cell modified in the worksheet, not the address of the cell containing the CELL formula. I'm not sure what to do about it though.
 
Upvote 0
Got it. The ROW and COLUMN functions, when used without reference, do self-refer.
Code: 
=OFFSET(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE),,1)
 
Upvote 0
Good stuff guys, Thanks for all the quick replies.

I ended up going with Barry's 2nd option.

Oh and Mike, yeah the R1C1 format was tried but in the end i think it would have confused the end user trying to switch to this format.

Thanks again, you'll probably be hearing a lot more from me, this is a great forum.(y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
SEARCHING ANOTHER WORKBOOK FOR CURRENT ACTIVE TEXT OFFSET AND RETURNING TEXT FROM 3 CELLS OFFSET FROM FOUND TEXT
2
Replies
11
Views
120
BAKELOVEMORE
B
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
85
ipbr21054
I
D
  • Question
Excel Offset function
2
Replies
12
Views
511
Alex Blakenburg
A
Jeffrey Mahoney
  • Question
Row of Last non-blank cell in column Dynamic Named Range
Replies
7
Views
134
Cubist
Cubist
most
  • Question
Trying to figure out why my scripts creates hidden and randomly values
Replies
8
Views
84
Alex Blakenburg
A

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

Share this page

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