Replies

I thought about this too, but unfortunately it wouldn’t work as the person wants explicitely NA instead of -100, and not an ’empty’ cell.
But thanks anyway for your kind post.
Any oder ideas maybe?
Regards,
K.

Hey, thanks a lot for the file, that has everything and even more than I’d hoped to get!

Cheers m8,
K.

Thank you very much, it is EXACTLY what I wanted.

Cheers,
K.

Well, I am not sure how you actually did in the formula, as much as I try to understand it – I can’t, but it does work!. I added an extra column, filled it with 0, and in the formula I selected it as well (ex. instead of A3:L3 I took now A3:M3).
Thanks a lot for making the impossible possible.

K.

Ok, try not to think of the descriptions of the months as such, but rather as any descriptions. In our company we are mainly working with bi-monthly data, less with monthly data. We are pulling the data from a databank created specially for our needs. Therefore when I say Past 3, I mean past 3 bi-monthly periods, not the past 3 physical periods, i.e. if we are now in the MJ01 period (May-June data collection), then I want the data to be cummulated from JF01 (i.e. 3 cells starting from JF01). If we are the next time in JA01, I want it to be summed up from MA01 onwards, always three columns. I am not sure how clear is this concept, that is why I simplified it in my first post, so I wouldn’t have to explain all this, as not many people are used to bimonthly data retrieval.
I kind of believe that what I want is not possible, unless there is a way to tell in the formula to sum up the last three cells where there is data but > 0, since the 0 is because the data is linked to an empty cell in the other worksheet.
Am I confusing enough?

Regards,
K.

Thanks a lot for your help. It does work, however, due to my omitting of some things, it works only halfways. I simply didn’t realise you would use the month-now combination.
What actually happens, is that we have also so-called bi-monthly data, for which the description of the months (in thr row 1) look like this: JF01, MA01, MJ01, JA01, SO01, ND01. Now here your formula will not work, as we are not speaking of real months in each columns.
Is there any possibility to modify the formula to work with these labels for the periods as well?
I appologise again for not mentioning this before, I feel really bad about it.

Thanks,
K.

One thing I found it works is in the Tools/Options in the Transition to turn off the Transition Formula Entry.

I am working with Excel 97 though, so these Options in Excel 2000 might be in a slightly different location.

Regards,
K.

Thanks a lot, it worked.
I was almost to give up, I considered the file corrupted.
Today I learned another thing.

Thanks again.
K.

Ok, thanks for your quick reply and confirmation…I am heading now to the store to buy a new disk…

Cheers,
Kislany

Hi there,

I had the same problem as you with the Offset function (but not with external links), and along the line I realised that if I clicked on the relevant cell, pressed F2 and ‘Enter’, the cell would update. Luckily I didn’t have many cells like that, so I started the macro recorder, did the F2-Enter key combination for all the involved cells and then I stopped the macro recorder. I placed the resulting code in ‘Thisworkbook’, and now every time I open the file, it updates the macro, i.e. the Offset function in the file.
You could also put it in a module and add a small button and attach the code to it,if you don’t want it to update every time you open the file…just a thought…
I know it’s not the best solution, but it does work and in lack of anything better…Btw, I posted the question in the MSExcel forum a couple of months ago, and nobody had any help for me there.