Hi all,
I'm looking for a way to write this:
as this:
=SUMIF('Sheet1'!$4:$4,'Sheet2'!$D6,'Sheet1'!G306:BG306)
Where the criteria runs across a range (G to BG) that can sometimes have columns deleted, but the range must remain fixed.=SUMIF('Sheet1'!4:4,'Sheet2!$D6,INDEX('Sheet1'!306:306,306,7):INDEX('Sheet1'!306:306,306,200))
Cheers
Like this...
=SUMIF(INDIRECT("Sheet1!G4:BG4"),Sheet2!$D6,INDIRECT("Sheet1!G306:BG306"))
That formula will always refer to Sheet1!G4:BG4 and Sheet1!G306:BG306.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks Tony.
I didn't think about indirect in this way, but seems to work just fine. Just to clarify, it shouldn't update the function if I delete columns?
Cheers and take a rep for quick response.
Nope. The formula will ALWAYS refer to Sheet1!G4:BG4 and Sheet1!G306:BG306.Originally Posted by Journeyman3000
Thanks!Cheers and take a rep for quick response.
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks