If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
find data in multiple worksheets
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 |
#2
|
|||
|
|||
find data in multiple worksheets
"Michael Psarras" wrote in message
... 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 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! |
Thread Tools | |
Display Modes | |
|
|