PDA

View Full Version : Need help with creating references from one sheet to another in a more efficient way.



spittingfire
11-08-2014, 10:09 AM
Hi all,
I am in need of some assistance in creating references that will creating reduce manual entries and updating. In the attached excel sheet I have accurate references for the first 3 months (January to March) as sample data. What I need to accomplish is when I enter "VAC, FLTR, SICK or WTB" on the Schedule tab it should automatically appear on the Vacation tab in the form of either "V,F,S or W". From the attached excel sheet you can see I already have formulas in the Vacation tab but it is certainly not that efficient as I have to manual edit each formula for each week as the schedule tab is laid out weekly and the vacation tab is laid out monthly.

In the excel sheet I have included a "VAC, FLTR, SICK and WTB" in the Schedule tab for January to show how it is suppose to reference on the vacation tab. With my limited excel knowledge I suspect that there must be a better and more efficient way to reference this.

One last thing I forgot to mention - the template and layout of the schedule tab will not change. The only thing that will change is of course are the dates.

Thanks in advance for any assistance you can provide.

p45cal
11-08-2014, 04:57 PM
in the attached, D30 formula changed to:
=IFERROR(VLOOKUP(Schedule!I8,abbr,2),"")
and similar for the cell below.
Supported by new named range called abbr at AM2:AN5

or more simply:
Formula for G31 changed to:
=LEFT(Schedule!E32,1)
and similar for the cell below.

any use?

spittingfire
11-13-2014, 07:18 PM
Thanks for the info - it helped a bit

Aussiebear
11-13-2014, 11:41 PM
Thanks for the info - it helped a bit

"Only a bit"???? well which "bit" didn't it help?

spittingfire
11-14-2014, 05:17 PM
well it works but was hoping to get away from all the copying and pasting that is still required to complete the year. I was hoping for some code that I could maybe copy and paste a month at a time minimum.