auto population based on single cell entry

[Guest]

I have a data range b37:dt47 which consists of information relevant to
11 different items. For now let's call the items a:k. I would like to be
able to populate an item's row of data/formulas based on inputting the
corresponding letter of the item in a cell. If I want the information for
item "b", currently I need to copy the entire row (38) and paste it in a
different location (let's say row 250). I would like to simply type "b" in a
cell in row 250 and for the data/formulas relevant to "b" to populate across
the new location. (cells b250:dt250)
I believe I could accomplish this with "If" statements in each cell
(after a few weeks of entering formulas) but I'm sure there must be a better
way.
Additionally, I can only hope that part of this formula could be used
to populate similar data from a drop-down selection in a seperate worksheet
in the same workbook.
Any assistance would be greatly appreciated.

 

[Guest]

I think VLOOKUP is the function you're looking for. Something like this
formula placed in cell C250, where you enter the name you're looking for into
cell B250:

=VLOOKUP($B$250,$B$37:$DT$47,COLUMN(C250)-1,FALSE)

This can then be copied across cells D250 through DT250. You'll probably
need to modify this slightly to fit your needs. Look up VLOOKUP in Excel
Help to learn more.

HTH,
Elkar

 

[Guest]

Elkar,
Thank you for the quick response. The result of your formula without any
tweaking being done by me results in a lovely row of #N/A's. If I try
tracing the error, I'm told that it is because of a circular reference. I do
not see where there could be a circular reference. Any ideas how I can
adjust the formula? I do use lookups and feel pretty comfortable with them.
Do you need a better explanation of what I'm trying to do?

Thanks in advance!

 

[Guest]

Just to clarify, you entered the formula starting in C250 not B250, correct?
Looking back at my post, that may have been a point of confusion.

Next step, the value that you manually type into cell B250 matches one of
the values found in B37:B47?

Do you have formulas in your range B37T47? If so, do any of them
reference Row 250?

Beyond those steps, I'm not sure where else to look, other than just doulbe
checking for typos.

HTH,
Elkar

 

[Guest]

Elkar,

Thank you. I did have a circular reference that I've now taken care of.
Your directions were crystal clear and did not cause me any confussion.
Two questions. 1 - will I be able to use this same formula (with the
correct reference cells/rows/columns of course) with a drop down selection
also?
2 - What does the "-1" do to the lookup in your formula?

 

[Guest]

1. Yes, you should have no trouble using this with a dropdown list.

2. The COLUMN(C250)-1 portion of the formula specifies which column within
the lookup range (B3747) to return a value from. COLUMN(C250) returns 3,
since C is the 3rd column. The -1 reduces that number to 2 (which is the
column we want from the lookup range (B is first, C is second etc...).
Normally, I'd just say 2 if I wanted the second column returned, but since
you had a rather large number of cells to copy this formula to, it makes it
easier to create a reference that will increment when copied. Hopefully that
makes sense.

 

[Guest]

Thank you again. You've saved me an awful lot of time. I didn't know I
could use a lookup in this way.

 

[Guest]

Hope you're still checking this thread. What do I need to do to copy the
cells' formats that populate from the lookup? VB?

 

[Guest]

Yes, VB would probably be required here. A formula cannot pull formats, only
data. Although, depending on your needs and how many different formats are
involved here, Conditional Formatting might be an option. You can have 3
conditional formats, plus the default format.

If you want to pursue the VB option and need help, I might suggest starting
a new thread. I dabble in VB code now and then, but am far from an expert
there.