Tjyylli

What is the use of option -o in the useradd command?

Limit the amount of RAM Mathematica may access?

Geography at the pixel level

Monty Hall variation

Why is it "Tumoren" and not "Tumore"?

Why could you hear an Amstrad CPC working?

Is domain driven design an anti-SQL pattern?

Is it possible for the two major parties in the UK to form a coalition with each other instead of a much smaller party?

Does a dangling wire really electrocute me if I'm standing in water?

How was Skylab's orbit inclination chosen?

Manuscript was "unsubmitted" because the manuscript was deposited in Arxiv Preprints

What do the Banks children have against barley water?

Landlord wants to switch my lease to a "Land contract" to "get back at the city"

Does it makes sense to buy a new cycle to learn riding?

Why did Howard Stark use all the Vibranium they had on a prototype shield?

Why do UK politicians seemingly ignore opinion polls on Brexit?

Is flight data recorder erased after every flight?

Does duplicating a spell with Wish count as casting that spell?

I looked up a future colleague on LinkedIn before I started a job. I told my colleague about it and he seemed surprised. Should I apologize?

How long do I have to send payment?

Lethal sonic weapons

"Riffle" two strings

Understanding the implication of what "well-defined" means for the operation in quotient group

CiviEvent: Public link for events of a specific type

Excel Formula - Display Values Not In Use

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

0

I am trying to populate a cell, and only display value if it does not already exist. If it exists, then display another value.

...But I cannot get my head around how to do this.

I currently have the following formula placed on cells in the E column

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

This matches some other criteria as you can see here. However, what you can also see is the value 7 is getting displayed twice. I'm wanting it not to display any values in use.

So, I then added an extra column, called 'Room In Use' in a config sheet, where the data is coming from... this column has some formulas to check if the values are in use

=NOT(ISERROR(MATCH(H2,Sheet1!E2:E41,0)))

You can see this in action here:

So, going back to my sheet1, I think this formula needs another condition to say If room in use == FALSE then do

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

else if all rooms are TRUE then display 'None Available'

Is this even possible in an excel formula or will I need to use VBA?

I have attached my excel document here: https://www.dropbox.com/s/crmbbp8esdvnv29/Book1.xlsm?dl=0

microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007

share|improve this question

asked 2 days ago

user1472266user1472266

1256

add a comment | 

0

I am trying to populate a cell, and only display value if it does not already exist. If it exists, then display another value.

...But I cannot get my head around how to do this.

I currently have the following formula placed on cells in the E column

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

This matches some other criteria as you can see here. However, what you can also see is the value 7 is getting displayed twice. I'm wanting it not to display any values in use.

So, I then added an extra column, called 'Room In Use' in a config sheet, where the data is coming from... this column has some formulas to check if the values are in use

=NOT(ISERROR(MATCH(H2,Sheet1!E2:E41,0)))

You can see this in action here:

So, going back to my sheet1, I think this formula needs another condition to say If room in use == FALSE then do

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

else if all rooms are TRUE then display 'None Available'

Is this even possible in an excel formula or will I need to use VBA?

I have attached my excel document here: https://www.dropbox.com/s/crmbbp8esdvnv29/Book1.xlsm?dl=0

microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007

share|improve this question

asked 2 days ago

user1472266user1472266

1256

add a comment | 

I am trying to populate a cell, and only display value if it does not already exist. If it exists, then display another value.

...But I cannot get my head around how to do this.

I currently have the following formula placed on cells in the E column

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

This matches some other criteria as you can see here. However, what you can also see is the value 7 is getting displayed twice. I'm wanting it not to display any values in use.

So, I then added an extra column, called 'Room In Use' in a config sheet, where the data is coming from... this column has some formulas to check if the values are in use

=NOT(ISERROR(MATCH(H2,Sheet1!E2:E41,0)))

You can see this in action here:

So, going back to my sheet1, I think this formula needs another condition to say If room in use == FALSE then do

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

else if all rooms are TRUE then display 'None Available'

Is this even possible in an excel formula or will I need to use VBA?

I have attached my excel document here: https://www.dropbox.com/s/crmbbp8esdvnv29/Book1.xlsm?dl=0

microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007

share|improve this question

asked 2 days ago

user1472266user1472266

1256

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

=NOT(ISERROR(MATCH(H2,Sheet1!E2:E41,0)))

=INDEX(config!H2:H20,

MATCH(1,

(F4=config!F2:F20)*(VLOOKUP(D4,config!C2:D20,2,FALSE)=config!G2:G20),0))

share|improve this question

asked 2 days ago

user1472266user1472266

1256

share|improve this question

asked 2 days ago

user1472266user1472266

1256

asked 2 days ago

user1472266user1472266

1256

asked 2 days ago

user1472266user1472266

1256