Hi - this is my first post here so please be kind, can not for the life of me figure this out. I have searched google finding a variety of potential solutions that whilst close to what I need, dont quite fit and given the fact that I am self taught - I can't work around.
Also, please excuse me if I dont explain this very well.
I work as staff nurse in a local hospital and am designing a dashboard. This dashboard needs to track changes in cardio-metabolic functioning.
Data is entered into a table via user-forms (working perfectly)
From the table I have created another series of sheets that track changes on a monthly basis over a 10 year period, this using a vlookup formula.
Now, because the original data might not always be entered on a monthly basis I am getting quite a few NA# messages.
One of the key indicators relates to an individuals weight, specifically whether there is an increase of 5kg or more within a three month time frame. This would not be a problem if there was consistent data across each and every month - with the NA# cells, unfortunately, this is not the case.
for example,
column A B C D
Row 1 - Jan Feb Mar Apr May Jun Jul Aug Sep
Row 2 - 80 81 NA# NA# NA# 87
(Please excuse the lack of formatting)
What I need to do is find a way of replacing the NA# values with the last known value and carry that value across until it reaches the next value - in the example above, this would therefore be 81 for the months of Mar, Apr and May, then returning the true value of 87 for the month of June.
I am not sure if I have been looking at this in completely the wrong way using OFFSET and MATCH functions - I simply can not get it to work properly
Any help with this would be very much appreciated.
Many Thanks
Jon
Also, please excuse me if I dont explain this very well.
I work as staff nurse in a local hospital and am designing a dashboard. This dashboard needs to track changes in cardio-metabolic functioning.
Data is entered into a table via user-forms (working perfectly)
From the table I have created another series of sheets that track changes on a monthly basis over a 10 year period, this using a vlookup formula.
Now, because the original data might not always be entered on a monthly basis I am getting quite a few NA# messages.
One of the key indicators relates to an individuals weight, specifically whether there is an increase of 5kg or more within a three month time frame. This would not be a problem if there was consistent data across each and every month - with the NA# cells, unfortunately, this is not the case.
for example,
column A B C D
Row 1 - Jan Feb Mar Apr May Jun Jul Aug Sep
Row 2 - 80 81 NA# NA# NA# 87
(Please excuse the lack of formatting)
What I need to do is find a way of replacing the NA# values with the last known value and carry that value across until it reaches the next value - in the example above, this would therefore be 81 for the months of Mar, Apr and May, then returning the true value of 87 for the month of June.
I am not sure if I have been looking at this in completely the wrong way using OFFSET and MATCH functions - I simply can not get it to work properly
Any help with this would be very much appreciated.
Many Thanks
Jon
Last edited:
