Replies

Well, I can’t find a way to make MATCH work across worksheets. (Perversely the MAX/MIN functions do.) The following, somewhat laboured example, works:

=MATCH(MAX(Sheet1!F15,Sheet1!G15,Sheet1!H15,Sheet1!I15,Sheet1!J15,Sheet1!K15,Sheet1!L15),Sheet1!F15:Sheet1!G15:Sheet1!H15:Sheet1!I15:Sheet1!J15:Sheet1!K15:Sheet1!L15,0)

Note all references are still on Sheet1. Any attempt to change this to refer to multiple sheets results in a #VALUE! error. As others more experienced than I have suggested, best approach is to combine the data from your various worksheets and work on the combined data. Good luck!

Hi There

Well.. I copied and pasted:

=Sheet1:Sheet4!$C$17:$E$20,Sheet1:Sheet4!$G$21:$H$24,Sheet1:Sheet4!$J$23:$L$25

And any attempt to do anything with the the newly defined range generated a #VALUE! error. So, I guess the answer is you can define the name but not do much useful with it. It was a rhetorical question in any case, related to another post, so lets not break sweat trying to solve this one. Thanks however for the enlightenment, as always.

Regards
Peter

Harumph! OK.. Plan B.. Work out how to move the worksheet in question out to another worksheet that only moves the cellsthat actually have some data in it..

OK.. The particular sledgehammer I have used to crack this nut.. Basically I’m importing data from MS_Project, re-formatting it and doing some smart stuff with confitional formatting to highlight overdue and nearly due items.

I have two worksheets, Project_Data and Project_Format. The first is where the data is imported. The second is the one that has the smarts regarding formatting. To allow for Project files of varying sizes the second worksheet allows for up to 2,000 import rows. Often there are less than 500 rows, simple approach would be to reduce the 2,000 rows.

The trick with the INDIRECT(CalcPrintArea) was to stop printing 2,000 rows. Plan B is to export the ‘Active’ rows to another worksheet, in fact it may as well be a separate spreadsheet. I ass-u-me this will be a job for VBA? Any pointers? As an aside, through the CalcPrintArea cell I know that I need to export all the cells in the area $A$1:$H$259, for the currently imported MS Project data.

The suggestion that Dick gave me worked, to a point. The ‘Automatic Format’ button is greyed out via both suggetsed routes?

Thanks Legare

We type at crossed purposes.. Although I mentioned only a single cell in my post, I actually want to pull through several hundred cells. Basically I have a large model that I ‘think’ I could reduce in size by pulling out the reporting side of it. What I need to do then is to look back into a planning/calculation model to pull out some financial data which is presented in a variety of report formats. What I was hoping to do was to allow the user to select which ‘model’ they wanted to get their data from. This would allow the reporting tool to be generic rather than tied to a single model.

VBA may be the only way to achieve this. Thanks for your advice for now.

Regards
Peter

Assuming the named range ‘MyData’ contains the range in which you are looking for the MIN or MAX values –

=MATCH(MIN(MyData),MyData,0)

Will tell you the FIRST location in the list where the MIN value can be found. i.e. If there were three values the same, this will only find the first occurence.

Using this, you could feed it into ADDRESS or OFFSET to calculate your lookup. Hope this helps.

Regards
Peter

In fact, here’s another way of doing it.. With this solution you calculate ALL the possible functions results in another area of the sheet and just display the result selected. If added the list of functions in F8:I8 and then calculated the results for each in F9:I9. F8:18 is a named range ‘lstFunctions’. D11 is where you select the actual result you want, this uses Data, Validation, List =’lstFunctions’ to limit valid choices. In cell E11 we then get the result we wanted by looking at where in the list ‘lstFunctions’ the chosen calculation, MIN, MAX, AVERAGE, SUM appears and then looking in the cell below that to get the result.

The formula to do this is:

=IF(ISERROR(MATCH(D11,lstFunctions,0)),0,INDIRECT(ADDRESS(9,(MATCH(D11,lstFunctions,0)-1)+6)))

The ADDRESS(9 AND the +6 are the key parts. The 9 is the ROW, 9 in this case. The +6 is the column, 6 being F. $F$9 is where our first result is parked =MIN(MyData). The MATCH(D11,lstFunctions) bit will return a number between 1 and 4, depending upon the value in D11. We need to take 1 away from this and then add it to the 6 which will give us the correct column, F, G, H or I. Obviously these co-ordinates should be changed to suit your purposes.

This approach could be extended for a fairly lengthy list of functions.

Regards
Peter

(Edited by j.peter.orourke on 23-Oct-02 10:07. System slow.. not sending attachments. Tried again!)

Hi Cathy

The attached spreadsheet I think does what you asked for? I’ve named B2:B30 MyData, amend to suit. I’ve used Data, Validation, List to limit what can be put in Cells A31 and A32. (Min, Max, Average, Sum). I’ve then used a nested IF function in B31 to B32 to calculate what has been selected. So, you end with something like:

=IF(A31=”Min”,MIN(MyData),IF(A31=”Max”,MAX(MyData),IF(A31=”Average”,AVERAGE(MyData),IF(A31=”Sum”,SUM(MyData),0))))

There may be a neater solution….. Hope this gives you some ideas.

Regards
Peter

Hi Simon

‘Attack’? Good heavens, hadn’t realised we were that sensitive in here! (Humour, ok!). Actually, Steve posted an even better solution than mine. I’m just not that comfortable with macro/vba solutions and so tend to avoid them. Not to say that I don’t use VBA, just limit it. As a consequence I have some amazingly clever single cell forumlas (formulae?) that are both indecipherable and probably more difficult to maintain than they ought to be.

Besides, one of the great things about this place is the variety of solutions offered to a single challenge. Somedays I make greater use of that sledge hammer than others.

Regards
Peter

Something like:

=ADDRESS(1,MATCH(A5,A1:E1))

Assumes the date you are looking for a date entered in Cell A5. The 1 in =ADDRESS(1, assumes that the dates you are searcing are in ROW 1. (A1:E1) Amend to suit.

Regards
Peter

Hi There

Not a macro, a single cell formula:

=INDEX(A118:A127,(MATCH(D125,B118:B127,0)))

Where:

A118:A127 is the column from which you want to display the value. (This is B in your question)
D125 is the cell you have entered the search/new value in.
B118:B127 is the column against which you want to check for the value entered.

Hope this helps some.

Regards
Peter

Arthur

The attached spreadsheet ‘may’ help explain Custom Cell formats to you. Basically I have used the four entries in A1:D1 in three rows below. i.e. The values in the first row are the same values in the three rows below, they are just formatted differently. Alongside each row I have shown the format string used to create the effects.

If you highlight any cell(s) and press Ctrl+1 it will bring up the cell formatting window. If you look at the ‘Number’ tab, you will see how the cell(s) you have highlighted have been formatted. Perhaps try playing with this spreadsheet and see what you can create.

If this doesn’t work, try posting a subset of your spreadsheet, explain what you want to achieve and I’m sure someone will jump in with advice. Good luck!

Regards
Peter

Also worth re-membering that you can achieve quite a lot with ordinary cell formatting.

Extract from Excel 2000 Creat a customer number format help:- ‘You can specify up to four sections of format codes. The sections, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify one section, all numbers use that format. If you skip a section, include the ending semicolon for that section. ‘

#,###.00;[ Red](#,###.00);0.00;”sales “@ – N.B. Do NOT put a space after the [ and before Red. I’ve put one here to get round thread formatting in the Lounge.
Positive ; Negative ; Zero ; Text

Read between the semi-colons to see the effect each number type has.

Regards
Peter

Hi There

Just guessing here… You say you have re-installed Office 97, you will need to ensure that all relevent service releases are also re-loaded. On the basis that you re-loaded Office 97 after you first encountered this problem, missing service releases are unlikely in themselves to be the cause of your problem. Though thought it worth mentioning it, just in case.

Regards
Peter

Hi Steve

The group I work with usually work remote from each other and also spend a lot of time e-mailing documents back and forth with external parties. For this reason I like to avoid any macros/VBA since it allows us to stick to our ‘Never run macros’ rule. It can be a pain but on balance for the best, certainly for our environment.

I promise, I’m not anti-VBA, I’d much rather have a simple ‘=ResourceSummary(D5:DD5,D$3:DD$5)’ than the long winded solution I’ve elected for.

Regards
Peter