Can one Excel cell be linked to different cell depending on what is typed in?

[SomethingUnreal]

OK, well, I will try explain this best I can. I am a truck driver and I would like to keep track of my fuel, such as what state I fileed up at and how many gallons, so, on a main sheet i got fuel gallons and what state I did it in, so for exmaple when I import 200 gallons and in next columb I import IA for example, on a different sheet it imports the gallons under iowa...I dislike useing 49 sheets, I hacve all 48 states on a summery sheet and colubs with gallons for each week. Take a look at my attachment.

[Andrew-R]

If, on the summary sheet, you replace the state names with the abbreviations then you can use a SUMIF function to add together gallons on the other sheets.

For example, if you replace Iowa in A25 with IA and put the following formula in B25 on the summary sheet it will total your Iowa fill-ups across all 5 weeks.

=SUMIF('Week 1'!N5:N25,"=" & A25,'Week 1'!M5:M24)+SUMIF('Week 2'!N5:N25,"=" & A25,'Week 2'!M5:M24)+SUMIF('Week 3'!N5:N25,"=" & A25,'Week 3'!M5:M24)+SUMIF('Week 4'!N5:N25,"=" & A25,'Week 4'!M5:M24)+SUMIF('Week 5'!N5:N25,"=" & A25,'Week 5'!M5:M24)

Once you've replaced all of the state names with abbreviations you can drag this formula to the other rows in the summary sheet and it will deliver the fuel for each state.

HTH

[SomethingUnreal]

THANKS SOO MUCH this is what I was looking for, but I have 1 more question, you can redownload my new attachment too. If you noticed on every week I had 4 fuel columbs, well, for every of thos 4 I added Gallons and State, so in the forumla where it sais N5:N25 how can I also get the software to recignize the other state columbs such as T5:T25 and W5:W25 and Q5:Q25 if you look at my attachment you will see, and why is it when I darg the formula down, it wont recignize the numbers just stays zero, I looked in the formula and when I darg it down, it changes N5:25 to N6:N26, I can do all these manulay but, I would liek the drag to keep the N5:N25 to stay lol drag it your self and youl see, thanks soo much for your help again Andrew

[SomethingUnreal]

hey there me again, I posted a reply but have not herd back, I am sorry for bothering but, if you could help em with one last thing. Well, Instead of ahving totals on a different sheet, perhaps I can ahve the SUMIF function on one sheet, like ahev all the states on each Week and then do a Paste Special - Paste Link on the totals. But how can I make a formula? If you can make oen for me, that would be super. Downlaod my attachment and you will see how I have it set up now, I have all states on each week, and when ever I type in for exmaple IA for Iowa, or IL for Illinois, on the same sheet it will show the gallons for that state.

[SomethingUnreal]

Reason is why I have 4 fuel columns and state and gallons is because sometimes I have multiple fill ups in one trip so that is why

[Andrew-R]

Heh, you're going to like this one ...

=SUMIF('Week 1'!$N$5:$N$25,"=" & A14,'Week 1'!$M$5:$M$24)+SUMIF('Week 2'!$N$5:$N$25,"=" & A14,'Week 2'!$M$5:$M$24)+SUMIF('Week 3'!$N$5:$N$25,"=" & A14,'Week 3'!$M$5:$M$24)+SUMIF('Week 4'!$N$5:$N$25,"=" & A14,'Week 4'!$M$5:$M$24)+SUMIF('Week 5'!$N$5:$N$25,"=" & A14,'Week 5'!$M$5:$M$24)+SUMIF('Week 1'!$Q$5:$Q$25,"=" & A14,'Week 1'!$P$5:$P$24)+SUMIF('Week 2'!$Q$5:$Q$25,"=" & A14,'Week 2'!$P$5:$P$24)+SUMIF('Week 3'!$Q$5:$Q$25,"=" & A14,'Week 3'!$P$5:$P$24)+SUMIF('Week 4'!$Q$5:$Q$25,"=" & A14,'Week 4'!$P$5:$P$24)+SUMIF('Week 5'!$Q$5:$Q$25,"=" & A14,'Week 5'!$P$5:$P$24)+SUMIF('Week 1'!$T$5:$T$25,"=" & A14,'Week 1'!$S$5:$S$24)+SUMIF('Week 2'!$T$5:$T$25,"=" & A14,'Week 2'!$S$5:$S$24)+SUMIF('Week 3'!$T$5:$T$25,"=" & A14,'Week 3'!$S$5:$S$24)+SUMIF('Week 4'!$T$5:$T$25,"=" & A14,'Week 4'!$S$5:$S$24)+SUMIF('Week 5'!$T$5:$T$25,"=" & A14,'Week 5'!$S$5:$S$24)+SUMIF('Week 1'!$W$5:$W$25,"=" & A14,'Week 1'!$V$5:$V$24)+SUMIF('Week 2'!$W$5:$W$25,"=" & A14,'Week 2'!$V$5:$V$24)+SUMIF('Week 3'!$W$5:$W$25,"=" & A14,'Week 3'!$V$5:$V$24)+SUMIF('Week 4'!$W$5:$W$25,"=" & A14,'Week 4'!$V$5:$V$24)+SUMIF('Week 5'!$W$5:$W$25,"=" & A14,'Week 5'!$V$5:$V$24)

On the plus side I've fixed it so you really can just paste it into the Alaska line and drag a copy all the way down to Wyoming without it screwing up the sum ranges, like my last formula did.

[shg]

Your workbook and formulas would be much less complicated if you put all the data on one sheet, with one column each for date, amount, state, and perhaps trip number or trip ID.

Then the analysis would be simple, and you'd have formulas you could actually maintain.

[SomethingUnreal]

Well, when I pasted it in, it says formula too long, I did a some research and this formula is indeed too long for excel 2003, looks like I am gonna have to upgrade to 2007, but, is there a way excel 2003 can do the same, if not, I'll go out and buy office 2007 thanks for your help, I greatly appreciate it

[Andrew-R]

Like shg says, it would be simpler to change the structure of your sheets ... certainly simpler (and cheaper) than upgrading your version of Excel.

If you went for one column per week for state and gallons, instead of splitting it over 4 sets of columns then you could just use a slightly modified version of the original formula I posted, which will easily be within Excel 2003 formula length range.

[Andrew-R]

As an afterthought, if you really, really, really wanted to keep your sheet in the format it's in now then what you could do is split the formula up.

For example, you could have some hidden rows off the bottom of each week sheet which total up the fuel for each state across the 4 trips, then the summary sheet would just have to total up the states for each week.

If you want I can post back a modified version of your example, showing how this would work, but you'd have to wait until tomorrow ... it's midnight here in the UK and I'm off to bed.

[SomethingUnreal]

Nah thanks again : I will just go and get office 2007 hehe no bigy, one thing I encounterd while iporting data, for emxaple when I import lets say 500 gallons columb and then i import CA for california, it says 500 under AZ lol, - i can probubly swap and fix thos manulay though you done more then enough try it your self too, like drag the formula down and see if the same thing happens to you

[Andrew-R]

Yeah, I cocked up my original formula, so that as you drag it around it moves the cell references. Put this formula on your Alaska line and drag it down and it should work ...

=SUMIF('Week 1'!$N$5:$N$25,"=" & A14,'Week 1'!$M$5:$M$24)+SUMIF('Week 2'!$N$5:$N$25,"=" & A14,'Week 2'!$M$5:$M$24)+SUMIF('Week 3'!$N$5:$N$25,"=" & A14,'Week 3'!$M$5:$M$24)+SUMIF('Week 4'!$N$5:$N$25,"=" & A14,'Week 4'!$M$5:$M$24)+SUMIF('Week 5'!$N$5:$N$25,"=" & A14,'Week 5'!$M$5:$M$24)

Hope you get it working and I can always call in the favour if I have a "package" I need transporting across the states, right

[SomethingUnreal]

hey of course You helped me alot, but, for soem reason, when I import IA for IOWA in like week one, on totals sheet it imports the gallons for a different state, well so I thought maybe I can do it differently. Maybe for each week I can make a chart for all states, and then have the sumif formula take the gallons depending on what abbreviation I put in, download my attachment and youl see. Perhaps the formula would be less complicated.

So instead of having the states on a different sheet, I though mayeb I can just do totals for that one week, sum them up and do a special paste on the summery sheet lol. Download my attachment youl see what I mean, perhaps the SUM IF function will work better like that, can you make a formula for me?

[shg]

Please stop quoting whole posts. It's just clutter.

[Andrew-R]

I've modified your sheet to put the weekly totals for each state on the sheets Week 1-5 and put in the formula on the summary sheet to total up each week.

Have a look and see what you think.

[SomethingUnreal]

yeah this is for sure thanks soo much for all your help, now I was wondering out of curiosity, how come some states are listed 2 times, such as IA listed twice, you dont have to fix it but I was wondering why? part of the formula?

[Andrew-R]

I didn't spot that - the list of states is yours, I just copied and pasted my formulas in. Far from being part of the formula it will actually stop it working properly - you need to delete the duplicate states, make sure that the list of states on the summary sheet is in exactly the same order as the lists on the weekly sheets and re-copy the formula down from Alaska.