Hi Mynda,
I have an extract of Statement of Accounts from an application which downloads each Client account code into multiple sheets. I have attached the sample file and would like to know how to extract certain cell reference from each Sheet.
The End result is per Sheet11 of the file.
Thank you and hope to hear from you soon.
Hello
Indirect will let yoiu make the cell from the worksheet name and text
=INDIRECT(A24&"!P3")
Then in the grid you can have a match to find the correct row, and index + Indirect to pull the data
=INDEX(INDIRECT($A24&"!a:a"),$M24)
The one thing you MUST to is to get rid of merged cells - the above will not work with merged cells and they are without doubt the worse thing you can use on a spreadsheet. Try 'Centre Accross Selection' from the formatting menu as it does a simular job but is far less destructive
Thank you very much for your response. very much appreciated.
The solution is very helpful.
P/s: The merge cells are actually from the extract from the source the application.
Fair enough, i have that at work as well and it drives me mad.
As long as you only need the data somthing lilke this might help
Sub MergeKiller()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("a:u").UnMerge
Next ws
End Sub
Hahaha... the number of sheets are > 500 sheets.
Thank you anyway for your advice and tips. Will try to look into it.
Hi Nazrin
Give this a try.
No idea how it will perform on 500 sheets as it uses a lot of INDIRECT functions.
Good luck.
Sunny
Hi again,
Getting specific cell reference from multiple sheets in same workbook is now working fine for me.
New similar question:
How do i now get specific cell reference from multiple sheets from a different Excel Workbook (file).
eg.
- I have WB1 and WB2
- WB1 is the master file
- I need to get specific Cell reference from multiple sheets in WB2 into WB1.
Can we still use MATCH & INDIRECT and INDEX? If so, how? I can seem to get it.
Thank you and hope you are able to assist.
