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.
 
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.:beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

sharky12345
  • Solved
Follow Hyperlink address without a physical link
Replies
3
Views
121
jkpieterse
jkpieterse
P
  • Question Question
Fill Cell that includes text in quotes plus data from other cells
Replies
2
Views
75
Alex Blakenburg
A
S
  • Question Question
Offset Help
Replies
1
Views
72
Scrapyard
S
A
  • Question Question
Evaluate cell or have cell refer to itself after being transposed via. lookup functions
Replies
2
Views
113
Sam_D_Ben
Sam_D_Ben
trishcollins
  • Question Question
VBA to return the offset formula used in the data validation source
Replies
1
Views
451
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,226,775
Messages
6,192,932
Members
453,767
Latest member
922aloose

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