I am trying to produce a reusable report that includes an indicator in a particular cell based on two numbers.
I have a block of cells in a, say, 6x6 matrix and would like to show specific character (x or dingbat) in the matrix; the position in the matrix based on two values in another part of the spreadsheet.
The logic would be along the lines of "Display ? in cell( a25, offset up(value in cell f23), offset across( value in cell l19))"
I can do this by placing an if statement in every cell in the matrix but wondered if there is an easier way to do this with a single function. The matrix would be replicated in various sections of the report and the 'if' approach would require an edit of every cell in the matrix
Many Thanks
Bob
? | UP | Across | ||||||
5.00 | 4.00 | |||||||
Replies (5)
Please login or register to join the discussion.
Can't think of a simpler way
without using vba. Can you not construct the spreadsheet initially so you can just cut and paste the formulae across the various matrices and allow the relative referencing to sort it out; you can always move your source cells subsequently to where they are wanted?
Having said that someone will be along in a moment with the obvious solution. :-)
Matrix
Sorry if I've misunderstood but it's late! Would the following do what you want: enter the required symbol in every cell of the matrix. Change the font colour to white, so that they all become invisible, then use a conditional format formula such as:
=AND(ROW()=$F$19,COLUMN()=$F$20)
(Obviously adjusting to allow for the offset from row1, column1 for the starting position of the grid)
set the format to black or red or whatever font colour. Only the cell where the row and column numbers match the cell values should then be visible.
Heat Map for large amounts of data ...
Alternatively ..
Heat maps allow lots of objects (KPI's, shares etc) to be easliy identified in order of importance or weighting
See
http://how.best-free-information.com/2009/04/how-to-create-a-heat-map-in-excel/
http://bitesizebio.com/articles/how-to-create-a-heatmap-in-excel/
http://blog.xlcubed.com/2008/08/heatmap-tables-with-excel/
Also use a google search of 'excel heat map'
Conditional formatting
Thanks for letting us know that it worked.
Interesting links re the heat maps. Excel conditional formatting in 2007 and 2010 also includes the 'Colour scales' option which allows for simple cell-based heat maps.