Lookup, Offset & Dymanic Name help

K

kenbquik

I have a spreadsheet that I will be updating monthly. Worksheet #1, I have
the columns monthly (Jan'08,Feb'08,Mar.....Feb'10) and 11 rows that contains
data (north,south…) for different entities. Further down the spreadsheet,
the data is summed together. Worksheet#2 consists of a table containing 12
months of data and a line graph that is fed from Worksheet#1.
I’d like to be able to enter the new data and have the table take the last
column on the right that contains data for the last month say Feb ’09 update
the table (last column) for month and ignore the remaining columns (Mar
’09….Feb ‘10) until that data is available. In this example the columns(Mar
’09….Feb ‘10) sum to zero. I’m not sure if I should use, lookup, offset or a
dynamic range name. Also, when I tried to using the lookup function, my last
column contained zeros(Feb ’09) and the lookup went to the month prior(Jan
’09) giving me the incorrect total for the month of Feb. ’09. Please help.
Thanks.
 
S

Shane Devenshire

Hi,

I'm not following your question you say you want to "I’d like to be able to
enter the new data and have the table take the last column on the right that
contains data for the last month say Feb ’09 update the table" So you want
the table to update the table?

Excluding this confusion, you probably need a dynamic range name if this
table is layed out horizontally. Best would be if you told us the version of
Excel and showed us a sample of the data layout.

Generally dynamic range names use something like:

=OFFSET($A$1,,,,COUNTA($A$1:$M$1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top