Retrieving right name from sheet

MrGeek1

New Member
Joined
Jul 24, 2019
Messages
32
Hi,

I need your help with the following.

Column 1 Column 2
X1 Insurance
X1 Tax
X1 Utilities
X1 Other
X2 Insurance
X2 Tax
X2 Utilities
X2 Other

On another sheet I have the X1 and X2’s that need to be plotted on this sheet in the first column, so I want to have a formula that recognizes it needs to switch to X2 when having insurance again. Does anyone know a clean way of doing this?

Thank you!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi MrGeek1,

like this?
Book1
AB
1X1Insurance
2X1Tax
3X1Utilities
4X1Other
5X2Insurance
6X2Tax
7X2Utilities
8X2Other
9X3Insurance
10X3Tax
11X3Utilities
12X3Other
13X4Insurance
14X4Tax
15X4Utilities
16X4Other
17X5Insurance
18X5Tax
19X5Utilities
20X5Other
Tabelle1
Cell Formulas
RangeFormula
A2:A20A2=IF(B2="Insurance",A1+1,A1)


Custom Number Format on column A is "X"#, so there really is just a number in the cell, but it's displayed as X1, X2 and so on.

Regards,
Elaszat
 
Upvote 0
Thanks Elaszat. In this case my example was simplified and would have instead of X1, X2 names in it that need to be retrieved from another sheet. What would you suggest then?
 
Upvote 0
It's probably possible more elegant, but here's my solution:

Book1
AB
1AdamInsurance
2AdamTax
3AdamUtilities
4AdamOther
5BeatriceInsurance
6BeatriceTax
7BeatriceUtilities
8BeatriceOther
9ChadInsurance
10ChadTax
11ChadUtilities
12ChadOther
13DominiqueInsurance
14DominiqueTax
15DominiqueUtilities
16DominiqueOther
17EmileInsurance
18EmileTax
19EmileUtilities
20EmileOther
Sheet1
Cell Formulas
RangeFormula
A1A1=INDEX(Sheet2!$A$1:$A$7,ROUNDUP(ROW(B1)/4,0)+1)
A2:A20A2=IF(B2="Insurance",INDEX(Sheet2!$A$1:$A$7,ROUNDUP(ROW(B2)/4,0)+1),A1)

Book1
A
1
2Adam
3Beatrice
4Chad
5Dominique
6Emile
7Frank
Sheet2


Regards,
Elaszat
 
Upvote 0
I don't get the switch in names when I insert the A2:A20 formula. Is there another formula that starts from A1 that you can copy down and leave the same?
 
Upvote 0
Did you insert the formula in A2 and copied it down? That worked with me.

I just tried with the following formula:
Code:
=INDEX(Sheet2!$A$1:$A$7,ROUNDUP(ROW(B1)/4,0)+1)

and copied down. That worked, too.

Edit: Btw, you divide the row by 4, because you have 4 arguments (Insurance, Tax,Utilites, Other). If you have more than 4, you need to adjust the formula.

Regards
 
Upvote 0
I keep on getting the same name, no adjustment of names. Any idea why that is? Please note that my names are visible more than once in sheet2, for example like this:

Watt
Watt
Watt
Duck
Duck
 
Upvote 0
Hi,

yeah, that's likely the reason why the name on your sheet won't change.

My formula takes the number of the row, divides it by 4 and rounds it up to get a number. For Rows 1 to 4, that number is 1, for rows 5 to 8, the number is 2 and so on.

The Index looks at the list on the other sheet and counts the entries, until it matches the number we got with the Roundup(row(B1)/4,0) plus 1. - Although, now, that I see it again, that +1 is not necessary anymore, provided you start the list of names in the first row of the referred area.

Once the Index matches, it gives the corresponding name. So, for rows 1 to 4, that would be, in your example "Watt". For rows 5 to 8, it would be "Watt", but the second in the List.

Here's what you can do:

Option 1:
Restructure the list on the other sheet, so that each name only appears once.

Option 2:
Create an assist column, that holds all the names you need on the first sheet and reference that list.

Regards,
Elaszat
 
Upvote 0
I now have two different names (assist column) which I insert as Sheet2!$B$5:$B$6 and instead of B1 I have C8 which makes the row count different. Do you have any further solution?
 
Upvote 0
So your column count returns 8 instead of 1. Try this:

Code:
=INDEX(Sheet2!$B$5:$B$6,ROUNDUP((ROW(C8)-7)/4,0))
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top