Excel IF Funtions possibly

Swaff39

New Member
Joined
Nov 22, 2022
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I'm not 100% certain I am using the proper function, thus the "possibly" in my title.
I have 2 sheets. Sheet two has a list of people in column A (a1:a25)and through the spreadsheet from a3 - z3 there are names of companies. For each employee, I have typed a * where they are working that day.
For each employee, I have 4 working in column c, 4 in colmn d etc.
On sheet 1 I have
A B C D
Row 5 Emp 1, Emp 2, Emp 3 Emp4

My question is... How do I do a logical test to see

Which emp1 on sheet 2 is working for company in column c row 4
Which emp2 working for company in column c row 10
if I have emp3 working for company in column c row 15
if I have emp4 working for company in column c row 22

Then put the employee name in row 6a, 6b, 6c and 6d

I looked at the countif and although that seems to return a value, HOW can i have it put the persons name in and not a yes or no.

Here is the command I was trying..
=IF('Second Sheet'=(COUNTIF(C5:C25,"*"),"c5:c25","0")

And of course, there is an error.. I know I am missing something or I am not using the correct operand. If, If then, conditional if?

What i want is:
If there is a star in column c, in this range c2:c10 then put the persons name that is corresponding in the space on sheet 1, otherwise, move on till you find a * and then put that corresponding name in it.
Then do the dame but for c11:c20

Hope this makes sense!

Harold
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to the MrExcel board!

It is very hard to understand your setup & requirement. For example
list of people in column A (a1:a25)
This implies that cell A3 contains a person's name.

from a3 - z3 there are names of companies.
This implies that cell A3 contains a company name

They cannot both be correct.


Perhaps you could use XL2BB (described below) to give us a small set of sample data and the expected results and explain again in relation to those XL2BB samples?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hello and thanks for the quick reply.

Here are the 2 sheets. On sheet 1 there are stars.

I want to look at where the stars are, and put the names into the columns on sheet 2

for example. On sheet 1, there is a star beside heather-lyn in C11, I want her name to appear in sheet 1 where it says soprano.
Adrienne in C15 Name where it states Alto
etc

I tried the XL2bb, but... (and it could be late) i couldnt get it to work... again, may be the time and concentration level.

I am hoping pictures make more sense.
 

Attachments

  • Xcel Sheet 1.JPG
    Xcel Sheet 1.JPG
    109.4 KB · Views: 10
  • xcel sheet 2.JPG
    xcel sheet 2.JPG
    28.1 KB · Views: 11
Upvote 0
The pictures do help but unfortunately, we cannot copy from them to test. :(
Perhaps try XL2BB again when fresher. If you still have difficulty with XL2BB, post the details of where things go wrong in a thread in the 'About This Board' forum
 
Upvote 0
Copy of Dickens Accounting 2022-1.xlsx
ABCDEFGH
1EventRenfrewVanierShenkmanMayor SavannahVanier
2Date2-Dec3-Dec3-Dec3-Dec3-Dec4-Dec
3Time6:00 - 7:001:00 - 3001:00 - 3:00 2:00 - 5:004:30 - 6:001:00 - 3:00
4So;prano
5Andrea MacWilliams*
6Reba Sigler**
7Clarissa Fortin
8Talia Kennedy*
9Pegi Jenkins*
10Alto
11Heather-Lynn Smith***
12Colleen Woodhouse*
13Julie Heath**
14Tammy Shaw
15Adrienne Morey*
16Tenor
17Ken Parlee**
18Gabor D. Hegedus
19Kenny Hayes*
20Harold Swaffield**
21Jennifer Grimsy (b)
22Antonio Direnzo (b)*
23Bass
24Lee-Pierre Shirey***
25Brian Wehrle**
26Christopher Yordy*
27John Lister
28Extra Singers
Performance Confirmation

Copy of Dickens Accounting 2022-1.xlsx
NOPQ
1SopranoAltoTenorBass
2yesnono
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Accounting
Cell Formulas
RangeFormula
O2:Q2O2=IF(COUNTIF(D5:D9,"*"),"yes","no")
 
Upvote 0
Thanks for mastering XL2BB. I'm unsure exactly what you want help with. Can you explain that again in relation to this latest sample data and step us through how you would manually get the result(s) that you want?
 
Upvote 0
Hi,

Sorry for the late reply. On the sheet Performance Confirmation I put a * for each singer where they need to be on the date that hey need to be there. Row 123 has the data about each job as indicated.

On the Accounting sheet, I have the voice parts for each part. I need a formula that will scan the performance confirmation for the * and put the NAME of the singer in column a into the spreadsheet on Accounting. Each person can ONLY be in one place at any given time (obviously) i also have an individual accounting sheet for each singer in the tabs section that may be easier to drive this with. I

use the formula - IF('Performance Confirmation'!<cell of person>="*",2,0) < this checks for the * and puts them as singing in the space.

Perhaps I can use the individual sheet per performer to generate the list I want.

I also could be asking to use the entire wrong Operand for what I want. I could only figure out the IF statement would fit, but I may be way off base for this,.

Something like ... if singer on performance confirmation has * then put singers name in this column on the accounting sheet. <-this was why I was using the IF operand but.. I may be way off base. Thus the title of my query above.
I hope this explains it..

thanks
 
Upvote 0
Row 123 has the data about each job as indicated.
:unsure: Row 123 is not shown.

I need a formula that will scan the performance confirmation for the * and put the NAME of the singer in column a into the spreadsheet on Accounting.
For what date? There is nothing on your Accounting sheet about dates.

Something like ... if singer on performance confirmation has * then put singers name in this column on the accounting sheet.
:confused: Your sample Accounting sheet has no singer names on it.

Could you manually (no formulas) complete some of the Accounting sheet based on the Performance Confirmation sheet data shown above, and step us through how you manually got those result(s)?
Remember that you are very familiar with your data, sheets, what they mean and what you want but we know nothing other than what you clearly show and/or tell us.

i also have an individual accounting sheet for each singer in the tabs section that may be easier to drive this with.
I can't offer anything on that since I have not seen one of those sheets.
 
Upvote 0
Hello again,

The performance confirmation sheet has the names all listed in Column A

Take a look at the performance confirmation sheet. The names are in Column A. in the columns following, there are "*" that correspond to If the singer sang.. using the C - the end for singer 1 (Andrea) D for Singer 2 etc. Each time they perform, there are stars indicating that the person will be singing at this performance. We ONLY do quartet performances.
On the Accounting sheet I want to automagically put the name from Performance calculation into the sheet under soprano, alto , tenor base. But ONLY if a star appears on the performance calculation sheet. Of course, I have a alto that also sings soprano and so her name sometimes would have to get there as well.. but I can do a manual change if needed

So, on the performance Confirmation sheet, you see there are 5 names then a blue line then 5 names, blue line, 5 names blue line, 4 names blue line. The blue line indicates the break.
top is Soprano, next is alto, next is tenor and then bass
so, What i would like is..
On the performance calculation sheet, IF 'Performance calculation' column = *, then goto column a and find the name in row where * is and put that name in the Accounting sheet in the proper column. (Soprano, Alto, tenor or bass) corresponding. If there is NO * then skip that.

Also, for each singer, I have separate tabs.. Each tab has the exact same TOP rows as the Performance Confirmation sheet for rows 1,2,3 but on row 4 I calculate how many hours they sung and that only appears on the days WHERE a * is. I use this formula to find that number..

=IF('Performance Confirmation'!c5="*",2,0) <- so, If the sheet at column c row 5 - * then give them 2 hours for that performance. In another section i cacluate all the hours and the amount of pay each performer gets for the participation

Does this make it any clearer? If not.. I'm not quite sure how to get all of the data to you to assist, unless I create a google drive and share the actual excel spreadsheet, which I would prefer not to do. BTW - i was asked by the owner to do this work, and I have created a wonderful spreadsheet with a LOAD of data... She just keeps wanting more. IF this cannot be done i will tell her It just cannot. She will understand. Then I put in the names manually as I have done in the past years.I was hoping for a way to automate it.

Harold
 
Upvote 0
find the name in row where * is and put that name in the Accounting sheet in the proper column.
So, for example, this is what you want for Alto based on the sample data above?

Swaff39.xlsm
O
1Alto
2Heather-Lynn Smith
3Adrienne Morey
4Julie Heath
5Colleen Woodhouse
6Heather-Lynn Smith
7Heather-Lynn Smith
8Julie Heath
Accounting


If not, this is all I have bee asking you to do and post it with XL2BB:
Then I put in the names manually as I have done in the past years.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
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