I could use some help, if anyone has time.
I have a workbook that is used to track meter readings.
I have attached one sheet of the workbook I an requesting help on.
There is a sheet for each city in the full workbook.
There is a summary sheet where I use VBA to populate C3:CZ236 -with:
0 if there is no activity for a meter (may be inactive)
1 if meter flow is added
-1 if meter flow is subtracted
The Cities are listed in C2:CZ2.
The Meters are listed in A3 thru A236.
Column B has formulas that show the net effect for each Meter.
There are 4 possible entries in column B:
0's -means the meter was not used (Col C thru CZ contained 0's)
0 -means the flow is added to 1 city and subtracted from another city (+1 and -1 or -1 and +1) -net 0
1 -means there is one city where this flow is added +1 net 1
2 -means there are two cities where the flow is added (+1 and +1) net 2
In Column DD I have a VLOOKUP that returns how each meter should be used and by what city.
I would like to have formulas in column DL that would concatenate the information on a row and combine it with the City found in Row 2.
I have examples in a few of the first cells in column DL.
I need a formula (in DL) that will let me compare with the formula in DD to see if they return the same thing.
Currently I use a macro that hides all the city columns that contain 0 for one meter at a time. This was fine in 2003, but in 2007 it takes much longer to run the macro.
Just heading out for the day, so I will not be able to respond until tomorrow.
If after reading this it is decided that it is not worth investing time, I certainly understand!
Thanks!
Need help with formula Excel 2007
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Need help with formula Excel 2007
You do not have the required permissions to view the files attached to this post.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need help with formula Excel 2007
I'd use a VBA function. Copy the following into a standard module:
In cell DL3, enter this formula:
=GetUse(C3:CZ3)
Fill down as far as needed.
Notes:
1) If you'd like the function to be recalculated whenever a value in columns C:CZ changes, add the following line at the beginning of the function:
Application.Volatile
This may have a negative effect on performance.
2) Don't forget to save the workbook as a macro-enabled workbook (.xlsm).
Code: Select all
Function GetUse(rng As Range) As String
Dim oCell As Range
Dim f As Boolean
For Each oCell In rng
Select Case oCell.Value
Case 1
GetUse = GetUse & ", +1 " & Cells(2, oCell.Column)
f = True
Case -1
GetUse = GetUse & ", -1 " & Cells(2, oCell.Column)
f = True
End Select
Next oCell
If f = False Then
GetUse = "0's"
ElseIf GetUse <> "" Then
GetUse = Mid(GetUse, 3)
End If
End Function
=GetUse(C3:CZ3)
Fill down as far as needed.
Notes:
1) If you'd like the function to be recalculated whenever a value in columns C:CZ changes, add the following line at the beginning of the function:
Application.Volatile
This may have a negative effect on performance.
2) Don't forget to save the workbook as a macro-enabled workbook (.xlsm).
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Need help with formula Excel 2007
Hans,
Thanks! I will stick the function in with the rest of the functions in that workbook, and give it a shot.
I am curiuos to see how it grabs the name of the city.
Thank you very much.
I will post back the results.
Thanks! I will stick the function in with the rest of the functions in that workbook, and give it a shot.
I am curiuos to see how it grabs the name of the city.
Thank you very much.
I will post back the results.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need help with formula Excel 2007
The code lets oCell loop though the cells in a row. If it encounters -1 or 1, it looks at Cells(2, oCell.Column); this is the cell in row 2 in the same column as oCell, i.e. the cell containing the name of the city.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Need help with formula Excel 2007
Hans,
You are amazing! Thank you very much. It works Great.
Thank you also for the explaination. I read the email last night on my cell phone and could not see where it would get the city.
I was very eager to get to word and try it.
You make many peoples jobs easier. THANK YOU
Chuck
You are amazing! Thank you very much. It works Great.
Thank you also for the explaination. I read the email last night on my cell phone and could not see where it would get the city.
I was very eager to get to word and try it.
You make many peoples jobs easier. THANK YOU
Chuck
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)