What's wrong with this ADDRESS formula? (referencing other sheets)

**tygrrboi** · 05-11-2014, 03:03 AM

Please Login or Register  to view this content.

In sheet one i have a table. Column F is a list of totals (hours worked) and Column H is reserved to write in "PAID" by the user.
a SUM of column F would total all the hours in the list. What I am trying to accomplish with the above code is to total all the hours worked in the list starting from the row after the user has input "PAID" (the hours before that have essentially already been paid and we need to know what money is owed AFTER that...)

The code was successful when it was one the same sheet as the table and I removed the sheet references from the above code but when I tried to put it into a different sheet, even after CTRL SHIFT ENTER for an array, it returns a #VALUE!

Can anyone help me?

**AliGW** · 05-11-2014, 03:52 AM

Can you attach a sample workbook?

**tygrrboi** · 05-11-2014, 04:00 AM

test.xlsx

Here you are.

I've made a couple of adjustments compared to the code above... I changed "PAID" to "@" and I realised I was searching in the wrong column for the "@" so I fixed that... but it still has an error. So I'm facing the same issue.

**AliGW** · 05-11-2014, 04:19 AM

What is the expected result where the formula doesn't work and why are the formulae different? I'm struggling to see why a simple SUMIF or COUNTIF would not suffice. Furthermore, why are you not just referencing the cell where the calculation works on the employee sheet from the summary sheet? Why the need to have the formula twice?

**tygrrboi** · 05-11-2014, 04:25 AM

I need it to give me a sum of the total hours that come AFTER the @ mark. In the case of the test file it should return "7" because thats the only data after the @ mark. But if you moved the @ mark to cell J4 instead of J5 it should return (Sum of 7 & 2..) 9.

**AliGW** · 05-11-2014, 04:29 AM

Yes, I understand. So, on the summary sheet in F8 you need:

=Sheet1!L2

Then, when you add extra sheets for different employees, use a drop-down in B1 of the summary sheet to select the correct employee sheet and add the INDIRECT function to F8 to reference B1 for the sheet name. You could easily hide the column on the employee sheet that holds the cell doing the calculation.

**tygrrboi** · 05-11-2014, 04:32 AM

I was hoping to not have to have the formula on each of the employee's sheets and then direct cell reference to a hidden cell... The cell L2 on Sheet1 was just to show that I had no trouble making the formula work locally, but I would ideally like to erase it.

**AliGW** · 05-11-2014, 04:36 AM

Why? It's easy to lock sections of the sheet that you don't want to be visible. At least you have a solution. Somebody else may be able to help you to get the formula working from the summary sheet, but if it were me, I really wouldn't bother. What is the issue with having a hidden column on the employee sheet? I can't see from the layout why it would matter.

**tygrrboi** · 05-11-2014, 04:40 AM

I suppose you're right. I just worry about processing speed. Working on a slower computer, calculating that function for several employees each time a cell is edited on the document could slow things down, whereas having it once on the summary page would only calculate one time when I changed the employee name. But I suppose it is trivial in terms of how long it takes to calculate. I guess I will only address the issue if I do realise its performing slowly.

Thank you for your help

**AliGW** · 05-11-2014, 04:43 AM

You're welcome! Sorry I couldn't solve the problem as you wanted. Leave the thread unsolved and see if anyone else can help - there are very many people here far more experienced than me.

**Tony Valko** · 05-11-2014, 10:56 AM

I didn't download your file.

Is this what you're wanting to do:

Data Range

	A	B	C	D
1	------	------	------	------
2	Paid	3		374
3	Paid	34
4	Paid	94
5		27
6		7
7		36
8		74
9		85
10		96
11		49
12

=SUMIF(A2:A11,"<>Paid",B2:B11)

**tygrrboi** · 05-11-2014, 11:00 AM

Kind of. But ideally I wanted to have paid written only in A4 which means anything preceding that has been paid. its a bit of a hassle to write it for every day.

**Tony Valko** · 05-11-2014, 01:43 PM

Like this...

Data Range

	A	B	C	D
1	------	------	------	------
2		3		374
3		34
4	Paid	94
5		27
6		7
7		36
8		74
9		85
10		96
11		49
12

=SUM(INDEX(B:B,MATCH("Paid",A:A,0)+1):B100)

**sktneer** · 05-12-2014, 11:14 PM

Try this array formula to see if this helps........

Please Login or Register  to view this content.

What's wrong with this ADDRESS formula? (referencing other sheets)

LinkBack

Thread Tools

Rate This Thread

Display

What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Re: What's wrong with this ADDRESS formula? (referencing other sheets)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Why is this referencing the wrong book?

[SOLVED] Issue with Complex formula only when now referencing different sheets. receiving #VALUE!

[SOLVED] formula referencing same cells but different sheets

formula problem referencing another sheets

[SOLVED] Referencing Sheets in a Formula

Bookmarks

Bookmarks

Posting Permissions