Thread: Diagonal updation required

I need to update 45 cells in the diagonal manner as shown in the attached pic. Every updation requires that one cell below each column gets filled with an integer value.Currently, we draw excel pivots and replace all the values for all the rows, but we intend to move to a system where only one diagonal set of values needs to be updated.This needs to be updated every month, hence a long term solution is needed which minimizes manual work
Please advice on the following:
1)Can I create a named set of (say 1000) cells starting from 10 cels below the first non empty cel in each column? I would then require a formula which checks for the first non zero value in this set and apply this formula to the destination cel (the uppermost non zero cel?)
I would then paste one diagonal every time and the report would automatically pick up the cels. Please note that the next time it would have to take the 2nd non zero value and so on......
2)Is there any way to drag a formulae diagonally? I would then not need any of the above as I can simply refer to the pivot and drag
3) Any other ideas.

Any help would be appreciated-

R B G

I went through the post involving replacing all diagonal values with 0.. but could not customize it for this situation ... Help!

Perhaps attaching an example spreadsheet might get more response, as I'm finding it difficult to understand exactly what you mean.

PFA excel sheet with Key and explanations.

Original-Sample sheet requires diagonally pasted values from Pivot Sample sheet

Thing is I have 15 tabs and 4 such files to take care off so its too cumbersome right now

I, for one, still need a better explanation.

1) do you want it to auto-populate by each day based upon today's date? If so, then some sort of IF(cell<=TODAY()) would be used.

2) Your sheet says it copies from 'Sheet 2'. There is no other sheet 2.

3) 'Copy' is too vague. If it is just a copy from another sheet, then you can just use =Sheet2!B5 (or whatever approrpriate cell it is), but I have a feeling there is some needed logic in your copying.

To answer your questions in your initial post:
1) People can get pretty creative with dynamic named ranges, but there is no formula that will put a formula in a new cell. VBA is likely needed for that.
2) I know of no method to drag a formula diagonally, but there is usually a way to create a formula that leaves cells blank and the pattern would be diagonal.
3) Other ideas would involve a better explanation of your issue. Provide a few more sheets, like a before and after, then a sheet with the source data (i.e. Sheet2 in your example), and some sort of logic on when you update the fields and how they copied from Sheet2.

Pauley

Pauley,

Thanks for the reply. For some reason,I was blocked from the forum and could not post for the last few days.

Anyway PFA an excel file with Before , After,Generated -1 , Generated -2 sheets. The before sheet details the present state of the report. The After sheet details the way updation is required. Genertated -1 is exported from SQL and will be recurring. Also, Generated-2 represents the future updation.

Since I will be exporting from SQL - the formats of the dates may not match. My copy paste logic hence, may not exploit a vlookup (and even if I could - since I cannot drag formulae, whats the point?)

Now the part where all this becomes annoying is that I have 40 such tabs to update making any solution involving formulae for each cel as too tiresome (since we have now established that it is not possible to drag forumale).

Can I explore a macro solution? Is that the only way out?