find data in multiple worksheets

  • Thread starter Thread starter Michael Psarras
  • Start date Start date
M

Michael Psarras

First of all thank you for replying to my e-mail. As per
your request I will give you more details on what I want
to do.
Every day I pass the rates of currencies in a worksheet
and then I rename the worksheet tug giving that day's
date. So I have workbook with 30 or so (up to now)
worksheets with currencies and currencies' rates, and
every worksheet tug is renamed to i.e. 2-11-03, 3-11-03,
4-11-03 and so on.
When I want to find the mins or max value of a currency I
use the simple function i.e. =max(2-11-03:30-11-03!B1)
where B3 is the carrency rate I want to find. (Note that
the data is stored in exactly the same cells in every
worksheet).
For example:

A B
1 USD 1,1697
2 GBP 0,6124


On every worksheet in cell B1 I put the rate of USD.

In this way I get the max or min value of i.e. USD rate
but I cannot find in which worksheet it is located. And
as I have renamed the tugs of the worksheets in dates, it
will be very very helpfull to also see WHEN the rate of
USD reached its max or min value.

I hope all above is clear and I would appreciate if you
could find me a solution.
As I said in my first e-mail, I don't want to use
find&replace. I need a function...

THANKS AGAIN
 
Michael Psarras said:
First of all thank you for replying to my e-mail. As per
your request I will give you more details on what I want
to do.
Every day I pass the rates of currencies in a worksheet
and then I rename the worksheet tug giving that day's
date. So I have workbook with 30 or so (up to now)
worksheets with currencies and currencies' rates, and
every worksheet tug is renamed to i.e. 2-11-03, 3-11-03,
4-11-03 and so on.
When I want to find the mins or max value of a currency I
use the simple function i.e. =max(2-11-03:30-11-03!B1)
where B3 is the carrency rate I want to find. (Note that
the data is stored in exactly the same cells in every
worksheet).
For example:

A B
1 USD 1,1697
2 GBP 0,6124


On every worksheet in cell B1 I put the rate of USD.

In this way I get the max or min value of i.e. USD rate
but I cannot find in which worksheet it is located. And
as I have renamed the tugs of the worksheets in dates, it
will be very very helpfull to also see WHEN the rate of
USD reached its max or min value.

I hope all above is clear and I would appreciate if you
could find me a solution.
As I said in my first e-mail, I don't want to use
find&replace. I need a function...

THANKS AGAIN
Click to expand...

The problem is the structure of your data. You cannot achieve everything
across different worksheets that you could achieve within a worksheet. Do
you have to put the data on different worksheets, rather than different rows
on one worksheet? If you had dates in column A and the rate for a particular
currency in column B, the problem would be simple:
=INDEX(A1:A30,MATCH(MAX(B1:B30),B1:B30,0))
You could, of course, have other currencies in columns C, D, E... and simply
adapt the formula for each of these. (Making the references to column A
absolute, and copying the formula across columns will do this
automatically).

When designing a spreadsheet, it's terribly important to consider from the
start what you need to achieve, and then choose a data structure to suit.
You get into big problems if you simply start with an arbitrary data
structure and then try to use it!
 
Back
Top