=INDIRECT( ADDRESS( ROW(), ( COLUMN() + 1 ),4) )īased partly on this which has a handy formula for transposing to get a column of column letters, incremented each row. For example, if you want to look up the cell to the right of the current cell (so increasing the cell letter by one and keeping the row the same): using indirect(), you can skip the substitute() and just pipe in the row number. So where it says 118(Total Quantity on hand),that is in cell C2. This what part of my spreadsheet looks like.
How often are you updating new product and withdrawalsIt's ment to be a subledger for the three cells you want to show inventory axctivity and balance. Now, in D2 is where I am going to be adding or subtracting quantities, then I want the adjusted amount to populate in cell E2 where it says 'Total Comforts. The sample was for display by day or some period of time. =SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,"")Įxample reading a cell A1 that contains a number to be treated as a column number (e.g. The only difference is, that I want it to both add and subtract within on formula. The only difference is, that I want it to both add and subtract within on formula.
=SUBSTITUTE(ADDRESS(1,( COLUMN(Z1) + 2 ),4),1,"")Įxample reading a cell A1 that contains a letter as text to be treated as a column letter: In this example, it's taking the current column and adding 1, so returning B if it's in column A and AA if it's in column Z.Įxample adding to a fixed column (adding 2 to column Z, returns "AB"): The part in the middle marked in bold is the only part that changes.
0 kommentar(er)
