Excel copy information from another sheet

Copper Contributor

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 

13 Replies

@MarkTemples 

Can you post a copy of your workbook?

@Roger Govier 

Thanks for getting back and sorry for delay. Here is a copy of the Income Sheet, I'll post the statment copies separately.

 

Mark

@Roger Govier 

 

Copy of Statement

@Roger Govier 

 

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

@MarkTemples 

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

 

@Roger Govier 

 

Hey, that's brilliant, thank you very much, just what I was hoping could be achieved.

Mark

 

@MarkTemples 

 

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

@MarkTemples 

That's like

=INDEX(IEmaster2021!41:41, 1, 'FREEHOLDER RENT STATEMENT'!$B$1) 

@Sergei Baklan 

 

Thank you, I amended the formula as suggested, but it hasn't altered the #VALUE! return.

 

Mark

  1. @MarkTemples can you upload a copy of your file. Then we can work out what the problem is.

@MarkTemples 

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")

 

Great, thank you very much, that appears to be working as required.

 

Kind regards

 

Mark Butler 

@MarkTemples , you are welcome