Excel: Auto-Fill Cells in Second Sheet Based on Data & Dropdown Selection from First Sheet

Hello,

Does anyone know if it is possible, and if so how, to do the following

I have a dataset on one sheet

100 ABCD
200 SSER
300 DETQ

e.t.c.

In the second worksheet, I would like to have (apart from everything else) 5 lines
quantity
letter_1
letter_2
letter_3
letter_4

the quantity field would be a drop-down list where I could choose 100, 200, 300 etc. and after selecting the letter_1 letter_2 letter_3 letter_4 field, they would be automatically taken from the first sheet. and I don't want to do it with some IF because there are many more rows in the first table

The matter is very simple
1.You add a range name for the first column of the first sheet (for a value of 100,200 ...). Just select them all, right-click and "name the range".
You call it what you want, I called it "data"
2. in the second sheet, in the cell where you want to select from the list 100,200 .. you activate it and in the "data" tab you press the correctness of the data.
3. When it starts up, in the "settings" tab, select the "list" option from the "allowed" drop-down list.
4. In the source you enter "= data" (without the quotes) - here you enter the name you created by selecting 100,200 ... etc
5. Then in the records below you use the function VLOOKUP, and change only the column (third parameter) from which the value is to read, this function.
If you have a problem with this function, see the built-in help in Excel, everything is clearly described there.
greetings

It helped?

Hello.
This is exactly what I was looking for.
I have one more question. Is it possible to do the same when the data is in a separate file and not in a separate sheet?

apply a hyperlink to the given file and the corresponding data on the sheet

Approx. But how do I reference the data in another file in the "SOURCE" line of the Data Validation window? Do I put a hyperlink there? How does this entry look like?

Should I use a hyperlink to download the entire data file to the basic file and then use it for searching and substituting?

Thanks in advance and regards.

Hello,

I need help with a similar matter. I want to create a sheet with a drop down list. Pattern:


Please help!

Attach a file.

Please!

There are two tabs in the sheet: "Main" and "Data". The data that is on the left side of the main sheet is best as if it were in the "Data" tab.

Thanks in advance!

But those drop-down lists are where exactly are they supposed to go in each cell?
Check the file.

It is important to me that when changing R1, R2 etc. these coefficients assigned to a specific R1, R2 etc. have changed. i.e. e.g.
when selecting R1, it automatically completes the cells related to R1
when selecting R10 -ll- -ll- -ll-

So if you change the value to R1 in cell A3, the values should change in the entire row 3, right?

Exactly,
for R1, the data assigned to HB and HF as well as HC and HH should change
(FTSH and HB = are calculated as HB * HF = FTSH and HB-FTSH = HB = ) - similarly in the case of CSH and HC =

with R2, the data assigned to AE and AI as well as AD and AG should change
(CSA and AE = are calculated as AE * AI = CSA and AE-CSA = AE =

But what should HF, HH, AI, AG change for? You do not have data for them, and if they are to change, what is to be calculated there?

Added after 12 [minutes]:

Something like that?

I changed because there were references to cells, not headers. It should be clear now.

Gray box - cells that are calculated
Colored fields - should be automatically completed with "data"

Maybe now.

The previous sheet is super because there is a drop-down list and the data is in a separate tab.

Will adjust, thanks and sorry for the confusion!

Greetings!

Added after 1 [hours] 12 [minutes]:

Unfortunately, there are errors:
FTSH - instead of 0.33 a is 0.40
CSH instead of 0.17 a is 0.00
AD instead of 0.83 a is 1.17

And one more question, is it possible to add, for example, 100 records to "data" and will something need to be changed in the spreadsheet?

President open the file copy test-new.

Junior85 wrote:

And one more question, is it possible to add, for example, 100 records to "data" and will something need to be changed in the spreadsheet?

instead
= VLOOKUP (L2, $ A $ 2: $ I $ 17,2,0)
you write
= VLOOKUP (L2, $ A $ 2: $ I $ 100,2,0)

Thanks, I managed it somehow

Hello,

1. Would like to do conditional formatting for HB, AE and AD and HC cells. If any of these cells has the value = 1.5 AE> = 2
HB> = 1 AE> = 2
HB> = 2 AE> = 2
HB> = 0.5 AE> = 2 - all in one color

I mean the formulas

Apply conditional formatting on the Home tab.

Yes I know. I managed to deal with the formatting in the column (+ underline) with one value. The problem arises when two or more values are involved. Despite many attempts, "if" and "and" formatting is beyond my strength :wink:

I don't mean a ready but an example ...

Junior85 wrote:

I don't mean a ready but an example ...

You can find an example of variable conditional formatting of columns in the linked thread Link

Gentlemen, I have another problem. I coped with Conditional Formatting, but I can't cope with the last task.

In "Main" I would like to create a dependent list. After choosing a league, players / teams are assigned to me.

Attached file

Anyone able to help?

And why should we waste our time for someone who is a colleague @ krzychu.m for the file sent with the solved task "thank you" :

Junior85 wrote:

Thanks, I managed it somehow

I think my entry was misunderstood ...

"Thanks m ! Thanks to you, I managed to deal with it somehow ".

Coming back to the topic ... I managed to do the formatting and underlining by trial and error.
The problem with the drop-down list is that I am not able to assign a specific amount of data to a particular category from the drop-down list, so that after selecting Team A, I can only assign specific players.

I watched a lot of videos on youtube - a movie with household appliances or car brands, but I still can't deal with it.

Junior85 wrote:

... The problem with the drop-down list is that I am not able to assign a specific amount of data to a particular category from the drop-down list, so that after selecting Team A, I can only assign specific players.

I watched a lot of videos on youtube - a movie with household appliances or car brands, but I still can't deal with it.

... ... with a friend it's real cessator hortorum ... ... so that you do not want to enter the search on the electrode / in the network (only some household appliances on the "wy-tube") entries "excel dynamic list" ... ...
Zależna_li...miczna.zip Download (3.99 kB)

Thank you very much! Believe me, I looked a lot and apparently it's beyond my strength.

The problem can be considered as solved.

Best regards and thank you again EVERYONE for their tips ...