CODEWITHSUNDEEP
excelexcel-formula
MasterJedi
MasterJedi

Reputation: 23

Populate several cells with values from list based on value of another cell

I am looking for some help with a report. On 'sheet1', I have a list of regions, and corresponding hospitals, structured in a format like this:

   A              B             C             D              E          
1 Regions     Region 1      Region 2      Region 3       Region 4
2 Region 1    Hospital 1    Hospital 6    Hospital 11    Hospital 15
3 Region 2    Hospital 2    Hospital 7    Hospital 12    Hospital 16
4 Region 3    Hospital 3    Hospital 8    Hospital 13    Hospital 17
5 Region 4    Hospital 4    Hospital 9    Hospital 14    Hospital 18
6 Region 5    Hospital 5    Hospital 10
7 Region 6
8 Region 7
9 Region 8

On my 'report' sheet, I have the following table set up with column headers 'Region' in A6 and 'Hospital' in B6:

       A              B            C            D            E
6   Region         Hospital
7   Region 1       Hospital 1
8                               
9
10
11                 Hospital 2
12
13
14
15                 Hospital 3
16
17
18
19                 Hospital 4
20
21
22
23                 Hospital 5
24

What I would like to do: Upon selecting 'Region 1' from the list I have in A7 of the 'report' sheet, I would like B7, B11, B15, B19, and B23 to populate with the values listed in the column headed 'Region 1' (Column B) of 'sheet1'.

As an added complexity, I need the solution to be dynamic, in that when I add a hospital to one of the 'region' columns in 'sheet1', this will also be carried over to the next free cell in column B of the 'report' sheet when that region is selected in A7. There are 3 cells between each 'hospital' in the report sheet.

Upvotes: 0

Views: 27495

Answers (2)

Jez
Jez

Reputation: 21

Use the VLOOKUP function:

VLOOKUP(A7,Hospital!:$A$1:$E$9,2)

A7 = The value to match

<br>Hospital! = Sheet where the lookup table exists
<br>:$A$1 = The starting cell for the table (top left corner)
<br>:$E$9 = The end cell (bottom right corner)
<br>2 = the corresponding value you want to display

Upvotes: 2

Saechel
Saechel

Reputation: 152

try this formula and put at B7 it will get the first hospital from the list. sheet2 is your report sheet. putting the formula again after 3 cells will get the rest of the hospital from that region. though you have to put the formula manually after each 3 cells to make it dynamical using formula.

=IF($A$7=Sheet1!$B$1,IF(Sheet1!B2<>"",Sheet1!B2,""))

note : increase B2 by 1 when copied to other cell for example when you put the formula to the next cell which is going to be B11, Sheet1!B2 should become Sheet1!B3 like so

=IF($A$7=Sheet1!$B$1,IF(Sheet1!B3<>"",Sheet1!B3,""))

do the same procedure for the rest of the cell where this formula will be copied

Additional : you said this only solve the partial problem so i revised it. try using this.

=IF($A$7="r1",Sheet1!B2,IF($A$7="r2",Sheet1!C2,IF($A$7="r3",Sheet1!D2,IF($A$7="r4",Sheet1!E2,""))))

Note:the r1 to r4 that you see is the name of your region. just replace them with Region 1 and so on.

Upvotes: 2

Related Questions