Crosswords3 mins ago
Excel Formula
13 Answers
I have a spreadsheet that has a list of clients in row 2, then their account balance in row 242. There are 12 worksheets in the file. I need a formula that I can input on a new sheet that will give me a report of who has a balance of $-5 or more. If they do owe more than $5 I want it to pull their name from row 2, if they still have money in their account and don't owe anything I don't need their name. Any suggestions for a formula???? I was able to write a formula that would give me a True if they owe or False if they don't but I can't figure out how to get it to pull that name out of row 2 if it is True. I'm also having trouble figuring out how to tell it I'm looking for a negative balance, when I put in -5 it thinks I'm trying to subtract, when I put in (5) it thinks I'm saying a new formula.
Here is an example
A B C D
1
2 Bob Sue Ned Billy
3 5.00 2.00 (2.00) (4.00)
4 (3.00) (3.00) (5.00) (5.00)
242 2.00 (1.00) (7.00) (9.00)
The total is on row 242, I need it to see that Ned and Billy have a greater than $-5.00 balance and after it sees that I need it to tell me their name from row 2. In my perfect world it would say Ned (7.00) then the next cell would say Billy (9.00). Thanks for any help you can give me on this!!! Oh and like I said the clients are spread over 12 sheets so I need that formula to grab the balance from row 242 on sheet one and sheet two and sheet 3 etc.
Thanks again!!!
Here is an example
A B C D
1
2 Bob Sue Ned Billy
3 5.00 2.00 (2.00) (4.00)
4 (3.00) (3.00) (5.00) (5.00)
242 2.00 (1.00) (7.00) (9.00)
The total is on row 242, I need it to see that Ned and Billy have a greater than $-5.00 balance and after it sees that I need it to tell me their name from row 2. In my perfect world it would say Ned (7.00) then the next cell would say Billy (9.00). Thanks for any help you can give me on this!!! Oh and like I said the clients are spread over 12 sheets so I need that formula to grab the balance from row 242 on sheet one and sheet two and sheet 3 etc.
Thanks again!!!
Answers
1 to 13 of 13
Right, I am not brilliant with Excel but this is how I would do it
If you put the following in A243 =IF(A242
If you put the following in A243 =IF(A242
02:26 Sat 08th Jan 2011
1. Put names running down column A.
2. Figures in column B but rename it as wk1
3. Insert column after names for wk2
4. type figures in wk2 column
5. insert column after names for wk3
6. type figure for wk3
etc for data
the running total could be in an inserted column before the names, that would show what you want.......I think ?
2. Figures in column B but rename it as wk1
3. Insert column after names for wk2
4. type figures in wk2 column
5. insert column after names for wk3
6. type figure for wk3
etc for data
the running total could be in an inserted column before the names, that would show what you want.......I think ?
-- answer removed --
-- answer removed --
Right, I am not brilliant with Excel but this is how I would do it
If you put the following in A243 =IF(A242<-4.99,A2," ") it will show the name in A2 if that person owes $5 or more. You can then use auto fill to put the formula in all the adjacent cells in that row and anyone owing $5 or more will show in row 243.
If you want data from a cell to appear on another worksheet, you need to define the name of the cell so for example A243 on sheet1 is defined as =Sheet1!$A$243 the same cell on sheet 2 would be =Sheet2!$A$243 and so on for all 12 sheets. If you have named each sheet, you need to replace sheet1 etc with the actual name.
You will need to define the row in which the figures and names appear and copy and paste that on the final worksheet where you want the final information to appear. If you have the original data in row 242 showing the amount each person owes and the final column is X for example, if the first cell in the row is A243, the cells with the data in are defined as =Sheet1!$A$243:$X$243. Copy and paste that onto the first cell in whichever row you want it to appear in on the final sheet. Move the cursor to the corner so a wee cross appears and drag it across so that it auto fills as many cells as there were on the source worksheet.
If you do the same with the row that had the names of those owing more than $5 you will have two rows. If you want to combine the information so that you have the name AND the amount, you need to use the concatenate function in another row.
If you put the following in A243 =IF(A242<-4.99,A2," ") it will show the name in A2 if that person owes $5 or more. You can then use auto fill to put the formula in all the adjacent cells in that row and anyone owing $5 or more will show in row 243.
If you want data from a cell to appear on another worksheet, you need to define the name of the cell so for example A243 on sheet1 is defined as =Sheet1!$A$243 the same cell on sheet 2 would be =Sheet2!$A$243 and so on for all 12 sheets. If you have named each sheet, you need to replace sheet1 etc with the actual name.
You will need to define the row in which the figures and names appear and copy and paste that on the final worksheet where you want the final information to appear. If you have the original data in row 242 showing the amount each person owes and the final column is X for example, if the first cell in the row is A243, the cells with the data in are defined as =Sheet1!$A$243:$X$243. Copy and paste that onto the first cell in whichever row you want it to appear in on the final sheet. Move the cursor to the corner so a wee cross appears and drag it across so that it auto fills as many cells as there were on the source worksheet.
If you do the same with the row that had the names of those owing more than $5 you will have two rows. If you want to combine the information so that you have the name AND the amount, you need to use the concatenate function in another row.
If you have data in row 1 and row 2 and you want it combined, input the following in C1 =CONCATENATE(a1," ",a2) the “ “ puts in a space between the data being combined. Autofill across the rest of the cells in that row and you will have cells with nothing in and cells with names and amounts. You will need to repeat this for each of the twelve sheets.
If you want to remove the blanks, the only way I can suggest is to copy each of the rows with the combined names and amounts but when you click on “paste” choose “paste special” and pick “values”. Copy this new row but instead of clicking on “values” choose “transpose” that will then put them into a column rather than a row and you can then sort them which will remove the blanks
No doubt there are folk much much more experienced in excel who could do it in fewer steps but at least it gives you an idea
If you want to remove the blanks, the only way I can suggest is to copy each of the rows with the combined names and amounts but when you click on “paste” choose “paste special” and pick “values”. Copy this new row but instead of clicking on “values” choose “transpose” that will then put them into a column rather than a row and you can then sort them which will remove the blanks
No doubt there are folk much much more experienced in excel who could do it in fewer steps but at least it gives you an idea
1 to 13 of 13
