# Posts by Xani

• ## Using Countif to calculate totals from another excel tab

Dear All

In the List Of Tickets tab, there is status column, In the Sheet 1 tab there is status which are Cancelled, Closed, Inprog, Queued and Verified. I try to use countif to count how many resolved there are how many inprog there are etc..

The problem I am receiving error message as shown in the photo.

## Files

• 68911494.xlsx

• ## Calculate Total Times Ticket Open

Hi

There should be reported date, I will check data why it is not there, but If I want to paste new data it will not work, do I need to do something after pasting new data?

Also why in the raw data there is reported date for all data but in the second table half of reported date not showing up ?

• ## Calculate Total Times Ticket Open

Dear Team

I am trying to calculate total times record open from Reported Date up to current date.

I will add new column called Total Duration where it needs to calculate DD / MM / YYYY , For example 1 Day , 15 Hrs , 20 Minutes

Present Day - Reported Date = 1 Day , 15 Hrs, 20 Minutes this is the time ticket been open as its present day

How can I do that?

## Files

• 12803940.xlsx

• ## How to make drop down menu by showing staff name and staff id ?

Thank u a lot , I will try it

• ## How to make drop down menu by showing staff name and staff id ?

Hello

I am trying to make Dropdown menu by selecting staff name from Employee Name & It will show directly staff number next to it.

I try to do but I couldn't understand the way.

Regards

• ## ​Calculate Duration between two dates

Yes I am importing data from system, so how i will be able to do that? maybe its complex task

• ## ​Calculate Duration between two dates

Hello

Can you help me to write formula to calculate duration between two dates Column K (Actual Finish) - Column J (Reported Date)

Format should be as : 1 Day 5 Hrs 20 Min

In the end I should calculate total Downtime of above data. Row 245

I don't know if this is consider too complicated formula or not but hopefully someone can help me.

• ## Dropdown Menu & VLookup formula not working

After click on Evaluate Formula it shows only value as 71 for example

• ## Dropdown Menu & VLookup formula not working

Also there is no more easy formula similar to the one I inserted?

• ## Dropdown Menu & VLookup formula not working

Thank u it worked but can u explain to me how does the two formula worked ?

• ## Dropdown Menu & VLookup formula not working

I open the test file same issue exist, my problem is how to add data validation for HRU and FCU without changing data validation list of AHU ?

If I choose FCU , numbers will show for example 302 and 304

If I choose HRU, numbers will show for example 5

What is the correct process to do it , please teach me that

• ## Dropdown Menu & VLookup formula not working

Thank u it worked! but again had the issue with after adding data validation list of AHU as drop down list for number, I change to HRU and from number I go to data validation and I choose that HRU number.

If I go back to AHU the number that I choose previously it gone and only number for HRU it will be showing how comes?

• ## Dropdown Menu & VLookup formula not working

Dear All

I make this table, and I try to make search feature by using drop down menu and vlookup formula, I use data validation to add HVAC Unit & Number, the problems am facing the following:

1. After I add HRU units number, I change HVAC Unit to FCU and I try to add drop down list for number from below table after I do this step through data validation it will change also number for HVAC unit.

2. After changing number it will not return correct data for Building, Location and Level from below table.

Please advise what is the mistake I did and how can I correct it. " I want to learn "

• ## How to calculate variance and ratio total open and closed tickets?

Dear All

Please find attached file, is the formula for variance and % Ratio Open/Total number correct or wrong ?

Regards;

• ## How to split data into separate workbooks?

It worked normal, Thanks for help All.

• ## How to split data into separate workbooks?

The format worked but the issue after separate each workbook it copy all data, for example service group ACO output same original data file.

So separate files does not work, i attached some examples.

## Files

• 02796906.xlsx

• ACO.xlsx

• AGL.xlsx

• ## How to split data into separate workbooks?

I mean after I run the Macro is it possible to have each workbook to have specific table format? for example same attached file?

As will take me very long time to go to each workbook to modify the format to be same attached file.

Row height 30

All text center align and middle

## Files

• 02796906.xlsx

• ## How to split data into separate workbooks?

Dear royUK

Did u find what is the issue exactly and how to solve this?

Regards;

Dear royUK

## Files

• 07050599.xlsm