Help Excel Find and Replace based on a condition

[dysfunction]

Ive got a report which is getting values from another sheet.
I am using a SUMIF formula to get the values from the sheet because the location of the value may vary in the lookup sheet.

The problem is that the description I am using is repeated a few times in the lookup sheet so it doesn't give me the right amount as shown in the attached file.

I need to find the description of the "Total" and do a search and replace of the description to a different name to do the sum if on. Is there a way to do this?

Or another way of just picking up the total amount?
I can't do a link to the total as this can move to a different line.

 

[caLLous]

I must admit I'm very confused. It took me a while to figure out that that was 2 sets of results from one spreadsheet in your screenshot. Also, what's wrong with the Expenses Costs (that sounds wrong) total in the top half?

Could you use a hidden column with a unique name for each "total" and then use that for the criteria for the SUMIF?

You could find a cell that contains a certain value with MATCH and then use SUBSTITUTE to replace the text in that cell...

 

[dysfunction]

Yes I didnt find it very easy to explain!
The First part in the picture is the "BEFORE renaming" section and the other part is the "AFTER renaming" section.

Well the "TOTAL" and the "ACCOUNT" line item in the report have the same name (Expense Costs) and therefore the SUMIF doesn't work.
I need to rename the TOTAL line but keep the "ACCOUNT" line name the same. I can't do a MATCH and SUBSTITUTE because it will change both names as they are the same. In the second half I renamed Expense Costs to Expense Total to get the Sumif to work but I need this to be a macro or something as I will have to do it quite regularly

I need some macro to saying if the cell next to the name is blank then change the name to something else.

 

[caLLous]

Have you tried playing around with the INDIRECT function? You want to find the cell with the string in it, then check the cell next to it to see if it contains anything and then, if it does, change the string in the first cell to something else. Right?

Assuming the "total" string will always be in the same column (and it's not THAT serious if it isn't) then you can use the example on that page under Creating indirect references from cell values and text.

INDIRECT("<column of total to check>",&CELL("row",<cell that contains the string>)

That will give you a reference to the cell containing (or not containing) the value so you can then check that and then alter the string in the previous cell accordingly (or not).

 

[caLLous]

Edit timer. That INDIRECT expression won't give you the reference of the cell, it will return the value within that cell.

 

[dysfunction]

Thanks Callous but I don't think that will work. I can't really have a formula to do this because the data page keeps getting overwritten with new data.

I really need to be able to run a macro to change the data page descriptions where the cell to the right of the description is blank.

 

[ST^]

I suppose you want to do this because you're not able to modify the report output? That would be BY FAR the easiest solution.

I don't see why you say "can't really have a formula". You're either pasting a formula in or you're running a macro to modify the report... either way, you're having to do something to the report.

I would use a formula which just concatenates the account number and the description (=A1&B1 for example). This means you'll get a new column that says:

10000Expenses Costs
20000Expenses Costs A
30000Expenses Costs B
Expenses Costs

Then you can use THAT column for your SUMIF.

But if you really want a macro, it's kinda easy:

Sub Macro1()

    Dim rng As Range
    Set rng = Application.Selection
   
    Dim currentCell As Range
    For Each currentCell In rng
    If Cells(currentCell.Row, currentCell.Column - 1).Value = "" And Not currentCell.Value = "" Then
        currentCell.Value = currentCell.Value + " Total"
    End If
    Next
   
End Sub

Just select the range (column) you want to "search" and run it. It's really quick and basic so don't blame me if it goes wrong, I dislike macros

 

[Bob007]

Can't you =sumifs (office 2007 onwards). Be a way to nest ifs within a single lookup.


Hope a picture paints a 1000 words

 

[ST^]

Yeah SUMIFS is good if he has >= 2007 (which he probably does because I see Calibri)

 

[dysfunction]

Yes I agree changing the source document would be the better solution but I can't at the moment. I will get them to fix the crap they are generating but in the meantime I need a short term fix.

Brilliant!! I have used SUMIFS before but didn't think about using it that way!!
That completely solves the problem!

Also that macro might come in handy at a later stage!