Donate SIGN UP

Excel Formula

Avatar Image
mklady121 | 22:45 Fri 07th Jan 2011 | Technology
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!!!
Gravatar

Answers

1 to 13 of 13rss feed

Avatar Image
Right, I am not brilliant with Excel but this is how I would do it

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 ?
Didn't Ed warn you earlier Jaydah?
cont...

the -6 debtor figures could be typed in Red.
fgs - try it or TIOLI.
They are not typed in red. The formula highlights them in red if the figure is negative.

I haven't got excel on this computer....or I'd be able to explain it.
-- answer removed --
I didn't say I was clever. Just your answer doesn't make sense.

They are talking about rows....you're talking about columns...!!


Again...you really do not know what this spreadsheet is for.....so your 1 to 6 instructions are irrelevant. Like...why would they name it wk1? Why?
-- answer removed --
Well the OP might come back...so please explain why they should '2. Figures in column B but rename it as wk1'

You need to be able to explain why...just like the Ed told you earlier.

This OP is obviously not in the UK by the use of $$$....


I give up...

Sorry mk....
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 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
Soz when I said put in C1 that should have been A3, it's late and I'm wabbit.
Question Author
Thank you TheCorbyLoon this answer worked perfect, it was exactly what I needed. Thanks again!!!

1 to 13 of 13rss feed

Do you know the answer?

Excel Formula

Answer Question >>