Ignoring the value of a formula in a cell.

[Royalbloodi]

Another one I'm not sure how to explain. So basically, I'm trying to make a spreadsheet that determines the last cell in a column that has a calculated value from a formula.

So, the value in G5 is based on subtracting the previous value in G4 from the Value in C5 using:

=IF(C5=0," ",(G4-C5))

Each time you enter a number of hours in Column C, it automatically calculates the remaining hours needed in Column G and tells you the Total number of hours spent in Column F.

Now, I need to reference G5 as being the last cell with data in it, however, since there are formulas in those cells, Excel only sees the value of that formula, whether there is an output calculation or not. How do I define the last row in that column and ignore the value of the formula in that cell?

Thanks!

 

[etaf]

=IF(C5=0," ",(G4-C5))
is a space
=IF(C5=0,"",(G4-C5))
is a blank
will that work
what are you using that excel sees as a formula ??

 

[Royalbloodi]

So, in another cell, I am trying to use:

=MATCH(REPT("z",255),G4:G29)

to determine what cell is the last to have text displayed. However, since there is a formula in that cell, it sees that and spits out "26" as a result. (That is the last cell in the range of G4-G29)
Even though G6 is the last cell with displayed text from a calculation. (First image.)

 

[etaf]

how about
=MAX((G4:G29<>"")*(ROW(G4:G29)))

 

[Royalbloodi]

how about
=MAX((G4:G29<>"")*(ROW(G4:G29)))

It returns the value of 29 instead of 6. Now I'm trying to figure out how to do this with VBA.

 

[etaf]

workign for me showing row 8 - which has the last value - excluding the formula whic is in row 10

Find Last cell number with text.xlsx
	G	H	I	J
1
2
3
4	1			8
5	2
6
7
8	a
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
J4	J4	=MAX((G4:G100<>"")*(ROW(G4:G100)))
G10	G10	=IF(B10="","",1)

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[RoryA]

You could use COUNT since that will ignore text values.

 

[Royalbloodi]

Again, I am trying to make Excel determine which cell is the last cell with any visible Data. All the cells in the range have formulas in them. So essentially I need something that can look at 26 rows of cells in a sheet which have formulas that calculate their value based on another cell, find the last one with a visible result and tell me what cell that is.

View attachment 104243

I've made the formulas visible so you can see them in each cell. Obviously when they're not visible, it would look like this:

View attachment 104244

Let's just use H5 as our cell I'm trying to display a result in...

With the information above, I'd like the result of G6 or 6 to be displayed in Cell H5.

As for using the XL2BB, I would, but I'm on a work computer that won't allow me to use it...

I apologize for the convoluted explanation, as initially stated, I'm having trouble putting to words what I'm trying to do.

 

[Royalbloodi]

COUNT will work with some finagling. I ended up entering the following:

=COUNT(G4:G29)+3

In K3

And I put:

="G"&K3

In K4
So now, K4 is a cell I can reference to find out the last cell with a value based on the number of cells with a numerical value in it. This will allow me to achieve what I need

And with an added number:

Thanks!