Jul 17 2019 08:12 AM
Jul 17 2019 08:12 AM
We have a spreadsheet "INCOME" - customers (Rows) and their monthly payments (columns). I would like to produce a statement using another sheet "STATEMENT" which picks up from the INCOME sheet the customer e.g A1 or A2 and their payments e.g B2:B13 (Jan - Dec). I can do this manually for each customer entering the rows and columns (using the = sign to copy the information from the INCOME sheet e.g.=INCOMEA1). But I think/hope I should be able to make this more of an automated process, can the row letter be picked up from a cell somewhere on the STATEMENT sheet so that It will automatic all pick up whichever Customer and their payments I choose???
Thanks in advance
Jul 18 2019 06:22 AM
Copy of Statement showing formulas - The INCOME spreadsheet is called MASTER for info.
I'd like to be able to enter the Row number somewhere on the STATEMENT spreadsheet 8, 9, or 10 being the row for that customer - and produce a statement for each customer.
again Thanks in advance
Jul 18 2019 07:29 AM
See attached workbook.
I haven't set it all up, just sufficient to show you how you can enter the Row number for the Customer, and the Index formula will bring you back all the relevant data for them to your Statement sheet.
You can copy the relevant formulae to your workbook and all should work fine.
Hope this helps
Jan 20 2021 07:30 AM
The statements have been working well and we now use them a lot.
I would like to be able to set a new type of statement using the information from a column as opposed to a row as previously requested (and sorted out).
I have tried to substitute the column letter where the row number is used the formula but i am getting a #VALUE! response.
=INDEX(IEmaster2021!I:I,'SC BASIC STATEMENT'!$B$2) - I being the column to collect the row content from - $B$2 being the cell where the row number is entered
=INDEX(IEmaster2021!41:41,'FREEHOLDER RENT STATEMENT'!$B$1) - 41 being the row to collect the column content from - $B$1 being the cell where the column letter is entered.
Any help you can give would be appreciated.
Jan 26 2021 02:22 AM
Great, thank you very much, that appears to be working as required.