Hi Again Everyone -
I was wondering if there was an easier way to copy and increase the cell value of a formula? Let me explain – W17 has this formula ='FJ'!$B$318 now in W18 I need 'FJ'!$B$340 and W17 I need 'FJ'!$B$362 and so on. I have about 4000 entries like this to go and sure could use an easier quicker way of getting it done - any help would be great and thanks.
I tried doing an offset but was unsure how to write it so I kept getting an error message.
If this is put in W17 and dragged down, I think it will do what you want.
=INDEX('FJ'!$B:$B, 22*ROW(W18)-78, 1)
OFFSET is another approach
=OFFSET('FJ'!$B$1, 22*ROW(W18)-79, 0, 1, 1)
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
It works - you just saved me millions of hours - Thank you!
Can you explain where you got the 78 from tho? When I move the formula over to another column and change the column from W to Z the formula stops working?
Thanks again Mike - you are a life saver!
The INDEX formula in W17 needs to have 318 as its second argument.
(340-318) * ROW(W18) - x = 318
22 * ROW(W18) - x = 318
22 * 18 - x = 318
x = 78
When copy/pasting that formula from one column to another, be sure that the row pasted to is the same as the one copied from.
Last edited by mikerickson; 05-22-2008 at 02:10 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks