bearcub
Well-known Member
- Joined
- May 18, 2005
- Messages
- 731
- Office Version
- 365
- 2013
- 2010
- 2007
- Platform
- Windows
I have a spreadsheet where I have a column with different currency headers. I am only allowed to have one currency per record.
For example, I have the following 5 column headers starting in B1:
US, CA, UK(GBP), FR(EUR)
what I would like to do is create a formula that would determine which column has a value > 0 (an employee is paid a commission). If there is an amount paid I need to add which ledger it impacts (starting in A2).
The region needs to be automatically populated with these results. Those are the ledger names used in our company.
The formula would look across each row to determine which cell in that row is greater than zero (commissions are being paid). It then has to go up to the column header to get a column name so it knows the ledger it impacts (per the example above).
I was toying with index match match but I don't know how I would determine which cell in the lookup range contains a value >0 in the row argument. I need to match the row address to the column header so I know which name to choose.
I was also mulling over the choose function but how to I get the first argument to know the cell address?
I could use a nested if, which I can do but I was wondering if there is a more efficient way of getting this column name.
Appreciate your help in advance,
Michael
For example, I have the following 5 column headers starting in B1:
US, CA, UK(GBP), FR(EUR)
what I would like to do is create a formula that would determine which column has a value > 0 (an employee is paid a commission). If there is an amount paid I need to add which ledger it impacts (starting in A2).
The region needs to be automatically populated with these results. Those are the ledger names used in our company.
Code:
Region US CA UK(GBP) FR(EUR) UK(EUR)
US 5000
CA 300
UK 200
FR(EUR) 250
UK(EUR) 200
The formula would look across each row to determine which cell in that row is greater than zero (commissions are being paid). It then has to go up to the column header to get a column name so it knows the ledger it impacts (per the example above).
I was toying with index match match but I don't know how I would determine which cell in the lookup range contains a value >0 in the row argument. I need to match the row address to the column header so I know which name to choose.
I was also mulling over the choose function but how to I get the first argument to know the cell address?
I could use a nested if, which I can do but I was wondering if there is a more efficient way of getting this column name.
Appreciate your help in advance,
Michael