E
echo_park
Hello all, I have a problem relating to Excel. It refers to creating a
search function which goes beyond the scope of VLOOKUP, or so I think
anyway.
I have a complex table displaying information for electronic
components, my problem relates to part of the table which is outlined
below.
Each component has its own row in the table.
Cells C5 - M5 contain headers of frequency values. (1, 5, 10, 25MHz
etc) with impedance values at these frequencies displyed in the
corresponding columns (C-M).
Component reference numbers are displayed in column P.
I have set up, on a separate page, two drop down lists of the available
frequencies, these are in cells C6 and C8. In the adjacent cells (D6,
D8) I want to be able to type in a value for the impedance for two
different frequencies (as selected from the drop down lists) and have
excel display in cell E8 the component reference number of the closest
matching part.
I thought the best way to define "best match" would be to have excel
sort the data with the closest match for one frequency at the top,
ranging down to the worst match, then sort by the second frequency as a
secondary sort. (Like "sort by", "then by" etc)
After much thought and searching the net for help I have had little
luck in coming up with any kind of solution to this. Seems I first need
to establish a link between the text in the drop-down list and the
corresponding column of the table. Then somehow get excel to change
what the list is sorted by, based on both the columns in question and
the value I am looking for. I also have no idea if excel can order by a
"closest match" to a value as it would have to look at the differences
in the numbers, whether they be larger or smaller than the value
entered.
I understand this is a very complex search and may by beyond the scope
of Excel without very complex code, as I have no knowledge of code this
is not really something I can consider. I also understand that I may
have explained very poorly what i am trying to achieve, if anyone could
give me any help with this it would be very much appriciated. If you
don't understand some of what I mean I will try and explain in more
detail.
Many thanks!
Richard.
search function which goes beyond the scope of VLOOKUP, or so I think
anyway.
I have a complex table displaying information for electronic
components, my problem relates to part of the table which is outlined
below.
Each component has its own row in the table.
Cells C5 - M5 contain headers of frequency values. (1, 5, 10, 25MHz
etc) with impedance values at these frequencies displyed in the
corresponding columns (C-M).
Component reference numbers are displayed in column P.
I have set up, on a separate page, two drop down lists of the available
frequencies, these are in cells C6 and C8. In the adjacent cells (D6,
D8) I want to be able to type in a value for the impedance for two
different frequencies (as selected from the drop down lists) and have
excel display in cell E8 the component reference number of the closest
matching part.
I thought the best way to define "best match" would be to have excel
sort the data with the closest match for one frequency at the top,
ranging down to the worst match, then sort by the second frequency as a
secondary sort. (Like "sort by", "then by" etc)
After much thought and searching the net for help I have had little
luck in coming up with any kind of solution to this. Seems I first need
to establish a link between the text in the drop-down list and the
corresponding column of the table. Then somehow get excel to change
what the list is sorted by, based on both the columns in question and
the value I am looking for. I also have no idea if excel can order by a
"closest match" to a value as it would have to look at the differences
in the numbers, whether they be larger or smaller than the value
entered.
I understand this is a very complex search and may by beyond the scope
of Excel without very complex code, as I have no knowledge of code this
is not really something I can consider. I also understand that I may
have explained very poorly what i am trying to achieve, if anyone could
give me any help with this it would be very much appriciated. If you
don't understand some of what I mean I will try and explain in more
detail.
Many thanks!
Richard.