Formatting Cell Values

[mpl34]

I am trying to have cell values of 1-1, 1-2, 1-3, 1-....

My guess is this would be most easily achieved through editing the format. I would like it so when I drag the cell containing 1-1 the next cell will have the value 1-2, and the following 1-3 and so on.

Secondly, I would like a cell to be dependent on the value of 2 other cells (both these cells contain drop down lists).

So what I am trying to achieve is that if i select the first value in the list in Cell #1 and the first value in the list in Cell #2... Cell #3 will display 1A. (i.e. if I were to choose the 2nd value in both lists Cell #3 will display 2B). I have shown a little example incase it wasn't clear...

List 1 List 2 Result
Value 1 Value 1 1A
Value 2 Value 1 2A
Value 1 Value 2 1B
Value 4 Value 3 4C
Value 5 Value 2 5B

The values I have listed under result are the actual values I am wanting (not just examples)

Cheers

 

[mpl34]

I have sloved my 2nd issue by using an index formula.... which although provides the desired outcome is not exactly the solution I was looking for as I did not want to have to reference other cells, but will do.

Still require help on issue #1.

 

[mpl34]

Solved.... format formula is "1-"#

 

[Peter_SSs]

OK, you already have solutions but ..

For the first issue, that already happens for me when I drag 1-1 it becomes 1-2 etc. How was your 1-1 cell formatted originally?

For the second issue, I don't think you should need INDEX, provided your Data Validation in your 2 target cells reference a list in your workbook. Something along these lines.

Excel Workbook
	A	B	C	D	E
1	Value 2	Value 4	2D		Value 1
2					Value 2
3					Value 3
4					Value 4
5					Value 5
6
Match Values

#VALUE!

 

[mpl34]

For the first issue, that already happens for me when I drag 1-1 it becomes 1-2 etc. How was your 1-1 cell formatted originally?

The original formatting kept recognising 1-1 as a date. So when I change the formatting to number it would then recognise 1-1 as 40179. I could then change the format to general but when I re-entered 1-1 it would automatically change the cells format to a date again. The problem was using "-" so by changing the cells format to custom "1-"# I did not have to enter "-" into the cell as it was included by my formatting

 

[Peter_SSs]

I formatted my first cell as Text before entering the 1-1, then dragged that cell.

Did you try the suggested MATCH formula (instead of INDEX) for the second problem?

 

[mpl34]

Ok, formatting the cell as txt did solve the problem.

I gone with the index formula. I originally wanted to avoid using reference cells but because I have decided to use drop down boxes to make it easier to use I decided to have a seperate reference sheet anyway