This is my first post in this forum, and I'm afraid I think it is a difficult one. I would be really
grateful for anyone's help on this. Please note that I am a complete novice with Excel and would be totally grateful for
Let me explain briefly the background to my question and what I am trying to achieve.
1) I have an excel spreadsheet, which runs to about 12 pages, containing a lot of data. (It is
actually within table format in Word, but I will need to transfer this all to Excel).
2) There are 85 rows
within the entire spreadsheet.
3) There are only 4 columns used in total, these columns are called 1, 2, 3, and
4. The data cells, under each column heading, contain different combinations of the words Big, Medium, Small, Micro. (Eg,
Big/Big/Small/Small or Medium/Medium/Medium/Medium. You get the idea, but there are also other words/symbols contained within
each cell.) .
4) Within each row, only 4 cells are used, which contain the one of the words
Big/Small/Medium/Micro (along with other data). Each of the 4 cells are under the column headings 1, 2, 3 and 4.
5) Each of the 85 rows (and thus the 4 cells within each row ) within the spreadsheet contain a mix of data â€“ words
and numbers. So, row one could contain the data words "Big, Big, Medium ,Small , " , row two could be,
"Medium,Medium,Small,Big," row three could be "Micro,Big,Small,Medium" etc.
If I client says to me,
what is the answer to the combination "Big/Micro/Small/Medium", I have to scroll through 12 pages of excel spreadsheet to
find the row with that specific combination and this can take ages!
6) I was therefore wondering if it is
possible, at the top of the spreadsheet to include buttons or some kind of macro to make finding this information quicker,
whereby I can click on a specific combination of buttons (which represent the specific combination requested), which will
then output the content of the entire relevant row (all 4 cells) in the "Answer Cell", as illustrated below? (Please note -
the output doesn't have to be within one cell - I am open to other suggestions as to how to display the content of the 4
relevant data cells at the top of the spreadsheet).
I set out below how I would like these buttons to look at the
top of the spreadsheet. The "xs" are meant to be buttons!
There will only ever be one applicable data row,
depending on the specific combination of the 4 buttons I push, and thus the relevant row would need to be displayed where the
italic word "answer" is written, ie just within one cell, right at the top of the spreadsheet, as illustrated below. It
doesn't matter how wide this answer cell needs to stretch to accommodate the data.
Apologies that the formatting
goes off in the below diagram. There are 5 columns listed here. The first 4 columns each contain the buttons headed
"Big/Medium/Small/Micro" and the 5th column is meant to contain the answer cell.
1 2 3 4
X Big X Big X Big X Big Answer cell!
X Medium X Medium X Medium X Medium
X Small X Small X Small X Small
X Micro X Micro X Micro X Micro
Thus, if the client says what is the answer to the combination
"Small/Small/Small/Small", I could click the 4 buttons which each represent the word "small" (in cells A4/B4/C4/D4, as I have
tried to represent in the diagram above), which would automatically locate the specific row within the data section of the
excel spreadsheet, and would thus spit the entire contents of that row into the answer cell, at E2.
Any help and guidance would be very much appreciated.
I have attached 1 page of the sample data (this is just a copy and paste into excel from word - the data will need to be
put in individual cells). It may look complicated, but you will see that each of the 4 cells per row include a combination of
the words Big/Medium/Small/Medium (etc) - along with a lot of other data contained within each cell.
This needs to
be put in excel.
What I want to do, is to be able to select, using the buttons I discussed, for example, the
combination Big/Big/Small/Small, which will extract (for the purposes of this example) the
data in row 2 of the attached document, and reproduce it in another row of my choosing.
Perhaps there is some way
of encoding each cell in the background with the word "Big or small or medium or Micro", as appropriate, which would make it
easier for excel to find.
Perhaps using some IF equation for the VBA buttons? I.e if the value in column 1 is
"Big" (ignore all other values in column 1) AND if the value in column 2 is "Small" ignore all other values in column 2 AND
if the value in column 3 is "Medium" ignore all other values, etc, until you have inputted the specific combination, using
the buttons, which thus locates the relevant row in the data area, and duplicates this row in another row of my choosing (ie,
just below the buttons!).