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
MArk
Jul 17 2019 08:46 AM
Can you post a copy of your workbook?
Jul 18 2019 06:19 AM
Thanks for getting back and sorry for delay. Here is a copy of the Income Sheet, I'll post the statment copies separately.
Mark
Jul 18 2019 06:19 AM
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
Mark
Jul 18 2019 07:29 AM
Hi Mark
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
Jul 18 2019 07:41 AM
Hey, that's brilliant, thank you very much, just what I was hoping could be achieved.
Mark
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
Substituted with
=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.
Thanks
Mark
Jan 20 2021 09:27 AM
Jan 22 2021 06:35 AM
Thank you, I amended the formula as suggested, but it hasn't altered the #VALUE! return.
Mark
Jan 22 2021 07:00 AM
Jan 22 2021 01:25 PM
Sorry, I missed that you use column letter, not column number. In this case as variant it could be
=INDIRECT("IEmaster2021!" & 'FREEHOLDER RENT STATEMENT'!$B$1 & "41")
Jan 26 2021 02:22 AM
Great, thank you very much, that appears to be working as required.
Kind regards
Mark Butler