I am trying to figure out how to retrieve data from various sheets
that have the same format. I have multiple sheets to which I add a
row of data every week. In particular I want to create a formula that
goes to a number of worksheets, finds the last row which includes
data and form that row retrieves the data in a given cell of that
row. Your help is highly appreciated.
Simplest way I can think of off the top of my head is to define a
dynamic named range on each sheet and use that with the INDEX()
function...
=INDEX('Sheet2'!LastRow,5)
...where 'LastRow' is the name of the dynamic range defined with local
(sheet level) scope on every sheet you want to pull from. The 2nd arg
is the cell index. Then you can set up your summary sheet something
like this:
Cols used: A~B~C, starting in row2
Data used...
Sheet2~5~=INDEX("'"&$A2&"'!LastRow"&$B2,$C2)
Sheet3~5~=INDEX("'"&$A3&"'!LastRow"&$B3,$C3)
Sheet5~7~=INDEX("'"&$A4&"'!LastRow"&$B4,$C4)
Sheet7~8~=INDEX("'"&$A5&"'!LastRow"&$B5,$C5)
...and so on
ColA contains the sheetnames of sheets you want to pull from. ColB
contains the cell index (or column#) in the range.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
